Mercurial > gemma
diff schema/gemma.sql @ 2781:a2127495093e
Prevent duplicate waterway axis and area geometries
Duplicate axis geometries can prevent finding a contiguous axis
between two distance marks in the current implementation of
ISRSrange_axis(). Although duplicate area geometries did not show
to be harmful up to now, they do not seem to be useful and generally
duplicate geometries have the potential to make debugging of any
geometry processing harder.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 22 Mar 2019 14:35:32 +0100 |
parents | ab1a22052437 |
children | 3b1bdbbc0225 |
line wrap: on
line diff
--- a/schema/gemma.sql Fri Mar 22 14:20:08 2019 +0100 +++ b/schema/gemma.sql Fri Mar 22 14:35:32 2019 +0100 @@ -16,12 +16,15 @@ BEGIN; -- --- Infrastructure +-- Extensions -- CREATE EXTENSION postgis; -- needed for multi-column GiST indexes with otherwise unsupported types: CREATE EXTENSION btree_gist; +-- +-- Trigger functions +-- -- TODO: will there ever be UPDATEs or can we drop that function due to -- historicisation? CREATE FUNCTION update_date_info() RETURNS trigger @@ -33,6 +36,42 @@ END; $$; +-- Trigger function to be used as a constraint trigger to enforce uniqueness +-- of geometries in the column with its name given as an argument to the +-- trigger function +CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS +$$ +DECLARE + new_geom geometry; + has_equal boolean; +BEGIN + EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0]) + INTO new_geom + USING NEW; + EXECUTE format( + 'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I ' + 'WHERE id <> $2', + TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME) + INTO has_equal + USING new_geom, NEW.id; + IF has_equal THEN + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('Failing row contains geometry in %s', + Box2D(new_geom)), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; + RETURN NEW; +END; +$$ +LANGUAGE plpgsql; + -- -- GEMMA data -- @@ -222,6 +261,10 @@ catccl smallint REFERENCES catccls, dirimp smallint REFERENCES dirimps ) + CREATE CONSTRAINT TRIGGER waterway_area_area_unique + AFTER INSERT OR UPDATE OF area ON waterway_area + FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area') + CREATE TABLE gauges ( location isrs PRIMARY KEY CHECK( @@ -308,6 +351,9 @@ objnam varchar NOT NULL, nobjnam varchar ) + CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique + AFTER INSERT OR UPDATE OF wtwaxs ON waterway_axis + FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs') -- This table allows linkage between 1D ISRS location codes and 2D space -- e.g. for cutting bottleneck area out of waterway area based on virtual