I have a table that consists of some PostGIS related data. These data are generated automatically on INSERT or UPDATE.
ALTER TABLE "Room" ADD COLUMN geolocation geography(Polygon, 4326) GENERATED ALWAYS AS (ST_MakePolygon("polygon"::geometry)) STORED;
Sometimes the data provided on the polygon column might not fit the generation function and cause an error. I wanted to handle this error and set a default value when it fail.
— Last resort options —
- Creating postgres functions that handle this task but that would disassociate the work from the codebase.
Advertisement
Answer
As you’re using ST_MakePolygon
I assume that the column "polygon"
contains LINESTRINGs and sometimes they are invalid, which is raising an error in your DDL statement.
What you could do is to create a CASE
conditional that validates the geometry before trying to create the polygon. For instance, this query checks if the geometry is valid, if it is a LINESTRING and if its ring is closed before attempting to create the POLYGON. In case it fails, it generates an empty polygon
(change it to NULL
if you want):
ALTER TABLE "Room" ADD COLUMN geolocation geography(Polygon, 4326) GENERATED ALWAYS AS ( CASE WHEN ST_IsValid("polygon") AND GeometryType("polygon")='LINESTRING' AND ST_IsClosed("polygon"::geometry) THEN ST_MakePolygon("polygon"::geometry) ELSE 'POLYGON EMPTY' END) STORED;
Demo: db<>fiddle