Mercurial > gemma

changeset 3431:adf06af7a79c

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