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