Mercurial > gemma
annotate schema/isrs.sql @ 3548:f3102fa16a69
client: data availability/accuracy: inject mapId into layer/styles factory to fix display issue with two maps
This layer displays features based on which other layers are visible. To detect those other layers visibility realiably, the layer
needs to be aware about the map it belongs to.
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Fri, 31 May 2019 12:45:30 +0200 |
parents | 5b3d778db00c |
children | 4c585b5d4fe8 |
rev | line source |
---|---|
1284
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
3 |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
6 |
2556
5b3d778db00c
schema: extend copyright year to 2019
Bernhard Reiter <bernhard@intevation.de>
parents:
2381
diff
changeset
|
7 -- Copyright (C) 2018, 2019 by via donau |
1284
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
10 |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
11 -- Author(s): |
1301
2304778c4432
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1284
diff
changeset
|
12 -- * Tom Gottfried <tom@intevation.de> |
1284
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
13 |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 -- Types and functions for ISRS location codes |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 BEGIN; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 |
2381
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
20 CREATE TABLE countries ( |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
21 country_code char(2) PRIMARY KEY -- ISO 3166 ALPHA-2 code |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
22 ); |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
23 |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
24 CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
25 AS $$ |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
26 SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code) |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
27 $$ |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
28 LANGUAGE sql |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
29 STABLE PARALLEL SAFE; |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
30 |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 -- Types |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre. |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 -- See RIS-Index Encoding Guide |
2381
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
37 CREATE TYPE isrs_base AS ( |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 country_code char(2), -- ISO 3166 country code |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 locode char(3), -- without the country code: |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 -- http://www.unece.org/cefact/locode/welcome.html |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 fairway_section char(5), |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
578
diff
changeset
|
42 orc char(5), -- Object Reference Code |
2381
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
43 hectometre int |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 ); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 |
2381
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
46 CREATE DOMAIN isrs AS isrs_base |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
47 CHECK (is_country((VALUE).country_code)) |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
48 CHECK ((VALUE).hectometre BETWEEN 0 AND 99999); |
99274fed6f3d
Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents:
2081
diff
changeset
|
49 |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 CREATE TYPE isrsrange AS RANGE ( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 subtype = isrs |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 ); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 -- Functions |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 CREATE OR REPLACE FUNCTION isrs_fromText( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
59 isrs_text varchar |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 ) |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 RETURNS isrs |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 AS $$ |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 DECLARE |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
64 isrs_len CONSTANT int = 20; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
65 BEGIN |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 IF char_length(isrs_text) <> isrs_len |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 THEN |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 RAISE 'ISRS location code must be % characters long', isrs_len |
937
72430f050d90
Improve error message for isrs_fromText
Tom Gottfried <tom@intevation.de>
parents:
579
diff
changeset
|
69 USING ERRCODE = 'invalid_parameter_value', |
72430f050d90
Improve error message for isrs_fromText
Tom Gottfried <tom@intevation.de>
parents:
579
diff
changeset
|
70 DETAIL = 'Failing value: ' || isrs_text; |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 ELSE |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 RETURN CAST(( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
73 substring(isrs_text from 1 for 2), |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 substring(isrs_text from 3 for 3), |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 substring(isrs_text from 6 for 5), |
578
61af85a432bf
Fix extraction of object reference code
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
76 substring(isrs_text from 11 for 5), |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
77 CAST(substring(isrs_text from 16 for 5) AS int)) |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 AS isrs); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 END IF; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 END; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 $$ |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 LANGUAGE plpgsql; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 |
2081
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
84 CREATE OR REPLACE FUNCTION isrs_asText( |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
85 code isrs |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
86 ) |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
87 RETURNS text |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
88 AS $$ |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
89 SELECT code.country_code |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
90 || code.locode |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
91 || code.fairway_section |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
92 || code.orc |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
93 || lpad(CAST(code.hectometre AS text), 5, '0'); |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
94 $$ |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
95 LANGUAGE sql |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
96 IMMUTABLE PARALLEL SAFE; |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
97 |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 COMMIT; |