# HG changeset patch
# User Bjoern Schilberg <bjoern@intevation.de>
# Date 1550580339 -3600
# Node ID ead321473840114f45d4860810f92117bf0747ac
# Parent  7cf8a276a80cb88d291931da07b7a7af9bed70bf
schema/gemma.sql: Added to gm_waterlevel bottlenecks_geoserver view.

diff -r 7cf8a276a80c -r ead321473840 schema/gemma.sql
--- a/schema/gemma.sql	Tue Feb 19 11:42:42 2019 +0100
+++ b/schema/gemma.sql	Tue Feb 19 13:45:39 2019 +0100
@@ -484,7 +484,11 @@
     WITH fairway_availability_latest AS (
         SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical
         FROM fairway_availability
-        ORDER BY bottleneck_id, date_info DESC NULLS LAST)
+        ORDER BY bottleneck_id, date_info DESC NULLS LAST),
+    gauge_measurements_waterlevel AS (
+        SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id,measure_date,predicted,water_level
+        FROM gauge_measurements WHERE predicted ='false'
+        ORDER BY fk_gauge_id, measure_date DESC NULLS LAST)
     SELECT
         b.id,
         b.bottleneck_id,
@@ -505,7 +509,8 @@
         rwl_mw.value AS mw,
         rwl_hdc.value AS hdc,
         fal.date_info AS fa_date_info,
-        fal.critical AS fa_critical
+        fal.critical AS fa_critical,
+        gmw.water_level as gm_waterlevel
     FROM bottlenecks b, gauges g,
         (SELECT gauge_id, value FROM gauges_reference_water_levels
            WHERE depth_reference = 'LDC') rwl_ldc,
@@ -517,6 +522,10 @@
             SELECT bottleneck_id,date_info,critical
             FROM  fairway_availability_latest
             WHERE b.id=bottleneck_id) fal ON TRUE
+        LEFT JOIN LATERAL (
+            SELECT water_level
+            FROM  gauge_measurements_waterlevel
+            WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
     WHERE b.fk_g_fid = g.location AND g.location = rwl_ldc.gauge_id
       AND g.location = rwl_mw.gauge_id AND g.location = rwl_hdc.gauge_id