Mercurial > gemma
changeset 3431:adf06af7a79c
Add forecast accuracy to gauges view
Forecast accuracy is calculated as the maximum deviation of confidence
interval bounds from the prediction within the next four days.
The JSON-aggregate of reference water levels is moved into a
sub-select to avoid having to add further independent columns to the
GROUP BY clause of the main statement.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 23 May 2019 17:29:34 +0200 |
parents | 6994602d2935 |
children | 6e15c3c1e9b7 |
files | schema/geoserver_views.sql |
diffstat | 1 files changed, 18 insertions(+), 7 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Thu May 23 17:28:14 2019 +0200 +++ b/schema/geoserver_views.sql Thu May 23 17:29:34 2019 +0200 @@ -16,17 +16,28 @@ g.geodref, g.date_info, g.source_organization, - json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), - r.value)) - AS reference_water_levels, - wl.water_level AS gm_waterlevel + r.rwls AS reference_water_levels, + wl.water_level AS gm_waterlevel, + fca.forecast_accuracy FROM waterway.gauges g - LEFT JOIN waterway.gauges_reference_water_levels r + LEFT JOIN (SELECT location, validity, + json_strip_nulls(json_object_agg( + coalesce(depth_reference, 'empty'), value)) AS rwls + FROM waterway.gauges_reference_water_levels + GROUP BY location, validity) AS r USING (location, validity) LEFT JOIN waterlevel_latest wl USING (location) - WHERE NOT g.erased - GROUP BY g.location, g.validity, wl.water_level; + LEFT JOIN (SELECT location, max(GREATEST( + water_level - lower(conf_interval), + upper(conf_interval) - water_level)) AS forecast_accuracy + FROM waterway.gauge_predictions + WHERE measure_date + BETWEEN current_timestamp + AND current_timestamp + '4 days'::interval + GROUP BY location) AS fca + USING (location) + WHERE NOT g.erased; CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS SELECT location_code,