Mercurial > gemma

comparison schema/default_sysconfig.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 4476ec4db818
children 30bb2d819d57
comparison
equal deleted inserted replaced
4619:4476ec4db818 4620:f77a6f9216ae
53 FROM waterway.gauges_reference_water_levels 53 FROM waterway.gauges_reference_water_levels
54 GROUP BY location, validity) AS r 54 GROUP BY location, validity) AS r
55 USING (location, validity) 55 USING (location, validity)
56 LEFT JOIN (SELECT DISTINCT ON (location) 56 LEFT JOIN (SELECT DISTINCT ON (location)
57 location, 57 location,
58 date_issue,
58 measure_date, 59 measure_date,
59 water_level, 60 water_level,
60 count(*) OVER (PARTITION BY location) AS n 61 count(*) OVER (PARTITION BY location) AS n
61 FROM waterway.gauge_measurements 62 FROM waterway.gauge_measurements
62 -- consider all measurements within 14 days plus a tolerance 63 -- consider all measurements within 14 days plus a tolerance
64 >= current_timestamp - '14 days 00:15'::interval 65 >= current_timestamp - '14 days 00:15'::interval
65 ORDER BY location, measure_date DESC) AS wl 66 ORDER BY location, measure_date DESC) AS wl
66 USING (location) 67 USING (location)
67 LEFT JOIN (SELECT DISTINCT ON (location) 68 LEFT JOIN (SELECT DISTINCT ON (location)
68 location, 69 location,
70 date_issue,
69 max(acc) FILTER (WHERE measure_date 71 max(acc) FILTER (WHERE measure_date
70 <= current_timestamp + '1 day'::interval) 72 <= current_timestamp + '1 day'::interval)
71 OVER loc_date_issue AS forecast_accuracy_1d, 73 OVER loc_date_issue AS forecast_accuracy_1d,
72 max(acc) OVER loc_date_issue AS forecast_accuracy_3d 74 max(acc) OVER loc_date_issue AS forecast_accuracy_3d
73 FROM (SELECT location, date_issue, measure_date, 75 FROM (SELECT location, date_issue, measure_date,
80 -- ... for the next three days from now 82 -- ... for the next three days from now
81 AND measure_date BETWEEN current_timestamp 83 AND measure_date BETWEEN current_timestamp
82 AND current_timestamp + '3 days'::interval) AS acc 84 AND current_timestamp + '3 days'::interval) AS acc
83 WINDOW loc_date_issue AS (PARTITION BY location, date_issue) 85 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
84 ORDER BY location, date_issue DESC) AS fca 86 ORDER BY location, date_issue DESC) AS fca
85 USING (location) 87 -- Show only forecasts issued with latest measurements or later
88 ON fca.location = g.location AND fca.date_issue >= wl.date_issue
86 $$); 89 $$);
87 90
88 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES 91 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
89 -- Directly accessed tables 92 -- Directly accessed tables
90 ('waterway', 'waterway_axis', NULL, NULL), 93 ('waterway', 'waterway_axis', NULL, NULL),