[postgis-users] selecting geometries, using 2 polygons

[nicklas.aven at jordogskog.no]

If you test st_intersects aginst the difference of p1 and p2 then you will only get the geometries intersecting both p1 and p2. I anderstood your question as you want all geometries inside p1 bot not if they also intersect p2.
 
Then I suggest this
 
select * from thetable where st_intersects(p1,the_geom) and not st_intersects(p2,the_geom)

I don't know if it is the best solution according to indexes but it should work.
 
/Nicklas

2009-04-28 strk wrote:

On Tue, Apr 28, 2009 at 03:16:04PM +0200, Malm Paul wrote:
>> Hi,
>> I have 2 polygons (rectangeles) p1 and p2, they intersect each other.
>> I would like to select all geometries within p1 excluding all geometries within p2.
>> 
>> How can I do this with postGIS and SQL ?
>
>I suggest you first get p3 as difference(p1, p2),
>then use ST_intersect on p3 and your geom column.
>
>You should be careful in the way you construct the query 
>so that difference() is computed only once, and make sure
>the gist index is used by ST_intersect.
>
>--strk;
>
> Free GIS & Flash consultant/developer () ASCII Ribbon Campaign
> http://foo.keybit.net/~strk/services.html /\ Keep it simple! 
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090428/fd69e81f/attachment.html>