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 on these errors but have not found anything useful. What is the problem here?

  • Since the error message says that the spatial reference is not valid, have you tried checking to see if 3857 is valid? – Vince Sep 29 '17 at 11:00
2

3857 was not added to Oracle Spatial until 11R2. So to use Web Mercator you either need to upgrade your database or patch your system. See Oracle bug 7113148 for details.

You could also add in a custom SRID by hand though tis not a task for the weary. See https://community.oracle.com/message/14393887 for someone recently in your exact boat - they never said how it turned out! The only advice even after a decade is to carefully follow the steps from Jayant Sharma at Oracle in this thread https://community.oracle.com/thread/649988?start=15&tstart=0.

However I think installing the patch would be a lot easier and safer. 11.7.0.1 is a pretty old database, might be time to consider an update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.