Mercurial > gemma

annotate schema/isrs.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +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;