Deleting NULL geometries and duplicate nodes from table using PostGIS

Question

I run the command to delete null geometry from a table:

delete from table where geom is null

And delete duplicate nodes by running the command:

update table set geom = ST_Multi(ST_Simplify(geom,0));

How to run the above commands in all tables using a loop?

Answer 1

This requires a procedural execution of dynamic SQL - best applied with a DO block.

Since we are only looking at modifying columns of type GEOMETRY, we can identify the respective relations easily through the geometry_columns View:

DO
$DO$
  DECLARE
    r record;
    
  BEGIN 
    FOR r in (
      SELECT
        srid,
        f_table_name,
        f_table_schema,
        f_geometry_column
      FROM
        geometry_columns
      -- WHERE
      --  <FILTER>
    ) LOOP
      RAISE NOTICE 'Deleting from table %.% [Geometry column: % | SRID: %]...', r.f_table_schema, r.f_table_name, r.f_geometry_column, r.srid;
       
      EXECUTE FORMAT(
        $FORMAT$
          DELETE
          FROM
            %1$I.%2$I
          WHERE
            %3$I IS NULL
          );
        $FORMAT$,
        r.f_table_schema, r.f_table_name, r.f_geometry_column
      );
      --COMMIT;
      
      RAISE NOTICE 'Updating table %.% [Geometry column: % | SRID: %]...', r.f_table_schema, r.f_table_name, r.f_geometry_column, r.srid;
       
      EXECUTE FORMAT(
        $FORMAT$
          UPDATE
            %1$I.%2$I
          SET %3$I = ST_SetSRID(
                       ST_RemoveRepeatedPoints(%3$I, 0),
                       %4%s
                     )
          ;
        $FORMAT$,
        r.f_table_schema, r.f_table_name, r.f_geometry_column, r.srid
      );
      
      --COMMIT;
    END LOOP;
  END;
$DO$
;

Note:

I use ST_RemoveRepeatedPoints instead of the more costly ST_Simplify - seems to be what you are after.

---

In PostgreSQL 11.0 and above, DO blocks allow for explicit Transaction Management; adding intermediate COMMITs (commented out in the code snippet above) between operations (i.e. DELETE & UPDATE) should greatly increase performance for many and/or large tables, as the intermediate snapshots of those modifications do not need to be kept isolated.

Keep in mind, though, that a COMMIT persists changes immediately, so any error encountered at a later point in the execution loop cannot ROLLBACK earlier changes!

---

Make sure you test and verify the record set passed to the FOR loop prior to executing the DO block; apply any schema or table filter logic needed:

SELECT
  srid,
  f_table_name,
  f_table_schema,
  f_geometry_column
FROM
  geometry_columns
-- WHERE
--   <FILTER>
;