7 Replies Latest reply: Oct 10, 2012 4:58 AM by don123 RSS

    create index failed

    don123
      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
          hi,

          pls have a look this thread

          ORA-13249: Error initializing geodetic transform

          regards,
          • 2. Re: create index failed
            don123
            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
              Ying Hu-Oracle
              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
                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
                  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
                    Ying Hu-Oracle
                    "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
                      thanks, it works for me now...