This discussion is archived
3 Replies Latest reply: Mar 3, 2013 10:44 PM by Simon Greener RSS

Issues Creating Spatial Index (Beginner)

994501 Newbie
Currently Being Moderated
I am getting the error below:

CREATE INDEX HHLT.BUILDING_SPATIAL_IDX
*
ERROR at line 1:
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


When I select run the following, I get:
select * from mdsys.sdo_geom_metadata_table;

SDO_OWNER SDO_TABLE_NAME
-------------------------------- --------------------------------
SDO_COLUMN_NAME
--------------------------------------------------------------------------------

SDO_DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
--------------------------------------------------------------------------------

SDO_SRID
----------
SYS BUILDING
BUILDING_POLYGON
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 0, 820, .005), SDO_DIM_ELEMENT('Y', 0, 580, .

005))



This is my script:
=========================
create user HHLT identified by HHLT;
grant all privileges to HHLT;

alter session set current_schema = HHLT;

CREATE TABLE HHLT.BUILDING
(
BUILDING_ID VARCHAR2(3),
BUILDING_NAME VARCHAR2(50),
VERTICES_COUNT INT,
BUILDING_POLYGON MDSYS.SDO_GEOMETRY,
CONSTRAINT BUILDING_PK PRIMARY KEY (BUILDING_ID)
);

INSERT INTO USER_SDO_GEOM_METADATA
VALUES ('BUILDING', 'BUILDING_POLYGON',
SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 820, 0.005),
SDO_DIM_ELEMENT('Y', 0, 580, 0.005)), NULL);

COMMIT;

CREATE INDEX ELKORDY.BUILDING_SPATIAL_IDX
ON ELKORDY.BUILDING(BUILDING_POLYGON)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
==================================

What am I missing? Working from Windows 7 install.
  • 1. Re: Issues Creating Spatial Index (Beginner)
    Simon Greener Journeyer
    Currently Being Moderated
    Dear Unnamed Person,

    It looks like cross-schema issues to me.

    You do all your work in the HHLT schema and then, at the end of your script you do this:
    CREATE INDEX ELKORDY.BUILDING_SPATIAL_IDX
     ON ELKORDY.BUILDING(BUILDING_POLYGON)
     INDEXTYPE IS MDSYS.SPATIAL_INDEX;
    Yet in your post you are:
    CREATE INDEX HHLT.BUILDING_SPATIAL_IDX
    Sorry, which is it?

    Also, when you select from the metadata table it tells you that the BUILDING table is in SYS schema (SYS is the owner) but not in HHLT or ELKORDY!

    I suspect that:
    alter session set current_schema = HHLT;
    Didn't connect as you expected - you are still the SYS user - so that when you did this:
    INSERT INTO USER_SDO_GEOM_METADATA 
     VALUES ('BUILDING', 'BUILDING_POLYGON',
     SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 820, 0.005),
     SDO_DIM_ELEMENT('Y', 0, 580, 0.005)), NULL);
    It associated the metadata with the SYS owner and not the HHLT owner hence the error when creating the index.

    I would recommend you.

    1. Connect as the SYS schema.
    2. Remove the metadata object;
    3. Connect in a new session to the HHLT schema as the HHLT user;
    5. Create a new metadata entry in user_sdo_geom_metadata when in HHLT schema;
    6. Create the index (drop any existing broken index first).

    regards
    SImon
  • 2. Re: Issues Creating Spatial Index (Beginner)
    994501 Newbie
    Currently Being Moderated
    Wow, I am a complete moron.

    Connecting correctly as the HHLT user and running the script (and making sure everything is HHLT.[blah]) worked! You are awesome! Thank you for the explanation and for pointing out the correct method of do this.
  • 3. Re: Issues Creating Spatial Index (Beginner)
    Simon Greener Journeyer
    Currently Being Moderated
    My pleasure.

    And thanks for awarding the points: it is greatly appreciated.

    Oh, no one's a moron. Sometimes we can't see the obvious.

    regards
    Simon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points