Mercurial > gemma
comparison 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 |
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 ( |