Mercurial > gemma

changeset 3432:6e15c3c1e9b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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