Selecting features that do NOT intersect in PostGIS

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


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.


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