Superb, Steve, The second option with union worked -( the first did'nt because the geometry type of pca_huc was multipolygon.)
Thanks much! Vishal -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Stephen Woodbridge Sent: Thursday, September 29, 2011 4:21 PM To: [email protected] Subject: Re: [postgis-users] how to select points outside polygons? On 9/29/2011 5:55 PM, Vishal Mehta wrote: > *Hi Steve, * > > ** > > I tried that > > select powerplants.gid, plantname > > from powerplants, pca_huc > > where state ='CA' > > AND NOT st_contains(pca_huc.geom,powerplants.geom) > > order by powerplants.gid; > > but got some 44,000 rows in the result. There are 1477 powerplants > (points) and 299 pca_huc polygons. The powerplants don't all fall > inside the polygons, and I want to get the list of those that fall outside. > > In a desktop gis I can quickly select and see that 102 powerplants > fall > outside: so I should not be getting 44,0000 rows in the result... Ahhh! that is a different problem! you want the points the do not fall in ANY polygon, so you need to first union or collect all you polygons into a single object and then ask which point are out that object. So maybe this will work: select powerplants.gid, plantname from powerplants, (select st_collect(geom) as geom from pca_huc) as pca where state ='CA' AND NOT st_contains(pca.geom,powerplants.geom) order by powerplants.gid; If that does not work then try: select powerplants.gid, plantname from powerplants, (select st_union(geom) as geom from pca_huc) as pca where state ='CA' AND NOT st_contains(pca.geom,powerplants.geom) order by powerplants.gid; -Steve > V > > ** > > ** > > Did you try: > > > > select powerplants.gid, plantname > > from powerplants, pca_huc > > where state ='CA' > > AND NOT st_contains(pca_huc.geom,powerplants.geom) > > order by powerplants.gid; > > > > This should give you all power plants in CA and not in your contains > > clause. What is the coverage of pca_huc table? If this covers all of > CA > > then there is not possible result. The would be like say where a=1 and > > not a=1. > > > > -Steve W > > > > On 9/29/2011 1:21 PM, Vishal Mehta wrote: > >>/ I have a point layer and a polygon layer. How do I select points >>that/ > >>/ are NOT contained within polygons?/ > >>/ / > >>/ For example, this query correctly selects points contained by >>polygons/ > >>/ / > >>/ --/ > >>/ / > >>/ select powerplants.gid, plantname/ > >>/ / > >>/ from powerplants, pca_huc/ > >>/ / > >>/ where state ='CA' / > >>/ / > >>/ AND/ > >>/ / > >>/ st_contains(pca_huc.geom,powerplants.geom)/ > >>/ / > >>/ order by powerplants.gid;/ > >>/ / > >>/ --/ > >>/ / > >>/ I tried to get the inverse selection using st_disjoint instead of/ > >>/ st_contains , but that did not work. I also tried using NOT and >>EXCEPT/ > >>/ without success (although I may not have constructed the latter >>queries/ > >>/ correctly..)/ > >>/ / > >>/ Thanks!/ > >>/ / > >>/ Vishal/ > > /Vishal K. Mehta <http://sei-us.org/about/staff_person/19>, Ph.D. / > > /Staff Scientist, / > > /Stockholm Environment Institute-US / > > /400 F St, Davis, CA 95616 / > > /http://sei-us.org/ / > > // > > > > _______________________________________________ > 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
