2

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.

  • Thinking on my feet here: If the datatype is SDO_GEOMETRY, use the SDO function -- SDO_GEOM.RELATE with a mask of DISJOINT -- with a self-join and an attribute condition of a.uniqueidcol != b.uniqueidcol. This will return ten billion rows, so you'll need to play around with EXISTS to eliminate polynomial expansion and maybe an envelope overlap to make better use of the spatial index (for each poly, test if any nearby polys overlap) – Vince Jun 4 '14 at 13:45
  • No, turn it around. Take a feature and do select count(*) where "SDO_ANYINTERACT(geom1,geom2)='TRUE'. Geom1 is feature that is under study, geom2 holds all the other features of the table. If count=0 the feature is disjoint. Query will use spatial index and it will be fast and I guess that doing it 100000 times will take couple of minutes. – user30184 Jun 4 '14 at 15:50
  • Doh! I was solving for the few polygons which do overlap, not the few disjoint ones. Still, using a self join is critical piece for making the solution work. Be sure you order the parameters correctly -- the many goes before the one, since the index on the first referenced column is used. – Vince Jun 5 '14 at 1:11
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: 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.

0

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, oracle example 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.

  • This example is for Esri's SDE.ST_GEOMETRY, not Oracle Spatial (SDO_GEOMETRY type); the concept is the same, but the syntax is different. – Vince Jun 4 '14 at 18:08
  • Whoops thanks Vince, I didn't read the creation syntax from the example! It's a shame that SQLfiddle doesn't have spatial extension. I'll correct the example later today. – MichaelStoner Jun 4 '14 at 18:14
  • Changed the Example from esri to an oracle example – MichaelStoner Jun 4 '14 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.