Mercurial > gemma
annotate schema/updates/1421/01.fairway-marks_wms-t.sql @ 5736:55892008ec96 default tip
Fixed a bunch of corner cases in WG import.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Wed, 29 May 2024 19:02:42 +0200 |
parents | bb2123358bd8 |
children |
rev | line source |
---|---|
4998
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 CREATE TEMP TABLE base_views (name, def) AS VALUES ( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 'fairway_marks_tmpl', $$ |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 SELECT id, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 lower(validity) AS valid_from, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 COALESCE(upper(validity), current_timestamp) AS valid_to, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 geom, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 datsta, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 datend, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 persta, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 perend, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 objnam, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 nobjnm, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 inform, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 ninfom, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 scamin, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 picrep, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 txtdsc, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 sordat, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 sorind, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 %s |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 FROM waterway.fairway_marks_%s |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 $$); |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 -- Settings common to all fairway marks layers |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 UPDATE sys_admin.published_services SET |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 srid = 4326, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 key_column = 'id', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 wmst_attribute = 'valid_from', |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 wmst_end_attribute = 'valid_to' |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 WHERE schema = 'waterway' AND name LIKE 'fairway_marks_%'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 -- Add SQL view definition for fairway marks layers |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 'colour, colpat, conrad, marsys, boyshp, catcam', 'boycar') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 WHERE schema = 'waterway' AND name = 'fairway_marks_boycar'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 'colour, colpat, conrad, marsys, boyshp', 'boysaw') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 WHERE schema = 'waterway' AND name = 'fairway_marks_boysaw'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 'colour, colpat, conrad, marsys, boyshp, catspm', 'boyspp') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 WHERE schema = 'waterway' AND name = 'fairway_marks_boyspp'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 $$colour, condtn, orient, catlit, exclit, litchr, litvis, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status$$, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 'lights') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 WHERE schema = 'waterway' AND name = 'fairway_marks_lights'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 'condtn, siggrp, catrtb, radwal', 'rtpbcn') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 WHERE schema = 'waterway' AND name = 'fairway_marks_rtpbcn'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
59 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 'colour, colpat, condtn, topshp', 'topmar') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 WHERE schema = 'waterway' AND name = 'fairway_marks_topmar'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
64 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
65 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 $$condtn, marsys, orient, status, addmrk, catnmk, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
69 string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
70 $$notmrk LEFT JOIN waterway.fairway_marks_notmrk_dirimps |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 ON id = fm_notmrk_id GROUP BY id$$) |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 WHERE schema = 'waterway' AND name = 'fairway_marks_notmrk'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
73 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 'colour, colpat, condtn, bcnshp, catlam', 'bcnlat_hydro') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
77 WHERE schema = 'waterway' AND name = 'fairway_marks_bcnlat_hydro'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 $$colour, colpat, condtn, bcnshp, catlam, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 $$bcnlat_ienc LEFT JOIN waterway.fairway_marks_bcnlat_dirimps |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
84 ON id = fm_bcnlat_id GROUP BY id$$) |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
85 WHERE schema = 'waterway' AND name = 'fairway_marks_bcnlat_ienc'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
86 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
87 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_hydro') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 WHERE schema = 'waterway' AND name = 'fairway_marks_boylat_hydro'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
92 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
94 'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_ienc') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
95 WHERE schema = 'waterway' AND name = 'fairway_marks_boylat_ienc'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
96 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
97 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 'colour, colpat, condtn, topshp', 'daymar_hydro') |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
100 WHERE schema = 'waterway' AND name = 'fairway_marks_daymar_hydro'; |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
101 |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 UPDATE sys_admin.published_services SET view_def = format( |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
103 (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
104 $$colour, colpat, condtn, topshp, orient, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
105 string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$, |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
106 $$daymar_ienc LEFT JOIN waterway.fairway_marks_daymar_dirimps |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
107 ON id = fm_daymar_id GROUP BY id$$) |
bb2123358bd8
Configure fairway marks layers with time support
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
108 WHERE schema = 'waterway' AND name = 'fairway_marks_daymar_ienc'; |