2

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.

2

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.

  • thanks! Besides functions, is there a mechanism to set default values for a geometry, e.g. via DDL, the way one can for any other kind of column? Conceptually it is the first idea that springs to my feeble mind, and I have certainly checked the docs, but I'm no expert and could easily have missed something. – MC5 Apr 21 '16 at 13:41
  • @MC5 I've added a trigger function example to set these as defaults. It's untested, so give it a try. – Mike T Apr 22 '16 at 0:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.