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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 > [email protected] > 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
