Finding Polygons which do not touch any other polygon in a single oracle database table

Question

I currently have an oracle table of approximately 100,000 records - all of which are polygons. I want to find all the polygons which do not touch any other polygon within the same table.

There are a few previous posts which use the ST_TOUCHES query but this relies on you having two geometries to compare against from different tables. Unless my understanding is incorrect? Where as what I want is a query which looks at the one table and one Geometry column and looks to see if a polygon touches another.

Answer 1

I whipped up a table of 89280 triangles, 78 of which where randomly coded for isolation. Then I deleted the neighbors of these triangles with the command:

DELETE FROM gse_tritab1
WHERE oid in (
        SELECT  a.oid oid
            FROM    gse_tritab1 a, gse_tritab1 b
        WHERE   b.class = 'X'
        AND     sdo_anyinteract(a.geom,b.geom) = 'TRUE'
) AND class = 'R';

Which resulted in a layer looking like this:

Issuing the query:

SELECT  a.oid oid
FROM    gse_tritab1 a
WHERE   NOT EXISTS (
        SELECT  b.oid
        FROM    gse_tritab1 b
        WHERE   SDO_FILTER(b.geom,a.geom,'querytype = WINDOW') = 'TRUE'
        AND     SDO_GEOM.RELATE(b.geom,'DISJOINT',a.geom,0.001) = 'FALSE'
        AND     b.oid <> a.oid
)

returned 76 rows (because two of the randomly selected polygons were touching) in 4.06 minutes.

Just for grins I also ran:

SELECT  a.oid oid
FROM    gse_tritab1 a
WHERE   NOT EXISTS (
        SELECT  b.oid
        FROM    gse_tritab1 b
        WHERE   SDO_ANYINTERACT(b.geom,a.geom) = 'TRUE'
        AND     b.oid <> a.oid
)

and it also generated 76 rows, in 6.47 minutes.

Both the DELETE and the queries used the technique known as a "self join" to compare a column in the table to itself (one in a subselect, and one in an EXISTS clause).

It's important to note that the position of the referenced columns in the spatial columns is important -- The table with the index (the "many" side of the "find many which have a relationship with one") must be in the first position of the Spatial function for optimal performance (and sometimes to prevent an ORA-13226 error). If I left out the SDO_FILTER constraint with the RELATE test, performance was significantly worse.

Answer 2

You're missing a self join, here's another question about it.

I've changed the example follow my earlier error, now using oracle's examples cola markets,

This link shows how to create these as ST_GEOMETRY Data to match your question.

SELECT c1.name, c2.name
FROM cola_markets c1, cola_markets c2
WHERE c1.shape.ST_TOUCHES(c2.shape) and c1.name != c2.name ;

Should give the touching cola_a, cola_b and cola_c, from cola_markets referenced twice as c1 and c2.

To find which regions are disjoint

Now to modify it for ones that do not interact:

SELECT c.name 
FROM cola_markets c 
WHERE c.name NOT IN 
(  
     SELECT c1.name
     FROM cola_markets cInner1, cola_markets cInner2
     WHERE cInner1.shape.ST_TOUCHES(cInner2.shape) and cInner1.name != cInner2.name 

);

I'm away from a instal of 11 g at the moment to test it.