[postgis-users] how to select points outside polygons?
Vishal Mehta
vishal.mehta at sei-us.org
Thu Sep 29 16:28:53 PDT 2011
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: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen Woodbridge
Sent: Thursday, September 29, 2011 4:21 PM
To: postgis-users at postgis.refractions.net
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
> 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
More information about the postgis-users
mailing list