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

[Obe, Regina]

Steve,
You are welcome.  You can ignore the error.  Actually I think they are
going to take out that notice in newer versions of Postgis.  It just
means the planning used may not be optimal I think.

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: Thursday, January 24, 2008 9:53 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Spatial query Help: points not in set of
polygons

Regina,

Thank you! I thought this was a LEFT JOIN situation, but my brain just 
was not parsing the problem. I used the 2nd query and it seems to be 
working great.

I'm runnning an older version:

"POSTGIS="1.1.1" GEOS="2.2.1-CAPI-1.0.1" PROJ="Rel. 4.4.9, 29 Oct 2004" 
USE_STATS"
"PostgreSQL 8.1.8 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5

(Debian 1:3.3.5-13)"

So I dropped the "ST_", but I am getting a NOTICE:

NOTICE:  LWGEOM_gist_joinsel called with incorrect join type
CONTEXT:  SQL statement "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"

Do you know why? Is this something that I need to worry about?

Your most excellent support of this list is always noticed by this 
reader. Thank you.

Best regards,
   -Stephen Woodbridge

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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users