PostGIS - default spatial reference and Z value on insert or geometry update?

Question

I have a spatial table with a geometry column geometry(MultiPolygonZ,4326).

We are trying to handle inserts and updates of geometries as GeoJSON from other systems of varying vintage. Everything is in reality 4326, but sometimes a geometry arrives that lacks the "crs" member, or the geometry might be XY instead of XYZ.

So those cases fail with either

• ERROR: Geometry SRID (0) does not match column SRID (4326)
• ERROR: Column has Z dimension but geometry does not.

I'd like to know if I can somehow set defaults on the geometry column for the SR (4326) and Z (0.0) to handle those cases.

Answer 1

Use ST_SetSRID and ST_Force3D (or ST_Force_3D) functions to force these geometry types.

---

To assign these as defaults for each geometry in a table, you could use a trigger function that evaluates the geometry before it is added in the database.

CREATE OR REPLACE FUNCTION force_geom_z_4326() RETURNS trigger AS
$BODY$BEGIN
  NEW.geom = ST_Force3D(ST_SetSRID(NEW.geom, 4326));
  RETURN NEW;
END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

CREATE TRIGGER my_table_tg
  BEFORE INSERT OR UPDATE
  ON my_table
  FOR EACH ROW
  EXECUTE PROCEDURE force_geom_z_4326();

The same trigger function can be re-used on other tables that need these defaults, and have a column named geom.