Mercurial > gemma
changeset 3432:6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 23 May 2019 18:36:26 +0200 |
parents | adf06af7a79c |
children | e56b280582ae |
files | schema/geoserver_views.sql |
diffstat | 1 files changed, 10 insertions(+), 5 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Thu May 23 17:29:34 2019 +0200 +++ b/schema/geoserver_views.sql Thu May 23 18:36:26 2019 +0200 @@ -18,7 +18,8 @@ g.source_organization, r.rwls AS reference_water_levels, wl.water_level AS gm_waterlevel, - fca.forecast_accuracy + fca.forecast_accuracy, + fca.forecast_accuracy_1d FROM waterway.gauges g LEFT JOIN (SELECT location, validity, json_strip_nulls(json_object_agg( @@ -28,10 +29,14 @@ USING (location, validity) LEFT JOIN waterlevel_latest wl USING (location) - LEFT JOIN (SELECT location, max(GREATEST( - water_level - lower(conf_interval), - upper(conf_interval) - water_level)) AS forecast_accuracy - FROM waterway.gauge_predictions + LEFT JOIN (SELECT location, + max(acc) FILTER (WHERE + measure_date <= current_timestamp + '1 day'::interval) + AS forecast_accuracy_1d, + max(acc) AS forecast_accuracy + FROM waterway.gauge_predictions, + GREATEST(water_level - lower(conf_interval), + upper(conf_interval) - water_level) AS acc (acc) WHERE measure_date BETWEEN current_timestamp AND current_timestamp + '4 days'::interval