I am copying tables using ogr2ogr from Oracle to PostGIS using

 ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME>/<PASSWORD>@<SID>:<table>

This command fails to copy indexed tables, but works on regular tables.

ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS

This might be related to https://github.com/hdknr/note/issues/11

Update: By indexing I mean these commands in Oracle:

INSERT INTO user_sdo_geom_metadata VALUES ('my_table', 'GEOLOC',  MDSYS.SDO_DIM_ARRAY(
  MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.0005),
  MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.0005)),  8307 );

What is the right way to copy an indexed table?

  • I can't imagine any possible reason why an index could have any possible impact on a full-scanning SELECT statement. – Vince Jul 10 at 23:40
  • @Vince It might be related to github.com/hdknr/note/issues/11 – Stepan Jul 11 at 15:33
  • @Vince when you ran the OGR2OGR statement on your Oracle to PostGIS connection, did you have any issues on your indexed tables, or did they all come across OK? – DPSSpatial Jul 11 at 15:37
  • @Vince When a table is NOT indexed it is copied correctly. When an index is added with CREATE INDEX (see updated question) it returns an error. – Stepan Jul 11 at 15:46
  • @Stepan You seem to be focusing on the wrong end of the process. It's not the Oracle side that's in difficulty, but the PostgreSQL side. Try to load a 10-row shapefile or CSV into the PG instance; if that fails, you can isolate your spatial_ref_sys problem. – Vince Jul 11 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.