Mercurial > gemma

annotate schema/updates/1421/01.fairway-marks_wms-t.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
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';