Quantum Thoughts

Oracle Spatial Data Import via Oralce utility shp2sdo.exe.

Posted on: August 5, 2008

GIS systems these days are vital components of various mission critical applications no doubt about it. In Greece there are some major GIS and mapping projects in the public sector like “ktimatologio”, GIS OTA and agriculture land GIS mapping to name a few.

Recently, I was involved in such a large scale project with the Greek Ministry of Agriculture. The main problem is that although Oracle offers a pretty much self consistent Spatial Database distribution there is no configuration tool to create new maps easy import georaster images, apply associations to them and generally provide functionality like ESRI ArcMap simply the state of the art desktop GIS applications for this job.

The solution proposed involved a swing GUI that parsed ESRI shapefiles and imported these files in the database. However there is a problem with Oracle’s script as it does not handle localization in the corresponding sql loader control file.

The solution to this problem is simple and is carried out by simply adding the correct localization directives in the loader control file. Namely these line below for Greek ASCII encoding.


If you want to implement a similar solution for your localization and do not know where to find your Oralce correct NLS LANG  directive just go here to find out.

The hole scripting procedure is below

  1. cmd>shp2sdo.exe shapefile_name shapefile_table_name -g geom -d -x (-180,180) -y (-90,90) -s 8307 -t 0.5 -vspatial [Most of the times shapefile_name would be the same with shapefile_table_name]
  2. cmd>sqlplus name/passwd @shapefile_name.sql
  3. Add the NLS encoding to the generated sql loader control file as described above.
  4. run Oracle sql loader cmd>sqlldr name/passwd control shapefile_name.ctl [sql loader is not default installation with oracle client tools you should have a database installation or run oracle wizard again and explicitly ask it to install sql loader]

Now that I reconsider, I should come back with a post on open source GIS solutions based on PostGres, Google Maps, Google Geocoder and GIS Server. An infrastructure I implemented in a project some months ago.


5 Responses to "Oracle Spatial Data Import via Oralce utility shp2sdo.exe."

Great Information

add to this u r so sweet 😉

Nice info and helped me a lot.

Nice tip! Is there a way to define the character set instead changing the .ctl?

I adore your blog post.. comfortable colours & motif. Does a person design and style this amazing site on your own or maybe do anyone bring in help to make it work in your case Plz react seeing that I!m aiming to style my personal website and would want to find out exactly where ough obtained this kind of from. thank you

Can you tell me if i need to have license for spatial module to use shp2sdo util?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

RSS Tweets

  • An error has occurred; the feed is probably down. Try again later.

RSS Reader Shared

  • An error has occurred; the feed is probably down. Try again later.

Flickr Photos

My Bookmarks

August 2008
« Jul   Dec »

Blog Stats

  • 83,312 hits
%d bloggers like this: