Mercurial > gemma
annotate schema/isrs.sql @ 5202:fbc79c8459b4 new-fwa
Load water values, too.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 08 May 2020 15:59:44 +0200 |
parents | 8fcabb6f971e |
children |
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 $$ |
4173
d3fb2f37380b
Schema qualify tables in function body
Tom Gottfried <tom@intevation.de>
parents:
3730
diff
changeset
|
26 SELECT EXISTS(SELECT 1 FROM public.countries WHERE country_code = code) |
2381
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 |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
50 CREATE FUNCTION isrs_cmp(a isrs, b isrs) RETURNS int |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
51 AS $$ |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
52 /* TODO: handle non-matching combinations of country_codes and |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
53 fairway_sections. Otherwise, this will give unexpected results if |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
54 both hectometre values do not refer to the same river. */ |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
55 SELECT (a).hectometre - (b).hectometre |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
56 $$ LANGUAGE sql |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
57 IMMUTABLE PARALLEL SAFE; |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
58 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
59 CREATE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
60 AS $$ |
4431
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
61 BEGIN |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
62 RETURN public.isrs_cmp(a, b) < 0; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
63 END; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
64 $$ LANGUAGE plpgsql |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
65 IMMUTABLE PARALLEL SAFE; |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
66 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
67 CREATE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
68 AS $$ |
4431
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
69 BEGIN |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
70 RETURN public.isrs_cmp(a, b) <= 0; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
71 END; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
72 $$ LANGUAGE plpgsql |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
73 IMMUTABLE PARALLEL SAFE; |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
74 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
75 CREATE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
76 AS $$ |
4431
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
77 BEGIN |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
78 RETURN public.isrs_cmp(a, b) = 0; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
79 END; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
80 $$ LANGUAGE plpgsql |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
81 IMMUTABLE PARALLEL SAFE; |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
82 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
83 CREATE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
84 AS $$ |
4431
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
85 BEGIN |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
86 RETURN public.isrs_cmp(a, b) >= 0; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
87 END; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
88 $$ LANGUAGE plpgsql |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
89 IMMUTABLE PARALLEL SAFE; |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
90 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
91 CREATE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
92 AS $$ |
4431
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
93 BEGIN |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
94 RETURN public.isrs_cmp(a, b) > 0; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
95 END; |
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
96 $$ LANGUAGE plpgsql |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
97 IMMUTABLE PARALLEL SAFE; |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
98 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
99 CREATE OPERATOR <~ ( |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
100 leftarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
101 rightarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
102 function = isrslt |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
103 ); |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
104 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
105 CREATE OPERATOR <~= ( |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
106 leftarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
107 rightarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
108 function = isrsle |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
109 ); |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
110 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
111 CREATE OPERATOR ~= ( |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
112 leftarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
113 rightarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
114 function = isrseq, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
115 commutator = ~= |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
116 ); |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
117 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
118 CREATE OPERATOR >~= ( |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
119 leftarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
120 rightarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
121 function = isrsge, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
122 commutator = <~=, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
123 negator = <~ |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
124 ); |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
125 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
126 CREATE OPERATOR >~ ( |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
127 leftarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
128 rightarg = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
129 function = isrsgt, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
130 commutator = <~, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
131 negator = <~= |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
132 ); |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
133 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
134 CREATE OPERATOR CLASS isrs_ops FOR TYPE isrs USING btree AS |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
135 OPERATOR 1 <~, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
136 OPERATOR 2 <~=, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
137 OPERATOR 3 ~=, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
138 OPERATOR 4 >~=, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
139 OPERATOR 5 >~, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
140 FUNCTION 1 isrs_cmp; |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
141 |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
142 CREATE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
143 AS $$ |
4431
8fcabb6f971e
Fix operator support functions
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
144 SELECT CAST(public.isrs_cmp(a, b) AS double precision) |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
145 $$ LANGUAGE sql |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
146 IMMUTABLE PARALLEL SAFE; |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
147 |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
148 CREATE TYPE isrsrange AS RANGE ( |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
149 subtype = isrs, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
150 subtype_opclass = isrs_ops, |
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
151 subtype_diff = isrs_diff |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
152 ); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
153 |
3566
4c585b5d4fe8
Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents:
2556
diff
changeset
|
154 |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
155 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
156 -- Functions |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
157 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
158 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
159 CREATE OR REPLACE FUNCTION isrs_fromText( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
160 isrs_text varchar |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
161 ) |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
162 RETURNS isrs |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
163 AS $$ |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
164 DECLARE |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
165 isrs_len CONSTANT int = 20; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
166 BEGIN |
3730
d9ac1c56bfe0
Make isrs_fromText case insensitive.
Sascha Wilde <wilde@intevation.de>
parents:
3566
diff
changeset
|
167 isrs_text = upper(isrs_text); |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
168 IF char_length(isrs_text) <> isrs_len |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
169 THEN |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
170 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
|
171 USING ERRCODE = 'invalid_parameter_value', |
72430f050d90
Improve error message for isrs_fromText
Tom Gottfried <tom@intevation.de>
parents:
579
diff
changeset
|
172 DETAIL = 'Failing value: ' || isrs_text; |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
173 ELSE |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
174 RETURN CAST(( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
175 substring(isrs_text from 1 for 2), |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
176 substring(isrs_text from 3 for 3), |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
177 substring(isrs_text from 6 for 5), |
578
61af85a432bf
Fix extraction of object reference code
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
178 substring(isrs_text from 11 for 5), |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
179 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
|
180 AS isrs); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
181 END IF; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
182 END; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
183 $$ |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
184 LANGUAGE plpgsql; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
185 |
2081
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
186 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
|
187 code isrs |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
188 ) |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
189 RETURNS text |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
190 AS $$ |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
191 SELECT code.country_code |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
192 || code.locode |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
193 || code.fairway_section |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
194 || code.orc |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
195 || 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
|
196 $$ |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
197 LANGUAGE sql |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
198 IMMUTABLE PARALLEL SAFE; |
40711ca3aa19
Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
199 |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
200 COMMIT; |