Mercurial > gemma
comparison schema/updates/1309/01.expose_section_country.sql @ 4742:b88ab93dcb2c
Expose section country in GeoServer layer
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 18 Oct 2019 12:18:23 +0200 |
parents | |
children |
comparison
equal
deleted
inserted
replaced
4741:5164b4450c42 | 4742:b88ab93dcb2c |
---|---|
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 -- consider all measurements within 14 days plus a tolerance | |
37 WHERE measure_date | |
38 >= current_timestamp - '14 days 00:15'::interval | |
39 ORDER BY location, measure_date DESC) AS wl | |
40 USING (location) | |
41 LEFT JOIN (SELECT DISTINCT ON (location) | |
42 location, | |
43 date_issue, | |
44 max(acc) FILTER (WHERE measure_date | |
45 <= current_timestamp + '1 day'::interval) | |
46 OVER loc_date_issue AS forecast_accuracy_1d, | |
47 max(acc) OVER loc_date_issue AS forecast_accuracy_3d | |
48 FROM (SELECT location, date_issue, measure_date, | |
49 GREATEST(water_level - lower(conf_interval), | |
50 upper(conf_interval) - water_level) AS acc | |
51 FROM waterway.gauge_predictions | |
52 -- consider predictions made within last 14 days ... | |
53 WHERE date_issue | |
54 >= current_timestamp - '14 days 00:15'::interval | |
55 -- ... for the next three days from now | |
56 AND measure_date BETWEEN current_timestamp | |
57 AND current_timestamp + '3 days'::interval) AS acc | |
58 WINDOW loc_date_issue AS (PARTITION BY location, date_issue) | |
59 ORDER BY location, date_issue DESC) AS fca | |
60 -- Show only forecasts issued with latest measurements or later | |
61 ON fca.location = g.location AND fca.date_issue >= wl.date_issue | |
62 $$); | |
63 | |
64 UPDATE sys_admin.published_services SET view_def = $$ | |
65 SELECT | |
66 s.id, | |
67 s.name, | |
68 (s.section).lower::varchar as lower, | |
69 (s.section).upper::varchar as upper, | |
70 s.area, | |
71 s.objnam, | |
72 s.nobjnam, | |
73 s.country, | |
74 s.date_info, | |
75 s.source_organization, | |
76 s.staging_done, | |
77 min(g.gm_measuredate) AS gm_measuredate, | |
78 min(g.gm_n_14d) AS gm_n_14d, | |
79 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, | |
80 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d | |
81 FROM waterway.sections s | |
82 LEFT JOIN ( | |
83 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ | |
84 WHERE NOT erased) AS g | |
85 ON g.location <@ s.section | |
86 GROUP BY s.id | |
87 $$ | |
88 WHERE schema = 'waterway' AND name = 'sections_geoserver' |