How to move spatial data from Oracle to Postgres [duplicate]

This question already has answers here: Migrate SPATIAL data from Oracle to开发者_StackOverflow社区 Postgresql (2 answers) Closed 6 years ago.

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

---

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.

---

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

---

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