Shp2ora and Ora2shp: Utilities for importing and exporting shapefiles to Oracle

Yes, another one of those. Its not exactly a novel idea as there are a few converters around for moving shapefile data to and from Oracle:

  • If you are an ESRI/ArcSDE user you have the sde2shp and shp2sde commands.
  • Oracle provides two options: a command line program, shp2sdo to load shapefiles into Oracle, and its java equivalent through Mapbuilder
  • Most (all?) GIS’ that can read Oracle Spatial data, include options to save the Oracle layer to a shapefile.

Plenty of choice you probably think. Well, I was still not happy. First of all I didn’t always have ArcSDE on all machines I was working on, secondly installing Mapbuilder means you need to go through the 18.2MB download, plus it will only LOAD shapefiles into Oracle and not the other way around and thirdly, I didn’t want to go through the whole process of downloading a fully-blown desktop GIS (say QGIS) just for the sake of converting a spatial table into a shapefile. Not to mention that you have to configure the client GIS first in order to connect to Oracle BEFORE you can even display the layer – so you can then export it. Too much hassle for a relative easy task.

So I decided to create my own little command-line utilities to do just that. You can download the full source code and executables from the Box.Net widget on the left side of this post (ora2shp.rar).

After compiling the project, you will find two executables under the Debug folders of Ora2shp and shape2ora projects: Ora2shp.exe and shp2ora.exe respectively,

Ora2shp syntax is: ora2shp <username/password>@dbalias> <spatial_table_name> <PK_col> <shape_col> <shapefile> ["optional_where_clause"]

If you use the where clause you should NOT put the WHERE keyword.

Shp2orashp2ora <username/password>@dbalias> <spatial_table_name> <shape_col> <shapefile> <srid>

If you don’t specify a SRID it will default to null. If the spatial table already exists records will get appended. If it doesn’t it will get created and will also create the oracle metadata records record (USER_SDO_GEOM_METADATA table) and spatial index.

It should deal with all shapes (multipart, Z, M) APART from multipatch.

Note that you will need Oracle client 10.2 installed to compile the programs as-is. Otherwise change the reference to the Oracle client of your choice. You will know if you have the wrong Oracle client version if you get an error about Oracle.DataAccess not being the same version as Oracle Client.

When running the programs any errors should appear on the console and the full stack trace will be written to orashp_err.log file, located at the same folder as the executables.

Note that  performance is not great- especially when creating the shapefile. It took something less than 15mins to create a point shapefile of around 66K records. (around 5M of shapefiles). But feel free to improve it and I would very much appreciate to get back to me if you do!

2 thoughts on “Shp2ora and Ora2shp: Utilities for importing and exporting shapefiles to Oracle

    1. Yup- thats another option, but again, you will need to install a pretty large download, and configure it. As said, those utilities were simply meant as a quick-and-dirty way to get data in and out of Oracle, not as a fully blown product.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s