Mercurial > gemma

comparison schema/gemma.sql @ 2781:a2127495093e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
comparison
equal deleted inserted replaced
2780:5850ac0f9ab6 2781:a2127495093e
14 -- * Sascha Wilde <sascha.wilde@intevation.de> 14 -- * Sascha Wilde <sascha.wilde@intevation.de>
15 15
16 BEGIN; 16 BEGIN;
17 17
18 -- 18 --
19 -- Infrastructure 19 -- Extensions
20 -- 20 --
21 CREATE EXTENSION postgis; 21 CREATE EXTENSION postgis;
22 -- needed for multi-column GiST indexes with otherwise unsupported types: 22 -- needed for multi-column GiST indexes with otherwise unsupported types:
23 CREATE EXTENSION btree_gist; 23 CREATE EXTENSION btree_gist;
24 24
25 --
26 -- Trigger functions
27 --
25 -- TODO: will there ever be UPDATEs or can we drop that function due to 28 -- TODO: will there ever be UPDATEs or can we drop that function due to
26 -- historicisation? 29 -- historicisation?
27 CREATE FUNCTION update_date_info() RETURNS trigger 30 CREATE FUNCTION update_date_info() RETURNS trigger
28 LANGUAGE plpgsql 31 LANGUAGE plpgsql
29 AS $$ 32 AS $$
30 BEGIN 33 BEGIN
31 NEW.date_info = CURRENT_TIMESTAMP; 34 NEW.date_info = CURRENT_TIMESTAMP;
32 RETURN NEW; 35 RETURN NEW;
33 END; 36 END;
34 $$; 37 $$;
38
39 -- Trigger function to be used as a constraint trigger to enforce uniqueness
40 -- of geometries in the column with its name given as an argument to the
41 -- trigger function
42 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
43 $$
44 DECLARE
45 new_geom geometry;
46 has_equal boolean;
47 BEGIN
48 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
49 INTO new_geom
50 USING NEW;
51 EXECUTE format(
52 'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I '
53 'WHERE id <> $2',
54 TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME)
55 INTO has_equal
56 USING new_geom, NEW.id;
57 IF has_equal THEN
58 RAISE EXCEPTION
59 'new row for relation "%" violates constraint trigger "%"',
60 TG_TABLE_NAME, TG_NAME
61 USING
62 DETAIL = format('Failing row contains geometry in %s',
63 Box2D(new_geom)),
64 ERRCODE = 23505,
65 SCHEMA = TG_TABLE_SCHEMA,
66 TABLE = TG_TABLE_NAME,
67 COLUMN = TG_ARGV[0],
68 CONSTRAINT = TG_NAME;
69 END IF;
70 RETURN NEW;
71 END;
72 $$
73 LANGUAGE plpgsql;
35 74
36 -- 75 --
37 -- GEMMA data 76 -- GEMMA data
38 -- 77 --
39 78
220 area geography(POLYGON, 4326) NOT NULL 259 area geography(POLYGON, 4326) NOT NULL
221 CHECK(ST_IsValid(CAST(area AS geometry))), 260 CHECK(ST_IsValid(CAST(area AS geometry))),
222 catccl smallint REFERENCES catccls, 261 catccl smallint REFERENCES catccls,
223 dirimp smallint REFERENCES dirimps 262 dirimp smallint REFERENCES dirimps
224 ) 263 )
264 CREATE CONSTRAINT TRIGGER waterway_area_area_unique
265 AFTER INSERT OR UPDATE OF area ON waterway_area
266 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area')
267
225 268
226 CREATE TABLE gauges ( 269 CREATE TABLE gauges (
227 location isrs PRIMARY KEY CHECK( 270 location isrs PRIMARY KEY CHECK(
228 (location).orc SIMILAR TO 'G[[:digit:]]{4}' 271 (location).orc SIMILAR TO 'G[[:digit:]]{4}'
229 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), 272 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
306 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), 349 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),
307 -- TODO: Do we need to check data set quality (DRC 2.1.6)? 350 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
308 objnam varchar NOT NULL, 351 objnam varchar NOT NULL,
309 nobjnam varchar 352 nobjnam varchar
310 ) 353 )
354 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
355 AFTER INSERT OR UPDATE OF wtwaxs ON waterway_axis
356 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs')
311 357
312 -- This table allows linkage between 1D ISRS location codes and 2D space 358 -- This table allows linkage between 1D ISRS location codes and 2D space
313 -- e.g. for cutting bottleneck area out of waterway area based on virtual 359 -- e.g. for cutting bottleneck area out of waterway area based on virtual
314 -- distance marks along waterway axis (see SUC7). 360 -- distance marks along waterway axis (see SUC7).
315 CREATE TABLE distance_marks_virtual ( 361 CREATE TABLE distance_marks_virtual (