[postgis-users] Invoking GIST index on 2 disjoints within a query

[Paul Ramsey]

Drop the &&. The indexes are not much help for testing Disjoint, unfortunately.

The other thing you could do is re-cast it as a does-not-intersect
test instead...

SELECT <some stuff> FROM A, B, C WHERE NOT ( ST_Intersects(A.the_geom,
B.the_geom) OR ST_Intersects(A.the_geom, C.the_geom) );

(The above will be indexed automatically for recent versions of
PostGIS. For older, add in the && clause manually like this.)

SELECT <some stuff> FROM A, B, C WHERE NOT ( (A.the_geom && B.the_geom
AND Intersects(A.the_geom, B.the_geom)) OR (A.the_geom && c.the_geom
AND ST_Intersects(A.the_geom, C.the_geom) ) );

YMMV... the B and C tables might be interacting and blowing up the
query, I don't have an intuitive feel for this stuff like Regina :)

P.

On Thu, Apr 3, 2008 at 2:12 PM, Dylan Lorimer <edylan at google.com> wrote:
> Hi,
>
>  So I have probably a simple question but I don't use PostGIS enough to
>  know the right way to go about this. Hoping someone can help.
>
>  So I've got 3 spatial tables, each containing a bunch of polygon
>  geometries. Let's call the tables A, B, C. What I want is to find out
>  which polygons in table A are disjoint from all polygons in tables B
>  and C. Meaning, which polygons in A have 0 overlap with polygons in B
>  or C.
>
>  I seem to have no troubles doing this when using only 2 tables, but
>  adding the 3rd is messing things up. Oh, and I also want to invoke the
>  GIST indexes to speed things up.
>
>  Here's the (slightly generalized) query I think I should be using:
>
>  SELECT <some stuff> FROM A, B, C WHERE disjoint(A.the_geom,
>  B.the_geom) AND disjoint(A.the_geom, C.the_geom) AND <some other
>  attribute filters> AND A.the_geom && B.the_geom AND A.the_geom &&
>  C.the_geom;
>
>  I've not seen this query complete successfully as it takes so long,
>  but I think something is wrong with it as running it against only A
>  and B is really quick and there aren't too many geometries in the
>  tables.
>
>  Thoughts?
>
>  Cheers,
>  dylan
>  _______________________________________________
>  postgis-users mailing list
>  postgis-users at postgis.refractions.net
>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>