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

[Stephen Woodbridge]

Regina,

I think I spoke too soon. On closer inspection of the results it looks 
like the query is returning all the points in the points table.

select a.file, a.id, a.name
   from points a LEFT JOIN polygons b
     ON (a.the_geom && b.the_geom
         and distance(a.the_geom, b.the_geom) = 0.0)
  where b.the_geom IS NULL

I think this has a logic issue, IF b.the_geom is NULL then the ON clause 
must also be NULL. Does that work?

I also tried this query, which returned no points:

select file, id, name
   from points
  where distance(the_geom, (select collect(the_geom) from polygons)) > 0.0';

Any thoughts why this does not appear to work?

I'm going to try your other query and see if I get different results 
with that.

Thanks,
   -Steve

Obe, Regina wrote:
> 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
> solely for the addressee. If you received this in error, please
> contact the sender and delete the material from any computer.
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users