# [postgis-users] Spatial query Help: points not in set of polygons

Obe, Regina robe.dnd at cityofboston.gov
Thu Jan 24 03:41:03 PST 2008

```SELECT a.*
FROM point a LEFT JOIN polygons b
ON (a.the_geom && b.the_geom
and ST_distance(a.the_geom, b.the_geom) = 0.0)
WHERE b.the_geom IS NULL

or

SELECT a.*
FROM point a LEFT JOIN polygons b
ON ST_Within(a.the_geom, b.the_geom)
WHERE b.the_geom IS NULL

I think the second one should be more efficient, but I haven't done any
benchmarks.

Hope that helps,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Stephen Woodbridge
Sent: Wednesday, January 23, 2008 10:27 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Spatial query Help: points not in set of
polygons

Hi all,

I'm drawing a blank on setting up a query for this.

I have a tables points and a table of polygons. I need to find all the
points that are not in any of the polygons.

select a.*
from points a, polygons b
where a.the_geom && b.the_geom
and distance(a.the_geom, b.the_geom) > 0.0;

The problem with this is that if a point is in polygon A it will have a
distance to polygon B, so this in no good.

I thought of doing something like:

select * from points
where distance(the_geom, union((select the_geom from polygons))) >
0.0;

or

select * from points
where distance(the_geom, collect((select the_geom from polygons))) >
0.0;

So is there a better way to do this. Seems like there should be. If not
which of these would you suggest.

Thanks,
-Steve
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended