2 Replies Latest reply: Jun 23, 2013 3:04 AM by 920054 RSS

    Error in spatial index

    920054

      Hi, everyone

      I created a spatial index on a table, but i get the error message:

      ORA-13033: the data in sdo_elem_info_array of sdo_geometry is unavailable

      How can i write a SQL command to delete those unavailable records?

        • 1. Re: Error in spatial index
          _jum

          Hi,

           

          here you find an example with a similar error. You simply can validate the spatial data, to find invalid records:

           

          DROP   TABLE sdo_test;
          
          CREATE TABLE sdo_test (
            nr      NUMBER,
            GEOM     MDSYS.SDO_GEOMETRY);
          
          COMMIT;
          
          --correct
          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)));
          
          --correct
          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)));
          
          --empty SDO_ELEM_INFO_ARRAY
          INSERT INTO sdo_test VALUES (3,
            SDO_GEOMETRY(3302, 8307, NULL,  SDO_ELEM_INFO_ARRAY(),
              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);
          
          --DROP   INDEX sdo_test_geom_spix;
          
          --ORA ERROR 13033
          CREATE INDEX sdo_test_geom_spix
                    ON sdo_test(geom)
             INDEXTYPE IS MDSYS.SPATIAL_INDEX
          PARAMETERS('sdo_indx_dims=2');
          
          --find the invalid record
          SELECT nr, SDO_GEOM.VALIDATE_GEOMETRY(geom,0.001) val
            FROM sdo_test; 
          
          nr     val
          -----------------------
          1    TRUE
          2    TRUE
          3    13033
          

           

           

          oops - where is the PREVIEW button in the new design ?!

          In the meantime found the Syntax Highlighting (->Switch to the advanced editor, paste your code, mark it, click the >> (insert) button, select Syntax Highlighting, choose the style...>

          • 2. Re: Error in spatial index
            920054

            Thank you very much!!!

            My problem has been resolved!!!