I put together a small Geotools based jar that Coldfusion can use to copy data from Oracle Spatial directly into PostGIS. Previously, we had needed to export the data as a shapefile, and use shp2pgsql to load the data into PostGIS.

That method had an ESRI imposed limitation, in that the length of shapefile attribute names can only be up to 10 characters long, whereas Oracle column names can be much longer, so they were truncated. No good!

Even though Geotools made it ridiculously easy to implement, it turns out I didn't even need to do it. I should have thought of ogr2ogr in the beginning! Of course, it supports Oracle Spatial and PostGIS, and its main function is to convert features from one data type to another. Anyways, here's how you would do it.

First you need to download ogr2ogr with the OCI driver. You can get it from FWTools but I found it easier to use the one distributed with OSGeo4W, from OSGeo, becuase it already includes the OCI driver.

The OCI driver uses a thick connection to your Oracle database, so you need to have the Oracle Instant Client or SQL Developer installed on the machine that you wish to connect from. This doesn't have to be the PostGIS machine or the Oracle machine - it can just be your normal desktop. Anyways, you need to copy the oci.dll that comes with your Instant Client installation to the "bin" directory of OSGeo4W.

Also, make sure you can connect to your Oracle database using the instant client. If you can't do that, you need to edit your TNSNAMES.ORA until you get it right.

Now you should be able to use ogr2ogr to connect to oracle using the name you gave it in your TNSNAMES.ORA, and transfer spatial tables to PostGIS, using this command (on one line):

Code:

It looks complicated, but it's really not.

Open source software wins again. Why does anyone still use shapefiles again?