Mercurial > gemma

annotate schema/updates/1431/01.bottlenecks_geoserver_add_time.sql @ 5098:52aac557cbd7 queued-stage-done

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default intp 'queued-stage-done' branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 24 Mar 2020 13:07:24 +0100
parents schema/updates/1430/01.bottlenecks_geoserver_add_time.sql@8dc27fc1d05c
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5092
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE TEMP TABLE base_views (name, def) AS VALUES (
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 'gauges_base_view', $$
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 SELECT
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 g.location,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 isrs_asText(g.location) AS isrs_code,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 g.objname,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 g.geom,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 g.applicability_from_km,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 g.applicability_to_km,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 g.validity,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 g.zero_point,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 g.geodref,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 g.date_info,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 g.source_organization,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 g.erased,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 r.rwls AS reference_water_levels,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 wl.measure_date AS gm_measuredate,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 wl.water_level AS gm_waterlevel,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 wl.n AS gm_n_14d,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 fca.forecast_accuracy_3d,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 fca.forecast_accuracy_1d
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 FROM waterway.gauges g
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 LEFT JOIN (SELECT location, validity,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 json_strip_nulls(json_object_agg(
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 coalesce(depth_reference, 'empty'), value)) AS rwls
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 FROM waterway.gauges_reference_water_levels
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 GROUP BY location, validity) AS r
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 USING (location, validity)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 LEFT JOIN (SELECT DISTINCT ON (location)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 location,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 date_issue,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 measure_date,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 water_level,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 count(*) OVER (PARTITION BY location) AS n
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 FROM waterway.gauge_measurements
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 WHERE measure_date
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 >= current_timestamp - '14 days 00:15'::interval
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 ORDER BY location, measure_date DESC) AS wl
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 USING (location)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 LEFT JOIN (SELECT DISTINCT ON (location)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 location,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 date_issue,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 max(acc) FILTER (WHERE measure_date
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 <= current_timestamp + '1 day'::interval)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 OVER loc_date_issue AS forecast_accuracy_1d,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 FROM (SELECT location, date_issue, measure_date,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 GREATEST(water_level - lower(conf_interval),
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 upper(conf_interval) - water_level) AS acc
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 FROM waterway.gauge_predictions
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 WHERE date_issue
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 >= current_timestamp - '14 days 00:15'::interval
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 AND measure_date BETWEEN current_timestamp
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 AND current_timestamp + '3 days'::interval) AS acc
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 ORDER BY location, date_issue DESC) AS fca
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 ON fca.location = g.location AND fca.date_issue >= wl.date_issue
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 $$);
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 UPDATE sys_admin.published_services
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 SET
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 wmst_attribute = 'valid_from',
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 wmst_end_attribute = 'valid_to',
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 view_def = $$
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 SELECT
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 b.id,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 lower(b.validity) AS valid_from,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 COALESCE(upper(b.validity), current_timestamp) AS valid_to,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 b.bottleneck_id,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 b.objnam,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 b.nobjnm,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 b.area,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 b.rb,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 b.lb,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 b.responsible_country,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 b.revisiting_time,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 b.limiting,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 b.date_info,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 b.source_organization,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 g.objname AS gauge_objname,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 g.reference_water_levels,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 fal.date_info AS fa_date_info,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 fal.critical AS fa_critical,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 g.gm_measuredate,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 g.gm_waterlevel,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 g.gm_n_14d,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 srl.date_max,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 g.forecast_accuracy_3d,
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 g.forecast_accuracy_1d
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 FROM waterway.bottlenecks b
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 LEFT JOIN (
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 ) AS g
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 ON b.gauge_location = g.location
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 AND g.validity @> current_timestamp
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 bottleneck_id, date_info, critical
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 FROM waterway.fairway_availability
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 ORDER BY bottleneck_id, date_info DESC) AS fal
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 ON b.bottleneck_id = fal.bottleneck_id
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 bottleneck_id, max(date_info) AS date_max
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 FROM waterway.sounding_results
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 GROUP BY bottleneck_id
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 ORDER BY bottleneck_id DESC) AS srl
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106 ON b.bottleneck_id = srl.bottleneck_id
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 $$
8dc27fc1d05c Add temporal validity attributes and WMS-T config to bottlenecks layer
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 WHERE schema = 'waterway' AND name = 'bottlenecks_geoserver';