Mercurial > gemma
annotate schema/isrs.sql @ 1157:403b99a43f54
retain profile line when switching to other draw tools
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Tue, 13 Nov 2018 13:40:35 +0100 |
parents | 72430f050d90 |
children | d0a5bb018fc9 |
rev | line source |
---|---|
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 -- Types and functions for ISRS location codes |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 BEGIN; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 -- Types |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 -- 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
|
12 -- See RIS-Index Encoding Guide |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 CREATE TYPE isrs AS ( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 country_code char(2), -- ISO 3166 country code |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 -- could be validated against countries table. |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 locode char(3), -- without the country code: |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 -- http://www.unece.org/cefact/locode/welcome.html |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 fairway_section char(5), |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
578
diff
changeset
|
19 orc char(5), -- Object Reference Code |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 hectometre int -- should be constrained to five digits |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 ); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 CREATE TYPE isrsrange AS RANGE ( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 subtype = isrs |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 ); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 -- Functions |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 -- |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 CREATE OR REPLACE FUNCTION isrs_fromText( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 isrs_text varchar |
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 RETURNS isrs |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 AS $$ |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 DECLARE |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 isrs_len CONSTANT int = 20; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 BEGIN |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 IF char_length(isrs_text) <> isrs_len |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 THEN |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 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
|
42 USING ERRCODE = 'invalid_parameter_value', |
72430f050d90
Improve error message for isrs_fromText
Tom Gottfried <tom@intevation.de>
parents:
579
diff
changeset
|
43 DETAIL = 'Failing value: ' || isrs_text; |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 ELSE |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 RETURN CAST(( |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 substring(isrs_text from 1 for 2), |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 substring(isrs_text from 3 for 3), |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 substring(isrs_text from 6 for 5), |
578
61af85a432bf
Fix extraction of object reference code
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
49 substring(isrs_text from 11 for 5), |
569
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 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
|
51 AS isrs); |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 END IF; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 END; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 $$ |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 LANGUAGE plpgsql; |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 |
ad07846b09d1
Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 COMMIT; |