Oracle Spatial Data Import via Oralce utility shp2sdo.exe.

Posted by: doxaras on: August 5, 2008

In: GIS
Comment!

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.

CHARACTERSET EL8ISO8859P7
BYTEORDER LITTLE

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

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]
cmd>sqlplus name/passwd @shapefile_name.sql
Add the NLS encoding to the generated sql loader control file as described above.
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.