5 Replies Latest reply: Apr 30, 2013 5:02 AM by AlanShar RSS

    Spatial indexes and null values in Geoms

    FlyingGuy
      Hey gang,

      Quick Question... Is it valid to have null values in an sdo_geometry column and the index still be valid and usable?

      I seem to remember having problems with spatial queries eg: sdo_inside, sdo_anyinteract etc., when there were null values.

      Thanks.

      11g R2 EE w/spatial ( obviously ).
        • 1. Re: Spatial indexes and null values in Geoms
          user8048037
          Would say YES, but try it by yourself:
          DROP   TABLE sdo_test;
          
          CREATE TABLE sdo_test (
            nr      NUMBER, 
            GEOM    SDO_GEOMETRY);
          
          COMMIT;
          
          TRUNCATE  TABLE sdo_test;
          
          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');
          
          INSERT INTO sdo_test VALUES (4, NULL);
          
          SELECT nr, SDO_GEOM.VALIDATE_GEOMETRY(geom,0.001) val  
            FROM sdo_test;    
          
          nr      val
          -------------
          1     TRUE
          2     TRUE
          3     TRUE
          4     NULL
          
          4 rows selected.
          • 2. Re: Spatial indexes and null values in Geoms
            Stefan Jager
            Hi FlyingGuy,

            Yes it is. I've used NULL geometries quite a bit and never encountered problems with the index, so don't think that has changed with 11R2.

            Cheers,
            Stefan
            • 3. Re: Spatial indexes and null values in Geoms
              AlanShar
              I have a SDO_GEOM column in a table, which has NULL values for some records. When I create spatial Index on this column, I get error. I have to run spatial query based on this column so need spatial index on it.

              Error starting at line 5 in command:
              CREATE INDEX field_idx ON WM_DIM_FIELD(geom)
              INDEXTYPE IS MDSYS.SPATIAL_INDEX
              Error at Command Line:5 Column:14
              Error report:
              SQL Error: 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:AAAUMIAAEAAAl9TAAA] in spatial indexing.
              ORA-13206: internal error [] while creating the spatial index
              ORA-13365: layer SRID does not match geometry SRID
              ORA-13249: SRID 20002 does not exist in MDSYS.CS_SRS table
              ORA-29400: data cartridge error
              Error - OCI_NODATA
              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.

              Could you please suggest why is it happening? Is it due to NULL values in GEOM column.

              Thanks,
              Alankar
              • 4. Re: Spatial indexes and null values in Geoms
                user8048037
                user3883362 wrote:
                ORA-13365: layer SRID does not match geometry SRID
                ORA-13249: SRID 20002 does not exist in MDSYS.CS_SRS table
                The reason seems for this error could rather be an incorrect SRID (here 20002)
                What is the result of the query:
                SELECT * 
                  FROM MDSYS.CS_SRS
                 WHERE  srid=20002;
                • 5. Re: Spatial indexes and null values in Geoms
                  AlanShar
                  Thanks,

                  I have created another question for this, The error which I posted here is not which I was getting, However why I was asking the question is resolved.

                  Yes we can have NULL data in SDO_GEOM fields and can be spatially indexed and spatial queries can be fired on that field.

                  Thanks everyone for looking into it.

                  Thanks,
                  Alan