Mercurial > gemma
annotate schema/isrs.sql @ 2351:9c4d6a61ad1d
staging: fix vanishing buttons when (U)BN list becomes too long
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Wed, 20 Feb 2019 11:08:20 +0100 |
parents | 40711ca3aa19 |
children | 99274fed6f3d |
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 |
d0a5bb018fc9
add header with license and author infos to one file
Bernhard Reiter <bernhard@intevation.de>
parents:
937
diff
changeset
|
7 -- Copyright (C) 2018 by via donau |
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 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 -- Types |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 -- 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
|
25 -- See RIS-Index Encoding Guide |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 CREATE TYPE isrs AS ( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 country_code char(2), -- ISO 3166 country code |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 -- could be validated against countries table. |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 locode char(3), -- without the country code: |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 -- http://www.unece.org/cefact/locode/welcome.html |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 fairway_section char(5), |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
578
diff
changeset
|
32 orc char(5), -- Object Reference Code |
2081
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
33 hectometre int -- TODO: should be constrained to five digits |
569
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 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 CREATE TYPE isrsrange AS RANGE ( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 subtype = isrs |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 ); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 -- Functions |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 CREATE OR REPLACE FUNCTION isrs_fromText( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 isrs_text varchar |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 ) |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 RETURNS isrs |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 AS $$ |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 DECLARE |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 isrs_len CONSTANT int = 20; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 BEGIN |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 IF char_length(isrs_text) <> isrs_len |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 THEN |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 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
|
55 USING ERRCODE = 'invalid_parameter_value', |
72430f050d90
Improve error message for isrs_fromText
Tom Gottfried <tom@intevation.de>
parents:
579
diff
changeset
|
56 DETAIL = 'Failing value: ' || isrs_text; |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 ELSE |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 RETURN CAST(( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
59 substring(isrs_text from 1 for 2), |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 substring(isrs_text from 3 for 3), |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 substring(isrs_text from 6 for 5), |
578
61af85a432bf
Fix extraction of object reference code
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
62 substring(isrs_text from 11 for 5), |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 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
|
64 AS isrs); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
65 END IF; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 END; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 $$ |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 LANGUAGE plpgsql; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
69 |
2081
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
70 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
|
71 code isrs |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
72 ) |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
73 RETURNS text |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
74 AS $$ |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
75 SELECT code.country_code |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
76 || code.locode |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
77 || code.fairway_section |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
78 || code.orc |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
79 || 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
|
80 $$ |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
81 LANGUAGE sql |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
82 IMMUTABLE PARALLEL SAFE; |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
83 |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
84 COMMIT; |