Mercurial > gemma
annotate schema/updates/1309/01.expose_section_country.sql @ 5736:55892008ec96 default tip
Fixed a bunch of corner cases in WG import.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Wed, 29 May 2024 19:02:42 +0200 |
parents | b88ab93dcb2c |
children |
rev | line source |
---|---|
4742
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 CREATE TEMP TABLE base_views (name, def) AS VALUES ( |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 'gauges_base_view', $$ |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 SELECT |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 g.location, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 isrs_asText(g.location) AS isrs_code, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 g.objname, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 g.geom, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 g.applicability_from_km, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 g.applicability_to_km, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 g.validity, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 g.zero_point, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 g.geodref, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 g.date_info, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 g.source_organization, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 g.erased, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 r.rwls AS reference_water_levels, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 wl.measure_date AS gm_measuredate, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 wl.water_level AS gm_waterlevel, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 wl.n AS gm_n_14d, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 fca.forecast_accuracy_3d, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 fca.forecast_accuracy_1d |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 FROM waterway.gauges g |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 LEFT JOIN (SELECT location, validity, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 json_strip_nulls(json_object_agg( |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 coalesce(depth_reference, 'empty'), value)) AS rwls |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 FROM waterway.gauges_reference_water_levels |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 GROUP BY location, validity) AS r |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 USING (location, validity) |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 LEFT JOIN (SELECT DISTINCT ON (location) |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 location, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 date_issue, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 measure_date, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 water_level, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 count(*) OVER (PARTITION BY location) AS n |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 FROM waterway.gauge_measurements |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 -- consider all measurements within 14 days plus a tolerance |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 WHERE measure_date |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 >= current_timestamp - '14 days 00:15'::interval |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 ORDER BY location, measure_date DESC) AS wl |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 USING (location) |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 LEFT JOIN (SELECT DISTINCT ON (location) |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 location, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 date_issue, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 max(acc) FILTER (WHERE measure_date |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 <= current_timestamp + '1 day'::interval) |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 OVER loc_date_issue AS forecast_accuracy_1d, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 max(acc) OVER loc_date_issue AS forecast_accuracy_3d |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 FROM (SELECT location, date_issue, measure_date, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 GREATEST(water_level - lower(conf_interval), |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 upper(conf_interval) - water_level) AS acc |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 FROM waterway.gauge_predictions |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 -- consider predictions made within last 14 days ... |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 WHERE date_issue |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 >= current_timestamp - '14 days 00:15'::interval |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 -- ... for the next three days from now |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 AND measure_date BETWEEN current_timestamp |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 AND current_timestamp + '3 days'::interval) AS acc |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 WINDOW loc_date_issue AS (PARTITION BY location, date_issue) |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
59 ORDER BY location, date_issue DESC) AS fca |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 -- Show only forecasts issued with latest measurements or later |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 ON fca.location = g.location AND fca.date_issue >= wl.date_issue |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 $$); |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
64 UPDATE sys_admin.published_services SET view_def = $$ |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
65 SELECT |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 s.id, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 s.name, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 (s.section).lower::varchar as lower, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
69 (s.section).upper::varchar as upper, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
70 s.area, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 s.objnam, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 s.nobjnam, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
73 s.country, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 s.date_info, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 s.source_organization, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 s.staging_done, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
77 min(g.gm_measuredate) AS gm_measuredate, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 min(g.gm_n_14d) AS gm_n_14d, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 FROM waterway.sections s |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 LEFT JOIN ( |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
84 WHERE NOT erased) AS g |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
85 ON g.location <@ s.section |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
86 GROUP BY s.id |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
87 $$ |
b88ab93dcb2c
Expose section country in GeoServer layer
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 WHERE schema = 'waterway' AND name = 'sections_geoserver' |