Mercurial > gemma

comparison schema/updates/1300/02.views_to_geoservers.sql @ 4620:f77a6f9216ae geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge default into geoserver_sql_views
author Tom Gottfried <tom@intevation.de>
date Fri, 20 Sep 2019 17:48:47 +0200
parents 0f2c3cb139cc
children b03aa1502736
comparison
equal deleted inserted replaced
4619:4476ec4db818 4620:f77a6f9216ae
29 FROM waterway.gauges_reference_water_levels 29 FROM waterway.gauges_reference_water_levels
30 GROUP BY location, validity) AS r 30 GROUP BY location, validity) AS r
31 USING (location, validity) 31 USING (location, validity)
32 LEFT JOIN (SELECT DISTINCT ON (location) 32 LEFT JOIN (SELECT DISTINCT ON (location)
33 location, 33 location,
34 date_issue,
34 measure_date, 35 measure_date,
35 water_level, 36 water_level,
36 count(*) OVER (PARTITION BY location) AS n 37 count(*) OVER (PARTITION BY location) AS n
37 FROM waterway.gauge_measurements 38 FROM waterway.gauge_measurements
38 -- consider all measurements within 14 days plus a tolerance 39 -- consider all measurements within 14 days plus a tolerance
40 >= current_timestamp - '14 days 00:15'::interval 41 >= current_timestamp - '14 days 00:15'::interval
41 ORDER BY location, measure_date DESC) AS wl 42 ORDER BY location, measure_date DESC) AS wl
42 USING (location) 43 USING (location)
43 LEFT JOIN (SELECT DISTINCT ON (location) 44 LEFT JOIN (SELECT DISTINCT ON (location)
44 location, 45 location,
46 date_issue,
45 max(acc) FILTER (WHERE measure_date 47 max(acc) FILTER (WHERE measure_date
46 <= current_timestamp + '1 day'::interval) 48 <= current_timestamp + '1 day'::interval)
47 OVER loc_date_issue AS forecast_accuracy_1d, 49 OVER loc_date_issue AS forecast_accuracy_1d,
48 max(acc) OVER loc_date_issue AS forecast_accuracy_3d 50 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
49 FROM (SELECT location, date_issue, measure_date, 51 FROM (SELECT location, date_issue, measure_date,
56 -- ... for the next three days from now 58 -- ... for the next three days from now
57 AND measure_date BETWEEN current_timestamp 59 AND measure_date BETWEEN current_timestamp
58 AND current_timestamp + '3 days'::interval) AS acc 60 AND current_timestamp + '3 days'::interval) AS acc
59 WINDOW loc_date_issue AS (PARTITION BY location, date_issue) 61 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
60 ORDER BY location, date_issue DESC) AS fca 62 ORDER BY location, date_issue DESC) AS fca
61 USING (location) 63 -- Show only forecasts issued with latest measurements or later
64 ON fca.location = g.location AND fca.date_issue >= wl.date_issue
62 $$); 65 $$);
63 66
64 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES 67 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
65 ('waterway', 'gauges_geoserver', 4326, $$ 68 ('waterway', 'gauges_geoserver', 4326, $$
66 SELECT 69 SELECT