Update, Delete

1. Update
   1. Update a column by a spatial condition
2. Delete
   1. Delete Geometries Contained in Polygons
   2. Delete Geometries NOT Contained in Polygons
   3. Delete Polygons intersecting Polygons in other tables
   4. Delete features which have duplicates within distance

Update

Update a column by a spatial condition

https://gis.stackexchange.com/questions/364391/how-to-refer-to-another-table-in-a-case-when-statement-in-postgis

UPDATE table1
    SET column3 = (
      SELECT 
        CASE
         WHEN table2.column7 >15 THEN 1
          ELSE 0
        END
      FROM table2 
      WHERE ST_INTERSECTS(table1.geom, table2.geom)
     --LIMIT 1
);

Is LIMIT 1 needed?

Delete

Delete Geometries Contained in Polygons

https://gis.stackexchange.com/questions/372549/delete-lines-within-polygon

Use an EXISTS expression. EXISTS terminates the subquery as soon as a single row satisfying the ST_Within condition is found. This is an efficient way when traversing a table by row (as in an UPDATE/DELETE), or otherwise comparing against a pre-selection (e.g. of ids).

DELETE FROM <geom_tbl> AS g
WHERE EXISTS (
  SELECT 1
  FROM   <poly_tbl> AS p
  WHERE  ST_Within(g.geom, p.geom)
);

Delete Geometries NOT Contained in Polygons

https://gis.stackexchange.com/questions/471587/delete-points-in-in-a-certain-area-using-postgis

Use an NOT EXISTS expression. NOT EXISTS terminates the subquery as soon as a single row satisfying the ST_Within condition is found. This is an efficient way when traversing a table by row (as in an UPDATE/DELETE), or otherwise comparing against a pre-selection (e.g. of ids).

DELETE FROM <geom_tbl> AS g
WHERE NOT EXISTS (
  SELECT 1
  FROM   <poly_tbl> AS p
  WHERE  ST_Within(g.geom, p.geom)
);

Delete Polygons intersecting Polygons in other tables

https://stackoverflow.com/questions/71814571/postgis-delete-polygons-that-overlap

Use EXISTS subqueries.

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

Delete features which have duplicates within distance

https://gis.stackexchange.com/questions/356663/postgis-finding-duplicate-label-within-a-radius

Features are duplicate if they have the same value column.

DELETE FROM features f
WHERE EXISTS (
    SELECT  1
    FROM    features
    WHERE   f.val = val AND f.id <> id AND ST_DWithin(f.geom, geom, <distance_in_CRS_units>)
);