[GIS] Errors in creating spatial index on SDO_GEOMETRY column in Oracle 11g

oracle-spatialspatial-index

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?

Best Answer

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.

Related Solutions

[GIS] ArcMap Query Layer passing invalid SQL to Oracle

You could try manually defining the spatial reference of a query layer :

ArcGIS uses the spatial reference identifier (SRID) specified on the shape values in the database to determine the spatial reference of the features being returned by a query layer. There may be instances where ArcGIS cannot accurately determine the spatial reference based on the current SRID value set on those features in the database. In these cases, the query layer will have an unknown spatial reference. It is possible to manually define the spatial reference on the query layer on the Advanced Options dialog box. It is important to note that this does not reproject the query layer; it just defines what spatial reference should be used when mapping the results of the query within ArcGIS.
Check the Advanced Options check box on the New Query Layer dialog box.
Click Select to choose the coordinate system to be used for the spatial reference.
Optionally, you can import the spatial reference from an existing dataset by clicking Import.

[GIS] is it possible to store empty geometries in an Oracle Database

This is the question/response I got from oracle's spatial expert in Europe in 2014.

q: Does oracle have the concept of an empty geometry? If so can you give me an example? Would this be a valid way of creating an empty point? SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(NULL, NULL, NULL), NULL, NULL)

My initial reaction was there is no such thing as an empty geometry and therefore it should be represented as a NULL, but thought I'd ask you as you might have come across the concept.

A: You are correct that we do not have a specific structure to represent an empty geometry. We choose instead to take advantage of the SQL NULL, which works fine.

For example intersecting two disjoint geometries returns NULL. So is generating a buffer around a null geometry: it returns null too. Unioning a geometry with a null also produces a null. And so is computing the length of a null geometry, or measuring the distance between a geometry and null.

This has the benefit of making the use of nulls for geometries fully orthogonal with the traditional use of SQL: anything combined with NULL is NULL. Geometric aggregations ignore NULLs - just like numerical aggregations.

Note however that measuring the area of a line or a point returns 0 - not NULL.

The use of NULL to represent empty geometries is probably open to arguing. There used to be discussions about the relational model arguing that there should be multiple kinds of NULL: typically to distinguish between an unknown value and a missing value. For example a NULL postal code may mean that I don't know the postal code for an address, or it may mean that the country does not use postal codes.

Related Question

[GIS] Registering database Arcgis 10.2.2