1 2 Previous Next 19 Replies Latest reply: Mar 22, 2007 4:22 PM by 568612 RSS

    failed to create spatial index

    556476
      Hy!

      I am quite new to ORACLE databases and want to use them for spatial data.
      But so far I was not able to create spatial indexes. I always got following error massage:

      ORA-29855: Fehler bei Ausführung der Routine ODCIINDEXCREATE
      ORA-13203: View USER_SDO_GEOM_METADATA konnte nicht gelesen werden
      ORA-13203: View USER_SDO_GEOM_METADATA konnte nicht gelesen werden

      I also got this massage when I used example data provided by oracle.
      I am very thankfull for every hint to solve this problem!
        • 1. Re: failed to create spatial index
          138365
          Don't know does XE provide spatial feature.
          Use PE version instead...
          • 2. Re: failed to create spatial index
            556476
            Hy!

            First off all thanks for your response. I thought that the locator is a full functioning part of the express edition. I also studied papers like: Oracle® Database Express Edition 2 Day Plus Locator Developer Guide

            I am totally puzzled now! Is the locator fully functioning in the express edition or not?
            • 3. Re: failed to create spatial index
              Hans Forbrich
              Locator is a subset of Spatial. Locator is included with XE.
              • 4. Re: failed to create spatial index
                Hans Forbrich
                To create a Spatial Index, you need two things:

                1) A column of SDO_GEOMETRY to index;
                2) An entry in the USER_SDO_GEOM_METADATA view that describes the characteristics of the SDO_GEOMETRY column.

                The entry in the metadata view tells the optimizer how to use the spatial index, if one has been created. Therefore the metadata must be updated before an index can be created, and the newer versions of spatial enforce this.

                The general form of creating the entry is:
                insert into user_sdo_geom_metadata values
                ( '{table_name}',
                  '{column_name}',
                  sdo_dim_array(
                    sdo_dim_element('{dim1name}',{range - lower bound}, {range - upper bound}, {tolerance}),
                    sdo_dim_element('{dim1name}',{range - lower bound}, {range - upper bound}, {tolerance}),
                  )
                  {srid | null}
                )
                for example, if you happen to be using longitude/latitude, but no specific coordinate system, in column (position) or table (cities) you would use
                INSERT INTO user_sdo_geom_metadata
                    (TABLE_NAME,
                     COLUMN_NAME,
                     DIMINFO,
                     SRID)
                  VALUES (
                  'CITY',
                  'POSITION',
                  SDO_DIM_ARRAY(  
                    SDO_DIM_ELEMENT('LONGITUDE', -180, 180, 0.005),
                    SDO_DIM_ELEMENT('LATITUDE', -90, 90, 0.005)
                     ),
                  NULL   -- SRID
                );
                • 5. Re: failed to create spatial index
                  556476
                  Hy!

                  Thanks a lot for your answer. I tried the following (coordination system is known):

                  INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
                  VALUES ('ROADS', 'ROADS_GEOM',
                  SDO_DIM_ARRAY
                  (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
                  SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
                  8307);


                  CREATE INDEX roads_sidx ON roads('ROADS_GEOM')
                  INDEXTYPE IS mdsys.spatial_index;

                  And I got this as return:

                  ORA-29855: Fehler bei Ausführung der Routine ODCIINDEXCREATE
                  ORA-13205: Interner Fehler beim Parsen von Spatial-Parametern


                  I must perform some fundamental errors, but can´t find out...
                  • 6. Re: failed to create spatial index
                    556476
                    Hy again!

                    I also got a fancy error message, when I wanted to check the metdadata table:

                    SELECT *
                    FROM mdsys.sdo_geom_metadata_table
                    WHERE sdo_table_name = 'roads';

                    got:

                    ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, MDSYS.SDO_DIM_ARRAY erhalten
                    (inconsistant data types: NUMBER expected, MDSYS.SDO_DIM_ARRAY received)


                    thanks for any hint or advise!!!
                    • 7. Re: failed to create spatial index
                      138365
                      CREATE INDEX roads_sidx ON roads('ROADS_GEOM')
                      INDEXTYPE IS mdsys.spatial_index;

                      And I got this as return:

                      ORA-29855: Fehler bei Ausführung der Routine
                      ODCIINDEXCREATE
                      ORA-13205: Interner Fehler beim Parsen von
                      Spatial-Parametern
                      Cause: An ALTER TABLE MODIFY COLUMN was issued on a column on which a domain index exists.

                      Action: Drop the domain index before attempting to modify the column.
                      • 8. Re: failed to create spatial index
                        138365
                        Take a look on this chapter in doc. At the end of chapter are examples...
                        • 9. Re: failed to create spatial index
                          556476
                          Dear Faust!

                          I did delete existing indexes and tried again (I think thats what you meant in your statement)

                          got this result (unfortunately):

                          ORA-29855: Fehler bei Ausführung der Routine ODCIINDEXCREATE
                          ORA-13203: View USER_SDO_GEOM_METADATA konnte nicht gelesen werden
                          ORA-13203: View USER_SDO_GEOM_METADATA konnte nicht gelesen werden


                          best regards

                          l.d.
                          • 10. Re: failed to create spatial index
                            556476
                            I found in an other forum someone with the same problem ( problem in creating spatial_index in htmldb.oracle.com
                            I also do not have acess rights to mdsys tables.

                            regards
                            l.d.
                            • 11. Re: failed to create spatial index
                              138365
                              ORA-13203: View USER_SDO_GEOM_METADATA konnte nicht
                              gelesen werden
                              Schaut so aus als Benutzer keine Rechte auf USER_SDO_GEOM_METADATA hat???
                              Kannst du diese Ansicht abfragen?
                              Hast du Beispiele aus Dokumenation durchgegagen?
                              faust@XE> CREATE INDEX customers_sidx ON customers(cust_geo_location)
                                2    INDEXTYPE IS mdsys.spatial_index;
                              
                              Index created.
                              
                              faust@XE> CREATE INDEX stores_sidx ON stores(store_geo_location)
                                2    INDEXTYPE IS mdsys.spatial_index;
                              
                              Index created.
                              
                              faust@XE> select * from USER_SDO_GEOM_METADATA;
                              
                              TABLE_NAME
                              --------------------------------
                              COLUMN_NAME
                              --------------------------------------------------------------------------------
                              
                              DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
                              --------------------------------------------------------------------------------
                              
                                    SRID
                              ----------
                              CUSTOMERS
                              CUST_GEO_LOCATION
                              SDO_DIM_ARRAY(SDO_DIM_ELEMENT('LONG', -180, 180, .5), SDO_DIM_ELEMENT('LAT', -90
                              
                              , 90, .5))
                                    8307
                              
                              TABLE_NAME
                              --------------------------------
                              COLUMN_NAME
                              --------------------------------------------------------------------------------
                              
                              DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
                              --------------------------------------------------------------------------------
                              
                                    SRID
                              ----------
                              
                              STORES
                              STORE_GEO_LOCATION
                              SDO_DIM_ARRAY(SDO_DIM_ELEMENT('LONG', -180, 180, .5), SDO_DIM_ELEMENT('LAT', -90
                              
                              , 90, .5))
                              
                              TABLE_NAME
                              --------------------------------
                              COLUMN_NAME
                              --------------------------------------------------------------------------------
                              
                              DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
                              --------------------------------------------------------------------------------
                              
                                    SRID
                              ----------
                                    8307
                              • 12. Re: failed to create spatial index
                                138365
                                I found in an other forum someone with the same
                                problem (
                                problem in creating spatial_index in htmldb.oracle.com
                                I also do not have acess rights to mdsys tables.
                                I think I found a problem:
                                Following query in SQL*Plus returning:
                                faust@XE> select
                                  2    table_name
                                  3  from
                                  4    all_tables
                                  5  where
                                  6    owner = 'MDSYS'
                                  7  order by
                                  8    table_name;
                                
                                TABLE_NAME
                                ------------------------------
                                OGIS_GEOMETRY_COLUMNS
                                OGIS_SPATIAL_REFERENCE_SYSTEMS
                                SDO_COORD_AXES
                                SDO_COORD_AXIS_NAMES
                                SDO_COORD_OPS
                                SDO_COORD_OP_METHODS
                                SDO_COORD_OP_PARAMS
                                SDO_COORD_OP_PARAM_USE
                                SDO_COORD_OP_PARAM_VALS
                                SDO_COORD_OP_PATHS
                                SDO_COORD_REF_SYS
                                
                                TABLE_NAME
                                ------------------------------
                                SDO_COORD_SYS
                                SDO_CS_SRS
                                SDO_DATUMS
                                SDO_DATUMS_OLD_SNAPSHOT
                                SDO_ELLIPSOIDS
                                SDO_ELLIPSOIDS_OLD_SNAPSHOT
                                SDO_PREFERRED_OPS_SYSTEM
                                SDO_PREFERRED_OPS_USER
                                SDO_PRIME_MERIDIANS
                                SDO_PROJECTIONS_OLD_SNAPSHOT
                                SDO_TOPO_DATA$
                                
                                TABLE_NAME
                                ------------------------------
                                SDO_TOPO_RELATION_DATA
                                SDO_TOPO_TRANSACT_DATA
                                SDO_TXN_IDX_DELETES
                                SDO_TXN_IDX_EXP_UPD_RGN
                                SDO_TXN_IDX_INSERTS
                                SDO_UNITS_OF_MEASURE
                                
                                28 rows selected.
                                And same query in APEX (as same user) returning only 10 rows!!???

                                Thus:
                                1. Don't use APEX for spatial processing - make it using SQL*Plus.

                                2. Inserting one row into USER_SDO_GEOM_METADATA does not mean that you created a new table 'ROADS'.
                                faust@XE> INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
                                
                                  2  VALUES ('ROADS', 'ROADS_GEOM',
                                  3  SDO_DIM_ARRAY
                                  4  (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
                                  5  SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
                                  6  8307);
                                
                                1 row created.
                                
                                faust@XE> CREATE INDEX roads_sidx ON roads('ROADS_GEOM')
                                  2  INDEXTYPE IS mdsys.spatial_index;
                                CREATE INDEX roads_sidx ON roads('ROADS_GEOM')
                                                           *
                                ERROR at line 1:
                                ORA-00942: table or view does not exist
                                Use CREATE TABLE statement for that ...

                                Cheers!
                                • 13. Re: failed to create spatial index
                                  Hans Forbrich
                                  Good catch.

                                  That makes a certain sense - SDO_USER_GEOM_METADATA and spatial indexing are administrative things, not general development or user things. Stuff I'd normally do in SQLPlus anyways.

                                  I thing this should be highlighted back to the XE design team for the next release, as it should probably be available in Apex!
                                  • 14. Re: failed to create spatial index
                                    556476
                                    Hallo Faust!

                                    I went through the examplex as you told me, but they delivered no result.
                                    Below I poste one of my tries:

                                    CREATE TABLE roads (id NUMBER,
                                    status VARCHAR2(5),
                                    road_geom SDO_GEOMETRY);


                                    INSERT INTO roads VALUES(
                                    1,
                                    'major',
                                    SDO_GEOMETRY(
                                    2002,
                                    8307,
                                    NULL,
                                    SDO_ELEM_INFO_ARRAY(1,2,1),
                                    SDO_ORDINATE_ARRAY(12.9224686,53.1074265, 12.9573921,53.1080311)
                                    )
                                    );



                                    INSERT INTO roads VALUES(
                                    1,
                                    'major',
                                    SDO_GEOMETRY(
                                    2002,
                                    8307,
                                    NULL,
                                    SDO_ELEM_INFO_ARRAY(1,2,1),
                                    SDO_ORDINATE_ARRAY(12.9221663,531145857, 12.92352836,531145333, 12.92569327,53.11564611, 12.92893268,53.11673585, 12.9316106479,53.11819464, 12.932453846,53.11851929)
                                    )
                                    );


                                    INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
                                    VALUES ('ROADS', 'ROADS_GEOM',
                                    SDO_DIM_ARRAY
                                    (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
                                    SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
                                    8307);


                                    CREATE INDEX roads_sidx ON roads('ROADS_GEOM')
                                    INDEXTYPE IS mdsys.spatial_index;


                                    ORA-29855: Fehler bei Ausführung der Routine ODCIINDEXCREATE
                                    ORA-13205: Interner Fehler beim Parsen von Spatial-Parametern


                                    SELECT * FROM USER_SDO_GEOM_METADATA;

                                    ORA-00932: Inkonsistente Datentypen: NUMBER erwartet, MDSYS.SDO_DIM_ARRAY erhalten



                                    Danke für Deine Geduld!


                                    l.d.
                                    1 2 Previous Next