Mercurial > gemma

annotate schema/isrs.sql @ 5684:536e842d9bfa sr-v2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Reorder vertices in tins to minimize delta distances.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Sun, 11 Feb 2024 22:32:55 +0100
parents 8fcabb6f971e
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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;