[postgis-users] how to select points outside polygons?

[Vishal Mehta]

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...

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/



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110929/fe9a2001/attachment.html>