5

Does anyone have a clear document on how to move Oracle Spatial data to Postgres (both Community version and Enterprise DB... Thanks

Improve this question
0

3 Answers 3

Reset to default
5

First, on Postgres you will need to install PostGIS. Then export your Oracle tables either to Shapefile (try SD02SHP described here) or to KML. If you can export them to shapefile, reimport them to PostGIS with shp2pgsql per these instructions. If you use kml, reimport the geometry with the geometry constructor ST_GeomFromKML.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You can use ogr2ogr. To connect to Oracle you need the OCI.dll provided by the Advanced Install of OSGeo4W.

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

See these other links:

Migrate SPATIAL data from Oracle to Postgresql

http://lists.maptools.org/pipermail/foss-gis-suite/2009-August/000781.html

Improve this answer

Comments

0

You could also probably export the geometries from Oracle as Well Known Text and then reimport them. This would require you writing import statements for the tables which should be pretty easy to create

Improve this answer

Comments