PostGIS: Delete polygons that overlap-CodePudding

I have four different polygon shapefiles and need to delete the polygons from polygon "a" that intersect polygons "b,c and d" leaving a table with only polygons in "a" that don't intersect any of the other layers. Is there a way to do this in PostGIS?

a(id,geom) b(id,geom) c(id,geom) d(id,geom)

CodePudding user response：

Use EXISTS() expression, e.g.:

DELETE 
  FROM <table_a> a
 WHERE EXISTS
     ( SELECT 1
         FROM <table_b> b
        WHERE ST_Intersects(a.geom,b.geom)
     )
    or EXISTS
     ( SELECT 1
         FROM <table_c> c
        WHERE ST_Intersects(a.geom,c.geom)
     )
    or EXISTS
     ( SELECT 1
         FROM <table_d> d
        WHERE ST_Intersects(a.geom,d.geom)
     )