Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

This seems to me like such a simple question (and it probably is) yet I cannot seem to find an example that gives me the answer. Using PostGIS, I just want to select points that fall outside of polygons. Ultimately this is the inverse of the ST_Intersects, as far as I can see it.

Example: I have a taxlot layer and a address point layer. I assume I should be using the ST_Intersects, but how do I tell it to do teh reverse selection? I thought maybe adding a NOT statement in front of the code below, but that did not work.

CREATE table t_intersect AS
SELECT 
  hp.gid, 
  hp.st_address, 
  hp.city, 
  hp.st_num,
  hp.the_geom
FROM 
  public.parcel as par,
  public.housepoints as hp
WHERE 
  ST_Intersects(hp.the_geom,par.the_geom);

Thanks for the input!

share|improve this question
add comment

4 Answers

up vote 10 down vote accepted

the reason it doesn't work with "not intersects" is that you only compare geometries in pair. and there will be the same problem with disjoint. every housepoint will disjoint some parcels even if it intersects one parcell.

underdarks suggestion doesn.t have that problem. there is also another trick to use that probably will make the use of indexes more effective:

CREATE table t_intersect AS
SELECT 
  hp.gid, 
  hp.st_address, 
  hp.city, 
  hp.st_num,
  hp.the_geom
FROM 
public.housepoints as hp left join
  public.parcel as par on
  ST_Intersects(hp.the_geom,par.the_geom)
where par.gid is null;

the idea is to join them with st_intersects and get the rows where parcel id is not present.

the indexes needed here is spatial index and an index on gid in parcels (assuming that id in parcells table is called gid too).

regards
Nicklas

share|improve this answer
1  
Thank you very much! Nicklas is exactly correct that ST_Disjoint will not produce the correct results. ST_Disjoint returns all features because, as he pointed out, each point is disjointed with some parcel polygons in the table, while this code snippet gave me the results I was hoping for. –  RyanDalton Dec 17 '10 at 0:16
add comment

You may be looking for ST_Disjoint

share|improve this answer
add comment

In case there is no specialized function:

CREATE table t_intersect AS
SELECT 
  hp.gid, 
  hp.st_address, 
  hp.city, 
  hp.st_num,
  hp.the_geom
FROM
  public.housepoints as hp
WHERE
  hp.gid NOT IN 
  (
    SELECT 
      h.gid
    FROM 
      public.parcel as p,
      public.housepoints as h
    WHERE 
      ST_Intersects(h.the_geom,p.the_geom)
  ) AS foo
share|improve this answer
add comment

How about ST_Disjoint? — Returns TRUE if the Geometries do not "spatially intersect" - if they do not share any space together.

share|improve this answer
1  
whoops - need to do a page refresh before answering :-) –  iant Dec 16 '10 at 22:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.