Wednesday February 02 2011 at 02:35
The Oracle Locator/Spatial SDO_RELATE search operator, and convenience operators, all provide for the ability to search for a positive occurrence of a topological relationship:
Operator 
Description 
SDO_RELATE 
Determines whether or not two geometries interact in a specified way. 
SDO_ANYINTERACT 
Checks if search and target geometries have the ANYINTERACT topological relationship. 
SDO_CONTAINS 
Checks if search and target geometries have the CONTAINS topological relationship. 
SDO_COVEREDBY 
Checks if search and target geometries have the COVEREDBY topological relationship. 
SDO_COVERS 
Checks if search and target geometries have the COVERS topological relationship. 
SDO_EQUAL 
Checks if search and target geometries have the EQUAL topological relationship. 
SDO_INSIDE 
Checks if search and target geometries have the INSIDE topological relationship. 
SDO_ON 
Checks if search and target geometries have the ON topological relationship. 
SDO_OVERLAPBDYDISJOINT 
Checks if search and target geometries have the OVERLAPBDYDISJOINT topological relationship. 
SDO_OVERLAPBDYINTERSECT 
Checks if search and target geometries have the OVERLAPBDYINTERSECT topological relationship. 
SDO_OVERLAPS 
Checks if any geometries in a table overlap (that is, have the OVERLAPBDYDISJOINT or OVERLAPBDYINTERSECT topological relationship. 
SDO_TOUCH 
Checks if search and target geometries have the TOUCH topological relationship. 
For example, here is the correct method of finding all points that fall inside a specific polygon (here Tasmania):
select /*+ORDERED*/ count(*)
from australian_states a,
geodpoint2d g
where a.admin_name = 'Tasmania'
and sdo_inside(g.geom,a.geom) = 'TRUE';
COUNT(*)

2
But the Oracle operators do not provide the ability to search for geometries that have no relationship ie the DISJOINT case, or the inverse of the required relationship eg NOT SDO_INSIDE? Note that there is no SDO_DISJOINT convenience operator (or a DISJOINT mask for SDO_RELATE).
But what if I want to find all that that are not inside Tasmania?
Note that we cannot query using a ‘FALSE’ return code as follows:
select /*+ORDERED*/ count(*)
from australian_states a,
geodpoint2d g
where a.admin_name = 'Tasmania'
and sdo_inside(g.geom,a.geom) = 'FALSE';
Error starting at line 1 in command:
select count(*)
from australian_states a,
geodpoint2d g
where a.admin_name = 'Tasmania'
and sdo_inside(g.geom,a.geom) = 'FALSE'
Error report:
SQL Error: ORA29902: error in executing ODCIIndexStart() routine
ORA13207: incorrect use of the [SDO_INSIDE] operator
ORA06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 416
29902. 00000  "error in executing ODCIIndexStart() routine"
*Cause: The execution of ODCIIndexStart routine caused an error.
*Action: Examine the error messages produced by the indextype code and
take appropriate action.
So how do we execute the reverse?
The Oracle Pro Spatial book says when discussing the SQL/MM relationships that:
ST_Disjoint [is the] Negation of ANYINTERACT (use MINUS to subtract ANYINTERACT results from entire set).
So, in our case what we have to do is create a SQL statement that represents the set of all features:
 The set of all points
select g.rowid
from geodpoint2d g;
And another that represents the positive case the set of all geometries inside our search feature:
 The set of all points inside Tasmania
select /*+ORDERED*/ g.rowid
from australian_states a,
geodpoint2d g
where a.admin_name = 'Tasmania'
and sdo_inside(g.geom,a.geom) = 'TRUE';
When we have these two we can then apply the MINUS SQL set operator to subtract the two:
select count(*)
from ( select g.rowid
from geodpoint2d g
MINUS
select /*+ORDERED*/ g.rowid
from australian_states a,
geodpoint2d g
where a.admin_name = 'Tasmania'
and sdo_inside(g.geom,a.geom) = 'TRUE'
);
COUNT(*)

498
I hope this is helpful to someone.

Comment [2]
I think I posted this before but didn’t go thru. Just wondering how the speed compares to this: The below is the way I normally tackle this question. I assume the below should also work on Oracle.
SELECT COUNT
FROM
( SELECT g.rowid FROM geodpoint2d g LEFT JOIN australian_states a ON (a.admin_name = ‘Tasmania’ and sdo_inside(g.geom,a.geom) = ‘TRUE’ )
WHERE a.rowid IS NULL) As foo;
— Regina · 9 February 2011, 13:04 · #
Regina,
Yes it does work in Oracle exactly as you have specified: that’s the nice thing about SQL, there is so many ways to “skin the cat”.
Is it faster? Hard to tell on my small test set, but the Autotrace of both approaches shows yours to be a lot more inefficient (Cost: 683 vs 8; Consistent Gets: 24623 vs 2671; recursive calls: 3120 vs 209)
Thanks for commenting. Much appreciated.
Simon
— Simon Greener · 10 February 2011, 07:03 · #