[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] how to select points outside polygons?
From:       Vishal Mehta <vishal.mehta () sei-us ! org>
Date:       2011-09-29 23:28:53
Message-ID: 7FA6F3BE1B6E1148914987B37A25A1DE2AA54D24B1 () MAILR023 ! mail ! lan
[Download RAW message or body]

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@postgis.refractions.net \
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Stephen \
                Woodbridge
Sent: Thursday, September 29, 2011 4:21 PM
To: postgis-users@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@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

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


[prev in list] [next in list] [prev in thread] [next in thread]