Selecting features that do NOT intersect in PostGIS

Question

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);

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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)
  );

Answer 5

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

Answer 6

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;

Answer 7

You can simply use st_difference. Have not tested your example but it would be something like this:

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_Difference(hp.the_geom,par.the_geom);

Answer 8

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!

Answer 9

Came across this recently. I am not stating that this is the optimal solution for large-scale datasets. However, it is an alternative that is a little easier to interpret and understand than all the solutions using joins.

Performance-wise it worked out pretty well for me on a large dataset.

select * from <tableA> 
where <tableA>.id not in (
    select <tableA>.id from <tableA> , <tableB> where <tableA>.geom && 
    <tableB>.geom and st_intersects(<tableA>.geom, <tableB>.geom)
)

Answer 10

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.