Mercurial > gemma

changeset 3456:ca395be62023

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add reference gauge forecast accuracy to bottlenecks view Instead of duplicating more sub-queries and CTEs, join the gauges view, which already has all calculated attributes.
author Tom Gottfried <tom@intevation.de>
date Fri, 24 May 2019 14:24:24 +0200
parents 188b55ef814f
children 870812d8f247
files schema/geoserver_views.sql
diffstat 1 files changed, 7 insertions(+), 18 deletions(-) [+]
line wrap: on
line diff
--- a/schema/geoserver_views.sql	Fri May 24 13:46:56 2019 +0200
+++ b/schema/geoserver_views.sql	Fri May 24 14:24:24 2019 +0200
@@ -64,11 +64,7 @@
     fairway_availability_latest AS (
         SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
             FROM waterway.fairway_availability
-            ORDER BY bottleneck_id, date_info DESC),
-    waterlevel_latest AS (
-        SELECT DISTINCT ON (location) location, water_level
-            FROM waterway.gauge_measurements
-            ORDER BY location, measure_date DESC)
+            ORDER BY bottleneck_id, date_info DESC)
     SELECT
         b.id,
         b.bottleneck_id,
@@ -85,24 +81,17 @@
         b.source_organization,
         g.location AS gauge_isrs_code,
         g.objname AS gauge_objname,
-        json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
-                                                  r.value))
-            AS reference_water_levels,
+        g.reference_water_levels,
         fal.date_info AS fa_date_info,
         fal.critical AS fa_critical,
-        wl.water_level AS gm_waterlevel
+        g.gm_waterlevel,
+        g.forecast_accuracy_3d,
+        g.forecast_accuracy_1d
     FROM waterway.bottlenecks b
-        LEFT JOIN waterway.gauges g
+        LEFT JOIN waterway.gauges_geoserver g
             ON b.gauge_location = g.location AND b.gauge_validity = g.validity
-        LEFT JOIN waterway.gauges_reference_water_levels r
-            USING (location, validity)
         LEFT JOIN fairway_availability_latest fal
-            ON b.id = fal.bottleneck_id
-        LEFT JOIN waterlevel_latest wl
-            USING (location)
-    WHERE NOT g.erased
-    GROUP BY b.id, g.location, g.validity,
-        fal.date_info, fal.critical, wl.water_level;
+            ON b.id = fal.bottleneck_id;
 
 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
     SELECT