This discussion is archived
5 Replies Latest reply: Apr 30, 2013 3:02 AM by AlanShar RSS

Spatial indexes and null values in Geoms

FlyingGuy Explorer
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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