Mercurial > gemma
annotate schema/isrs.sql @ 1158:da75faa8043f
added central moveMap method to pan and zoom the map on certain events
it was requested to prevent zooming out if the zoomlevel is already higher then the desired default zoom (for certain zoom/pan targets)
so there's a parameter preventZoomOut which, if set to true, lets this method only zoom in but not out.
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Tue, 13 Nov 2018 14:22: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; |