How to migrate spatial data from Oracle to PostGIS

Install GDAL/OGR with Oracle driver. The simplest way to do this is to use OSGeo4W. If you choose to install GDAL/OGR using OSGeo4W don’t forget to select the gdal-oracle10g package in the “Advanced Install” mode.

Now run this command (replace {{ }} items with your values):

ogr2ogr 
-a_srs {{SRS}} 
-overwrite -f 
"PostgreSQL" 
-nln {{POSTGIS SCHEMA}}.{{POSTGIS TABLE}} 
PG:"
host='{{POSTGIS HOST}}' 
port='{{POSTGIS PORT}}' 
user='{{POSTGIS USER}}' 
password='{{POSTGIS PASSWORD}}' 
dbname='{{POSTGIS DATABASE}}'" 
OCI:"{{ORACLE USER}}/{{ORACLE PASSWORD}}
@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = {{ORACLE HOST}})
(PORT = {{ORACLE PORT}})))
(CONNECT_DATA = (SID = {{ORACLE SID}})))
:{{ORACLE SCHEMA}}.{{ORACLE TABLE}}"

If you don’t want to waste your time writing this command for each table, you can use the Calc spreadsheet we prepared to help you:

Download: ogr2ogr – oracle spatial to postgis




Leave a Reply

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