Creating a spatial index on oracle

jedge picture jedge · Apr 14, 2016 · Viewed 9k times · Source

I have a table 'floating_options', and I want to create a spatial index on a column 'area_geo' (which is a sdo_geometry column, with two rows of data that appear as expected when I select * from floating_options).

I have used the following code but I am receiving the error below. I would be very grateful for any help! Thanks!

CREATE INDEX area_idx ON floating_options(area_geo)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;


Error report -
SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
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.

Answer

micklesh picture micklesh · Apr 14, 2016

Before indexing the table you should have it 'spatially enabled'.
Try to check if it is shown in spatial metadata:

SELECT * FROM USER_SDO_GEOM_METADATA
    WHERE TABLE_NAME = UPPER('floating_options')
      AND COLUMN_NAME = UPPER('area_geo');

If there are no results - then a couple of options are available.
One dirty way - insert data directly

INSERT INTO USER_SDO_GEOM_METADATA
VALUES (UPPER('floating_options'),UPPER('area_geo'),
        mdsys.SDO_DIM_ARRAY(
             mdsys.SDO_DIM_ELEMENT('Easting', <lowest_x>, <highest_x>, <x_tolerance>),
             mdsys.SDO_DIM_ELEMENT('Northing', <lowest_y>, <highest_y>, <y_tolerance>)
        ), <SRID>);

Please change the < placeholders > accordingly

Please take a look also at https://community.oracle.com/thread/836452?tstart=0 or http://gerardnico.com/wiki/oracle_spatial/metadata