Mercurial > gemma

annotate schema/updates/1421/01.fairway-marks_wms-t.sql @ 5539:90ba92820b26 aggregate-gm-import-logging

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default into aggregate-gm-import-logging branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 26 Oct 2021 00:05:28 +0200
parents bb2123358bd8
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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';