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

oracle-spatialsql

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.

Best Answer

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: triangle field

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.

Related Question