41

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 the 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);
  • I had the same thought process, thought that the NOT would also do the trick like any other where condition – Luffydude Sep 20 '18 at 13:21
41

The reason it doesn't work with "not intersects" is that you only compare geometries in pairs; there will be the same problem with disjoint. Every housepoint will disjoint some parcels even if it intersects one parcel.

underdark's suggestion doesn't have that problem. There is also another trick that probably will make more effective use of indexes:

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 are a spatial index and an index on gid in parcels (assuming that id in parcels table is called gid too).

  • 2
    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
  • This query will get planned the same as this one gis.stackexchange.com/a/136177/6052 so it's purely a matter of style which you prefer. =) For those shopping answers. – Evan Carroll Jan 5 '18 at 18:00
14

You may be looking for ST_Disjoint

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

  • 2
    While ST_Disjoint does that, it does not however uses spatial indexes. You are going to wait a loooooong time – nickves Jun 12 '15 at 13:19
9

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
5

Here we use NOT EXISTS and CREATE TABLE AS SELECT (CTAS)

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 NOT EXISTS (
    SELECT 1
    FROM public.parcel AS par 
    WHERE ST_Intersects(hp.the_geom,par.the_geom)
  );
3

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

  • 4
    whoops - need to do a page refresh before answering :-) – Ian Turton Dec 16 '10 at 22:07
1

In some cases is very useful use LATERAL JOIN, it can be very fast It should look like

SELECT * FROM houses h
LEFT JOIN LATERAL (
   SELECT True t FROM parcels p
   WHERE ST_Intersects(p.geom, h.geom)
   LIMIT 1
) p ON True
WHERE p.t IS NULL;
1

Simply using NOT before the ST_Intersects does the trick:

This gets all addresses that are not within neighborhood #62:

select 
a.*
from denver.neighborhoods as n
join denver.addresses as a on not ST_Intersects(n.geom, a.geom)
where n.nbhd_id = '62'

Note the order of the geom columns - polygons first, points second, which is reversed from the usual usage of ST_Intersects.

Quick and simple! Have been wondering how to do this correctly for a while!

  • Also worked for "NOT ST_Within". My query completed in ~30.0 seconds for both the NOT ST_Within and using an outer join then checking for Null on the right side, so there doesn't seem to be any performance hit. Thanks! – Nate Wanner Jan 26 '17 at 17:57
  • @NateWanner good to know! I can't believe how easy and fast that is!!! – DPSSpatial Jan 26 '17 at 18:22
  • This is actually a pretty bad idea because you're getting the cartesian product – Evan Carroll Jan 3 '18 at 2:05
  • @EvanCarroll what does that mean? – DPSSpatial Jan 5 '18 at 16:46
  • It means if you're not just getting 1 denver.address, you're getting one for every not-matching denver.neighborhood. – Evan Carroll Jan 5 '18 at 17:07
-1

This may not be the fastest solution... But I usually just cheat by joining all the features of the other table.

Create table blah as
select
  d.*
from
  data_i_want d,
  (select st_union(geom) geom from not_in_here) n
where
  st_disjoint(d.geom,n.geom);

Nice and snappy if the not_in_here table isn't that complex.

  • That's never snappy. It's just not as unsnappy as it would be if not_in_here is complex. ;) – Evan Carroll Jan 5 '18 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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