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