Mercurial > gemma

annotate schema/updates/1309/01.expose_section_country.sql @ 4742:b88ab93dcb2c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Expose section country in GeoServer layer
author Tom Gottfried <tom@intevation.de>
date Fri, 18 Oct 2019 12:18:23 +0200
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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'