# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1558629386 -7200
# Node ID 6e15c3c1e9b735d2989254fcf8526ee37fe56769
# Parent  adf06af7a79c3916cf99ecda8c6852684be941b0
Add forecast accuracy within next day to gauges view

diff -r adf06af7a79c -r 6e15c3c1e9b7 schema/geoserver_views.sql
--- 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