This discussion is archived
7 Replies Latest reply: Oct 10, 2012 2:58 AM by don123 RSS

create index failed

don123 Newbie
Currently Being Moderated
hi, create index failed when i try to create spatial index in sql developer spatial tools...any suggestions ??



An error was encountered performing the requested operation:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-13249: Error in spatial index: [mdrcrtxfergm]
ORA-13249: Error in spatial index: [mdpridxtxfergm]
ORA-13200: internal error [ROWID:AAAYTyAAEAAABFbAAA] in spatial indexing.
ORA-13206: internal error [] while creating the spatial index
ORA-13364: layer dimensionality does not match geometry dimensions
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.
Vendor code 29855
  • 1. Re: create index failed
    dataseven Explorer
    Currently Being Moderated
    hi,

    pls have a look this thread

    ORA-13249: Error initializing geodetic transform

    regards,
  • 2. Re: create index failed
    don123 Newbie
    Currently Being Moderated
    hi, thanks

    but i suspect the problem due to LRS geometries and GTYPE=3302 as given below.


    SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
    -87.899771, 42.000853, 0, -87.899109, 42.000847, 54.8504622))

    SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
    -87.917489, 41.992077, 0, -87.917063, 41.99174, 51.4503307))

    SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
    -87.925704, 41.965994, 0, -87.925705, 41.965445, 60.9789892))
  • 3. Re: create index failed
    yhu Journeyer
    Currently Being Moderated
    What's in user_sdo_geom_metadata? You may check the following example:

    http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_lrs_concepts.htm#i885124
  • 4. Re: create index failed
    don123 Newbie
    Currently Being Moderated
    yhu,

    The article says to use "sdo_indx_dims=3" parameter when we have X,Y,Z, M.
    But in my case, it is only X,Y,M and this parameter is not required.
    I have also tried by using sdo_indx_dims=2, but not solved.

    thanks
  • 5. Re: create index failed
    _jum Journeyer
    Currently Being Moderated
    Your example works for me:
    DROP TABLE sdo_test;
    
    CREATE TABLE sdo_test (
      nr      NUMBER, 
      GEOM    MDSYS.SDO_GEOMETRY);
    
    COMMIT;
    
    INSERT INTO sdo_test VALUES (1,
      SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), 
        SDO_ORDINATE_ARRAY(-87.899771, 42.000853, 0, -87.899109, 42.000847, 54.8504622)));
    
    INSERT INTO sdo_test VALUES (2,
      SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1),
        SDO_ORDINATE_ARRAY(-87.917489, 41.992077, 0, -87.917063, 41.99174, 51.4503307)));
    
    INSERT INTO sdo_test VALUES (3,
      SDO_GEOMETRY(3302, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), 
        SDO_ORDINATE_ARRAY(-87.925704, 41.965994, 0, -87.925705, 41.965445, 60.9789892)));
    
    DELETE FROM USER_SDO_GEOM_METADATA 
      WHERE TABLE_NAME = 'SDO_TEST' AND COLUMN_NAME = 'GEOM' ;
    
    
    INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) 
      VALUES ('SDO_TEST', 'GEOM', 
        MDSYS.SDO_DIM_ARRAY 
          (MDSYS.SDO_DIM_ELEMENT('X', -87.925705, -87.8991090, 0.001), 
           MDSYS.SDO_DIM_ELEMENT('Y',  41.965445,  41.9654450, 0.001),
           MDSYS.SDO_DIM_ELEMENT('M',    0.00000,  60.9789892, 0.001)
         ),8307); 
      
    
    CREATE INDEX sdo_test_geom_spix
              ON sdo_test(geom)
       INDEXTYPE IS MDSYS.SPATIAL_INDEX 
    PARAMETERS('sdo_indx_dims=2');
    
    SELECT nr, SDO_GEOM.VALIDATE_GEOMETRY(geom,0.001) val, sdo_geom.sdo_length(geom,0.001) len  
      FROM sdo_test;    
    
    nr      val    len
    -----------------------------------
    1     TRUE     54,8504621993542
    2     TRUE     51,4503307056062
    3     TRUE     60,978989198583
     
  • 6. Re: create index failed
    yhu Journeyer
    Currently Being Moderated
    "sdo_indx_dims=2" is good because you have X and Y.
    The question is whether diminfo in user_sdo_geom_metadata
    matches geometry diminfo:
    select * from user_sdo_geom_metadata where table_name = '...' and column_name = '...';
    select geometry_column from table_name from rowid = 'AAAYTyAAEAAABFbAAA';
  • 7. Re: create index failed
    don123 Newbie
    Currently Being Moderated
    thanks, it works for me now...

Legend

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