How to update the metadata of a layer in Oracle imported through FME Workbench?

Question

I used FME Workbench to import an ESRI feature class onto Oracle. I used the original ESRI SRID when I imported through FME. Now that the table is in Oracle, I want to change the metadata and SRID to match that of Oracle. First I used the following code but it gave me an error because there was already OTTAWAPROVRDS in USER_SDO_GEOM_METADATA (I'm guessing it was automatically added when imported through FME)

INSERT INTO USER_SDO_GEOM_METADATA 
(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 
VALUES 
('OTTAWAPROVRDS', 'GEOM',
 MDSYS.SDO_DIM_ARRAY 
 ( MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.5), 
 MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.5) 
 ), 
 8307
);

Then I used the following code to change just the SRID

UPDATE USER_SDO_GEOM_METADATA a
SET a.SRID = 8307
WHERE a.TABLE_NAME = 'OTTAWAPROVRDS';

which worked but when I tried to migrate to current I get the following error:

Error report -
ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
ORA-13365: layer SRID does not match geometry SRID
ORA-06512: at "MDSYS.SDO_MIGRATE", line 423
ORA-06512: at "MDSYS.SDO_MIGRATE", line 474
ORA-06512: at line 1
29877. 00000 -  "failed in the execution of the ODCIINDEXUPDATE routine"
*Cause:    Failed to successfully execute the ODCIIndexUpdate routine.
*Action:   Check to see if the routine has been coded correctly.

Which leads me to believe that just updating the SRID does NOT work and I have to update the whole metadata. I tried this code:

UPDATE USER_SDO_GEOM_METADATA
SET DIMINFO =  MDSYS.SDO_DIM_ARRAY 
 ( MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.5), 
 MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.5) 
 ),
 SRID = 8307
WHERE COLUMN_NAME = 'OTTAWAPROVRDS';

But the result is 0 rows updated

Answer 1

FME has indeed automatically inserted the proper metadata for your table. And I see no reason to change that.

Unless, if I get you correctly, what you really want to do is transform the data you loaded into SRID 8307, i.e. long/lat WGS84. Is that correct ? And what is the current SRID ?

To transform your table to a new coordinate system, you need to do the following:

1) Drop the existing spatial index

drop index <your index name>;

2) Update all geometries using the SDO_CS_TRANSFORM() function, like this:

update ottawaprovrds
set geom = sdo_cs.transform (geom, 4326);
commit;

Notice I use 4326 (the EPSG code) rather than 8307, Oracle/OGC code because it is "more standard", i.e. directly understood by pretty much all GIS toolkits.

3) Update the metadata:

UPDATE USER_SDO_GEOM_METADATA SET
DIMINFO = SDO_DIM_ARRAY (
  SDO_DIM_ELEMENT('Long', -180, 180, 0.5), 
  SDO_DIM_ELEMENT('Lat', -90, 90, 0.5) 
),
SRID = 4326
WHERE TABLE_NAME = 'OTTAWAPROVRDS';
commit;

Notice: the selector is for TABLE_NAME not COLUMN_NAME

4) Recreate the spatial index

create index <your index name> on ottawaprovrds (geom)
indextype is mdsys.spatial_indextype;