Skip to main content
added 101 characters in body
Source Link
Evan Carroll
  • 7.1k
  • 2
  • 33
  • 62

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

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)
  );
Edited formatting
Source Link
Joseph
  • 76.3k
  • 7
  • 172
  • 286

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 * From public.parcel as par WHERE ST_Intersects(hp.the_geom,par.the_geom))

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 * From public.parcel as par 
WHERE
  ST_Intersects(hp.the_geom,par.the_geom))

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 * From public.parcel as par WHERE ST_Intersects(hp.the_geom,par.the_geom))

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 * From public.parcel as par 
WHERE
  ST_Intersects(hp.the_geom,par.the_geom))
Source Link

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 * From public.parcel as par WHERE ST_Intersects(hp.the_geom,par.the_geom))