[GIS] Convert non-spatial Oracle table to spatial Oracle table in a different database

ogr2ogroracle-dbmsoracle-spatial

I'm trying to use ogr2ogr to convert data from a non-spatial table (has X & Y values) in an Oracle database (1) to a spatial format in a different Oracle database (2). This will be scheduled to run daily to overwrite the table in Oracle database (2) each time.

I have it working using a ogr2ogr's Virtual File Format, with the file Oracle.vrt which is like:

<OGRVRTDataSource>
  <OGRVRTLayer name="TEST_X_Y">
    <SrcDataSource>OCI:userid_1/password_1@database_instance_1:TEST_X_Y</SrcDataSource>
    <SrcLayer>TEST_X_Y</SrcLayer>
    <GeometryType>wkbPoint</GeometryType>
    <LayerSRS>EPSG:27700</LayerSRS>
    <GeometryField encoding="PointFromColumns" x="X_TEXT" y="Y_TEXT"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

Then using the following ogr2ogr command:

ogr2ogr -update -overwrite -f OCI OCI:userid_2/password_2@database_instance_2:TEST_OGR_IN Oracle.vrt TEST_X_Y -lco OVERWRITE=YES -lco SRID=27700 -nln TEST_OGR_IN

Now this works and creates the spatial table in the Oracle database (2) fine, but when I run the ogr2ogr command a second time I get an error like:

ERROR 1: ORA-00001: unique constraint (MDSYS.UNIQUE_LAYERS) violated
ORA-06512: at "MDSYS.SDO_GEOM_TRIG_INS1", line 43
ORA-04088: error during execution of trigger 'MDSYS.SDO_GEOM_TRIG_INS1' in
  INSERT INTO USER_SDO_GEOM_METADATA VALUES ('TEST_X_Y',
...

The table (TEST_OGR_IN) still gets overwritten/updated in the Oracle database (2) but the problem appears to be that ogr2ogr has inserted values into the USER_SDO_GEOM_METADATA table on the Oracle database (1) for the non-spatial table (TEST_X_Y) and is violating an index/constraint?

Although the process works, I'd like to understand why the error is happening and how I can avoid it.

Best Answer

I was having the same (or similar) issue with simply reading from a database. I noticed as well that a non-spatial table was being added to the SDO_GEOM_METADATA table. To resolve it, I removed the table name from the OCI connection string. Since I had the two tables (joining a non-spatial to the spatial for the query) in the SQL, it still worked and I no longer had the trigger going off. I should note I'm running from the C++ GDAL interface.

Related Question