ogr2ogr: 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 This has been fixed in newer release of FME. But let's look at the open source way of doing it.

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!

Batch processing multiple tables

Some preparation of a text file with a list of all Oracle table names to be extracted and loaded into PostGIS is required.

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM DBA_OBJECTS
  WHERE OBJECT_TYPE = 'TABLE'
  AND OWNER = '[some other schema]'

Write this list out to text file and save it.

rem This reads a list of table names from txt file and passes them to ogr2ogr which then exports from Oracle into PostGIS

for /F %%f in (C:\path\to\your\textfile\OracleTables.txt) do ogr2ogr -a_srs EPSG:27700 -overwrite -f "PostgreSQL" -nln yourschema.%%f PG:"host=localhost user=youruser password=yourpassword dbname=yourdatabase" OCI:"oracleuser/oraclepassword@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521)))(CONNECT_DATA = (SID =oraclesid))):oracleschema.%%f"

Sources:
•http://stackoverflow.com/questions/9789686/migrate-spatial-data-from-oracle-to-postgresql
•http://lists.maptools.org/pipermail/foss-gis-suite/2009-August/000781.html
•http://gis.stackexchange.com/questions/7606/ogr2ogr-srs-options-where-is-the-well-known-definition-determined

^Traditionally, as in traditionally at my place of work. Originally posted 20 July 2012 at http://words.mixedbredie.net/archives/1757