Tuesday, May 30, 2023

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.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles