Spatial query w/o spatial data

Question

I trying to execute a next query on Oracle Database 11g Enterprise Edition (11.1.0.6.0):

SELECT "__ItemId"
FROM "Cities"
WHERE "Longitude" IS NOT NULL AND "Latitude" IS NOT NULL
  AND SDO_ANYINTERACT(SDO_GEOMETRY('POINT(' || "Longitude" || ' ' || "Latitude" || ')'),
    SDO_UTIL.FROM_WKTGEOMETRY('POLYGON ((-100 80, 100 80, 100 -80, -100 -80, -100 80))')) = 'TRUE'

Where "Longitude" and "Latitude" - numeric [NUMBER(28,5)] columns in the "Cities" table.

UPD: Next query (with the same error) can be used for tests:

SELECT 'Solved!'
FROM DUAL
WHERE SDO_ANYINTERACT(SDO_GEOMETRY('POINT(' || 100 || ' ' || 100 || ')'),
  SDO_UTIL.FROM_WKTGEOMETRY('POLYGON ((-150.0 82.0, 150.0 82.0, 150.0 -67.0, -150.0 -67.0, -150.0 82.0))')) = 'TRUE';

I get an error:

Error report -
SQL Error: ORA-13226: interface not supported without a spatial index
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_3GL", line 71
ORA-06512: at "MDSYS.SDO_3GL", line 239
13226. 00000 -  "interface not supported without a spatial index"
  *Cause:    The geometry table does not have a spatial index.
  *Action:   Verify that the geometry table referenced in the spatial operator
             has a spatial index on it.

Questions:

How can I check, that point with specified "Longitude" and "Latitude" in the specified polygon? Polygon in not always simple, it can be any.
How can I create a spatial index on a table without any spatial column?
It's really, I can not just call a spatial operator?

score 1 · Accepted Answer · answered Dec 29 '13 at 11:34

1

As the error messages state, you need a Spatially indexed column to make use of the Spatial operators.

You can get equivalent functionality from the RELATE function in the SDO_GEOM package using the mask "ANYINTERACT":

SELECT sdo_geom.relate(a, 'ANYINTERACT', b, 0.05)
FROM dual

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objgeom.htm#BGHCDIDG

answered Dec 29 '13 at 11:34

Ben

1,902
17
17

Sorry, one more question: will this way use spatial indexes when they exists? When "a" or "b" is a spatial column in a table with spatial index on this column? – Viacheslav Ivanov Dec 30 '13 at 06:07
In my tests indexes not used (for SDO_ANYINTERACT index is used), but this is OK - question about table with out spatial index. Thanks for you answer! I will use different ways. – Viacheslav Ivanov Dec 30 '13 at 07:39
If I recall correctly, the documentation says that the first shape (a) could be from a spatially indexed column, but as with everything Oracle there are no guarantees of index use. – Ben Dec 30 '13 at 09:13
Yes, I was tried with first shape is geometry field with spatial index and TABLE ACCESS (FULL) was performed :о( When SDO_ANYINTERACT used, the index was used. In any case, thanks you! – Viacheslav Ivanov Dec 30 '13 at 09:42

score 0 · Answer 2 · answered Dec 24 '13 at 13:12

0

first, you can not create spatial index on a table without any spatial column, and sdo_anyinteract uses spatial index, there are many spatial operator in oracle you can try another one,

I'm not sure but you can try sdo_relate(obj1,obj2,'querytype=window mask=anyintereact')

hope it works.

answered Dec 24 '13 at 13:12

CanFil

335
2
8

Thanks! The same query with WHERE …SDO_RELATE(SDO_GEOMETRY('POINT(' || "Longitude" || ' ' || "Latitude" || ')'), SDO_UTIL.FROM_WKTGEOMETRY('POLYGON ((-100 80, 100 80, 100 -80, -100 -80, -100 80))'), querytype=window mask=anyintereact') = 'TRUE'; Generates the same error. – Viacheslav Ivanov Dec 24 '13 at 14:49

Spatial query w/o spatial data

2 Answers2