Mercurial > gemma
changeset 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 | 5850ac0f9ab6 |
| children | 615b0a9b8098 |
| files | schema/gemma.sql schema/gemma_tests.sql schema/run_tests.sh schema/tap_tests_data.sql |
| diffstat | 4 files changed, 83 insertions(+), 3 deletions(-) [+] |
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
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/gemma_tests.sql Fri Mar 22 14:35:32 2019 +0100 @@ -0,0 +1,33 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2019 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Tom Gottfried <tom@intevation.de> + +-- +-- pgTAP test script for gemma schema definition +-- + +SELECT throws_ok($$ + INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES + (ST_GeogFromText('LINESTRING(0 0, 1 1)'), 'test'), + (ST_GeogFromText('LINESTRING(0 0, 1 1)'), 'test') + $$, + 23505, NULL, + 'No duplicate geometries can be inserted into waterway_axis'); + +SELECT throws_ok($$ + INSERT INTO waterway.waterway_area (area) VALUES + (ST_GeogFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')), + (ST_GeogFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')) + $$, + 23505, NULL, + 'No duplicate geometries can be inserted into waterway_area'); +
--- a/schema/run_tests.sh Fri Mar 22 14:20:08 2019 +0100 +++ b/schema/run_tests.sh Fri Mar 22 14:35:32 2019 +0100 @@ -28,7 +28,8 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(61)' \ + -c 'SELECT plan(63)' \ + -f gemma_tests.sql \ -f isrs_tests.sql \ -f auth_tests.sql \ -f manage_users_tests.sql \
--- a/schema/tap_tests_data.sql Fri Mar 22 14:20:08 2019 +0100 +++ b/schema/tap_tests_data.sql Fri Mar 22 14:35:32 2019 +0100 @@ -119,7 +119,7 @@ ORDER BY id ASC, ids DESC FETCH FIRST ROW ONLY)), cleaned1 AS ( - SELECT geom + SELECT DISTINCT geom FROM (SELECT (ST_Dump(cbuf)).geom FROM cleaned) AS dmp, waterway.waterway_axis WHERE ST_Intersects(geom, wtwaxs))