Mercurial > gemma

annotate schema/isrs.sql @ 3810:68dc59b428df yworks-svg2pdf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
dogfooding our productionbuild of jspdf and svg2pdf
author Thomas Junk <thomas.junk@intevation.de>
date Fri, 05 Jul 2019 10:31:53 +0200
parents d9ac1c56bfe0
children d3fb2f37380b
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 $$
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
26 SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code)
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
27 $$
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
28 LANGUAGE sql
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
29 STABLE PARALLEL SAFE;
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
30
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 -- Types
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 -- See RIS-Index Encoding Guide
2381
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
37 CREATE TYPE isrs_base AS (
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 country_code char(2), -- ISO 3166 country code
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 locode char(3), -- without the country code:
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 -- http://www.unece.org/cefact/locode/welcome.html
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 fairway_section char(5),
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 578
diff changeset
42 orc char(5), -- Object Reference Code
2381
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
43 hectometre int
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 );
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45
2381
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
46 CREATE DOMAIN isrs AS isrs_base
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
47 CHECK (is_country((VALUE).country_code))
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
48 CHECK ((VALUE).hectometre BETWEEN 0 AND 99999);
99274fed6f3d Validate country and hectometre in ISRS code
Tom Gottfried <tom@intevation.de>
parents: 2081
diff changeset
49
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 $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
61 SELECT isrs_cmp(a, b) < 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
62 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
63 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
64
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
65 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
66 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
67 SELECT isrs_cmp(a, b) <= 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
68 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
69 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
70
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
71 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
72 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
73 SELECT isrs_cmp(a, b) = 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
74 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
75 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
76
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
77 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
78 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
79 SELECT isrs_cmp(a, b) >= 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
80 $$ LANGUAGE sql
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 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
84 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
85 SELECT isrs_cmp(a, b) > 0
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
86 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
87 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
88
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
89 CREATE OPERATOR <~ (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
90 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
91 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
92 function = isrslt
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
93 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
94
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
95 CREATE OPERATOR <~= (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
96 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
97 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
98 function = isrsle
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
99 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
100
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
101 CREATE OPERATOR ~= (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
102 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
103 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
104 function = isrseq,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
105 commutator = ~=
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
106 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
107
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
108 CREATE OPERATOR >~= (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
109 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
110 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
111 function = isrsge,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
112 commutator = <~=,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
113 negator = <~
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
114 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
115
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
116 CREATE OPERATOR >~ (
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
117 leftarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
118 rightarg = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
119 function = isrsgt,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
120 commutator = <~,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
121 negator = <~=
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
122 );
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
123
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
124 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
125 OPERATOR 1 <~,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
126 OPERATOR 2 <~=,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
127 OPERATOR 3 ~=,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
128 OPERATOR 4 >~=,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
129 OPERATOR 5 >~,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
130 FUNCTION 1 isrs_cmp;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
131
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
132 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
133 AS $$
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
134 SELECT CAST(isrs_cmp(a, b) AS double precision)
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
135 $$ LANGUAGE sql
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
136 IMMUTABLE PARALLEL SAFE;
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
137
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
138 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
139 subtype = isrs,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
140 subtype_opclass = isrs_ops,
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
141 subtype_diff = isrs_diff
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
142 );
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
143
3566
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes
Tom Gottfried <tom@intevation.de>
parents: 2556
diff changeset
144
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
145 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
146 -- Functions
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
147 --
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
148
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
149 CREATE OR REPLACE FUNCTION isrs_fromText(
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
150 isrs_text varchar
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
151 )
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
152 RETURNS isrs
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
153 AS $$
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
154 DECLARE
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
155 isrs_len CONSTANT int = 20;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
156 BEGIN
3730
d9ac1c56bfe0 Make isrs_fromText case insensitive.
Sascha Wilde <wilde@intevation.de>
parents: 3566
diff changeset
157 isrs_text = upper(isrs_text);
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
158 IF char_length(isrs_text) <> isrs_len
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
159 THEN
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160 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
161 USING ERRCODE = 'invalid_parameter_value',
72430f050d90 Improve error message for isrs_fromText
Tom Gottfried <tom@intevation.de>
parents: 579
diff changeset
162 DETAIL = 'Failing value: ' || isrs_text;
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
163 ELSE
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
164 RETURN CAST((
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
165 substring(isrs_text from 1 for 2),
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
166 substring(isrs_text from 3 for 3),
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
167 substring(isrs_text from 6 for 5),
578
61af85a432bf Fix extraction of object reference code
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
168 substring(isrs_text from 11 for 5),
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
169 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
170 AS isrs);
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
171 END IF;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
172 END;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
173 $$
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
174 LANGUAGE plpgsql;
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
175
2081
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
176 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
177 code isrs
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
178 )
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
179 RETURNS text
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
180 AS $$
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
181 SELECT code.country_code
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
182 || code.locode
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
183 || code.fairway_section
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
184 || code.orc
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
185 || 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
186 $$
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
187 LANGUAGE sql
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
188 IMMUTABLE PARALLEL SAFE;
40711ca3aa19 Add function to get text representation of location code
Tom Gottfried <tom@intevation.de>
parents: 1301
diff changeset
189
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
190 COMMIT;