If I understand you correctly, then I think the most efficient route is to
figure out which ones overlap with an Intersect as Paul mentioned, and then
throw them out. I presume you mean intersect rather than overlaps - ie.
Overlaps means it can't be contained within where as intersects can be.
Either way flip st_intersect with st_overlaps if you really want overlap
check.
SELECT A.*
FROM A LEFT JOIN B ON ST_Intersects(A.the_geom, B.the_geom)
LEFT JOIN C ON ST_Intersects(A.the_geom, C.the_geom)
WHERE B.gid IS NULL AND C.gid IS NULL;
Hope that helps,
Regina
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Paul
Ramsey
Sent: Thursday, April 03, 2008 5:23 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Invoking GIST index on 2 disjoints within a
query
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 <[EMAIL PROTECTED]> 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@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
