2

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.
shellter
  • 36,525
  • 7
  • 83
  • 90
jedge
  • 937
  • 2
  • 11
  • 18
  • 1
    Welcome to stackOverflow. In the future, please use the `{}` format tool at the top left of the edit box on highlighted text to format as code/data/output/errMsgs/etc. Good Luck – shellter Apr 14 '16 at 13:32
  • 1
    I have [a previous example here](http://stackoverflow.com/a/33258347/1509264) - I'm not going to re-post it as @micklesh has covered the bits that you're missing in his answer. – MT0 Apr 14 '16 at 14:19

3 Answers3

6

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

micklesh
  • 417
  • 1
  • 4
  • 16
  • Great! Thank you very much. This has successfully inserted a row into the USER_SDO_GEOM_METADATA table, which I can see when I query it as you advise above. However when I do this on the table I mention above and the same on another table with point coordinates (using the INSERT INTO statement you have described above) and then use a SDO_CONTAINS query, I still get: {ORA-13226: interface not supported without a spatial index}, potentially suggesting that it hasn't successfully created an index. – jedge Apr 15 '16 at 11:11
  • Well, this insert does not create any index. You should now create spatial index as you have already tried: CREATE INDEX area_idx ON floating_options(area_geo) INDEXTYPE IS MDSYS.SPATIAL_INDEX; – micklesh Apr 15 '16 at 11:56
4

The following should also be considered: Oracle has case-sensitive names see this post.

The next problem with the "intelligent" guys of Oracle is, that their USER_SDO_GEOM_METADATA table does not support lower-case names (at least in 11g).

So with for a table definition like this

CREATE TABLE "cola_markets" (
  "mkt_id" NUMBER PRIMARY KEY,
  "name" VARCHAR2(32),
  "shape" SDO_GEOMETRY);

you cannot create a spatial index.

When inserting the metadata

INSERT INTO USER_SDO_GEOM_METADATA 
  VALUES (
  'cola_markets',
  'shape',
  SDO_DIM_ARRAY(   -- 20X20 grid
    SDO_DIM_ELEMENT('X', 0, 20, 0.005),
    SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
     ),
  NULL   -- SRID
);

the names are converted to upper-case.

If you then create the index

CREATE INDEX cola_spatial_idx
   ON "cola_markets"("shape")
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;

You will get the error mentioned above

ORA-13203: failed to read USER_SDO_GEOM_METADATA view

because it cannot find the lower-case names in the metadata table.

Conclusion:

  • use only upper-case names (or no double quotes)
  • Orcale guys are blockheads
Community
  • 1
  • 1
Mahe
  • 759
  • 1
  • 6
  • 21
0

This is a solution: 

INSERT INTO user_sdo_geom_metadata 
VALUES ('target_table', 'SHAPE', sdo_dim_array(sdo_dim_element('x',-180,180,.005),sdo_dim_element('y',-90,90,.005)), 4326);

create index new_index_table on target_table(SHAPE) indextype is mdsys.spatial_index;

It should work.

Dumi
  • 1,414
  • 4
  • 21
  • 41
Surya Ganguly
  • 1