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

Oracle Spatial Index on a non-NOTNULL Column

AlanShar Newbie
Currently Being Moderated
I am using Oracle 11g with Spatial data init.

I have a SDO_GEOM column in a table, which has NULL values for some records. I have to run spatial query based on this column so need spatial index on it. Is it possible to create Spatial Index on SDO_GEOM column even if some of these are NULL and get results by spatial query?


Thanks,
Alan

Edited by: user3883362 on Apr 29, 2013 5:59 AM
  • 1. Re: Oracle Spatial Index on a non-NOTNULL Column
    Ivan Bush Journeyer
    Currently Being Moderated
    Alankar,

    This errors is saying that the SRID in the USER_SDO_GEOM_METADATA table for your table is not the same as the SRID of the geometries in your table.
    It says that the SRID you are using is 20002.

    Can you select the data from USER_SDO_GEOM_METADATA for your table and post it. Also post a record from your table.

    Regards.

    Ivan
  • 2. Re: Oracle Spatial Index on a non-NOTNULL Column
    AlanShar Newbie
    Currently Being Moderated
    Thanks Ivan,

    That I got that was SRID error, which I erroneously put in the question. Now I have modified the question could you please look at it and help me.

    Thanks,
    Alan
  • 3. Re: Oracle Spatial Index on a non-NOTNULL Column
    NoelKhan Journeyer
    Currently Being Moderated
    Alan,
    Is it possible to create Spatial Index on SDO_GEOM column even if some of these are NULL
    Yes.
    SQL> CREATE TABLE test (ID NUMBER PRIMARY KEY, geom MDSYS.SDO_GEOMETRY);
    
    Table created.
    
    -- "insert a row with non-NULL geometry"
    SQL> INSERT INTO test VALUES (1, SDO_GEOMETRY('POINT (6000000 2100000)', 40986));
    
    1 row created.
    
    -- "insert a row with NULL geometry"
    SQL> INSERT INTO test VALUES (2, NULL);
    
    1 row created.
    
    SQL> CREATE INDEX test_spx ON test (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
    
    Index created.
    ...and get results by spatial query
    Yes, presuming your data is valid.
    -- "Creates a 10' buffer around the point we previsouly inserted then applies SDO_INSIDE"
    SQL> SELECT ID, SDO_INSIDE(geom, SDO_GEOM.SDO_BUFFER(geom, 10, 1)) FROM test;
    1 TRUE --"our point geometry"
    2 FALSE --"our NULL"
    
    2 rows selected.
    If you're having trouble here, I suspect you may be using functions that are choking on NULLs, e.g., converting a NULL geometry to/from WKT.

    How are you accessing the data (sqlplus, custom application, etc.)? What's the query and can we see some sample data?

    Regards,
    Noel
  • 4. Re: Oracle Spatial Index on a non-NOTNULL Column
    AlanShar Newbie
    Currently Being Moderated
    Thanks Noel,

    Yes I tried that and I found as you said that we can have spatial index on GEOM field even if it has some NULL values. Moreover we can execute spatial queries on that.

    I was firing the query from SQL, but also be firing same kind of query from PL/SQL, does that make any difference to above statement ?

    Thanks,
    Alan

    Edited by: user3883362 on Apr 30, 2013 3:09 AM

Legend

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