Fixing the SRID in a spatial geometry column

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

CREATE INDEX test_geom_srid_spidx ON test_geom_srid (geom)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX

Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in spatial index: [mdrcrtxfergm]
ORA-13249: Error in spatial index: [mdpridxtxfergm]
ORA-13200: internal error [ROWID:AAHlT1AABAAHWddAAA] in spatial indexing.
ORA-13206: internal error [Invalid geometry; check ROWID] while creating the spatial index
ORA-13365: layer SRID does not match geometry SRID
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause:    Failed to successfully execute the ODCIIndexCreate routine.
*Action:   Check to see if the routine has been coded correctly.

 

This problem is caused by an attempt to create a spatial index on a table where a SRID n has been specified in USER_SDO_GEOM_METADATA, but the geometries in the table are not associated to the same SRID, typically they have a null SRID.

If the coordinates of the geometries are correct for the expected SRID, you can fix the value stored inside the geometry objects as follows, provided you have a primary or unique key defined for the table:

update tab a
   set geom = (select sdo_geometry(
                         b.geom.sdo_gtype, 
                         <new_srid>, 
                         b.geom.sdo_point, 
                         b.geom.sdo_elem_info, 
                         b.geom.sdo_ordinates) 
                 from tab b 
                where b.id = a.id);

After fixing the geometries the spatial index creation should succeed, after dropping any invalid index that might have been left after the initial attempt.

See message translations for ORA-13365.