0

I am using Oracle 11g (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production) and I have a table that has column of type SDO_GEOMETRY. I want to create a spatial index on this column (NEW_SHAPE). My query is as following:

CREATE INDEX GIS_GEOM_SRID3857_SPTIDX ON GIS_GEOM_SRID3857_LOOKUP ( NEW_SHAPE )
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
/

and I am getting the following errors:

Error starting at line : 23 in command -
CREATE INDEX GIS_GEOM_SRID3857_SPTIDX ON GIS_GEOM_SRID3857_LOOKUP ( NEW_SHAPE )
INDEXTYPE IS MDSYS.SPATIAL_INDEX
Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: SRID 3857 does not exist in MDSYS.CS_SRS table
ORA-29400: data cartridge error
Error - OCI_NODATA 
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.

I have searched allot on these errors but have not found anything useful. I would highly appreciate if you may point me to the right direction and help in identifying the issue I am facing. It will save me allot of time. Many thanks in advance for your assistance.

Amir
  • 685
  • 3
  • 13
  • 36
  • There's a known issue: "Missing SRID 3857 in 11.2.0.3 after an upgrade from 11.1.0.7." – Rene Sep 28 '17 at 12:14
  • I have not updgraded. The version I am using is: "Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production" – Amir Sep 28 '17 at 12:16

1 Answers1

0

First of all, your data must actually be SRID 3857 and sdo_geom.srid be set to 3857 for all rows of the table, for future processing to work.

Now, since the error raises, srid is set to 3857 in user_sdo_geom_metadata. So the question is: Does SRID 3857 exist in DB's SRIDs?
If not, you must insert it, drop the spatial index (usually a failed index gets created) and re-create it.

Let me know if you need any help on any of the above...

GregStef
  • 176
  • 6