Exporting Data from Oracle to PostGIS

Traditionally exporting from Oracle to PostGIS involved an intermediate step Oracle to Shapefile and then Shapefile to PostGIS. But this means that field names longer than 10 characters in Oracle get truncated in the conversion to Shapefile. And using the QGIS or PgAdminIII shp2pgsql gui has no easy way to set the schema or SRID other than manual intervention. I guess it could be run from the command line though…

Using Safe’s FME you can go directly from Oracle to PostGIS. But there is no way to define the destination schema in the PostGIS database. The PostGIS writer expects there to be a schema with the same name as the Oracle schema in order to complete the extraction and translation. See https://groups.google.com/forum/#!topic/fmetalk/zQsUUJNR5h8

So, open-source here we come. ogr2ogr can read and write to a number of different formats including Oracle (this uses the proprietary OCI.dll). On Windows install OSGeo4W with GDAL/OGR. Run the setup.exe and “Advanced Install” to install the gdal-oracle10g package. See http://stackoverflow.com/questions/9789686/migrate-spatial-data-from-oracle-to-postgresql

The command to issue at the command line is (all on one line):

ogr2ogr -a_srs <srs> -overwrite -f "PostgreSQL" -nln <postgis schema>.<postgis table> PG:"host=<postgis host> user=<postgis user> password=<postgis password> dbname=<postgis database>" OCI:"<oracle user>/<oracle password>@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port number>)))(CONNECT_DATA = (SID =<sid name>))):<oracle schema>.<oracle table>"

<srs> = in the EPSG format e.g EPSG:27700

This uses ogr2ogr to connect to an Oracle database, pull a table from a schema and write it to a defined schema in PostGIS database.

Nice!

Sources:

Leave a Reply

Your email address will not be published. Required fields are marked *