Mercurial > gemma

changeset 2327:ead321473840

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
schema/gemma.sql: Added to gm_waterlevel bottlenecks_geoserver view.
author Bjoern Schilberg <bjoern@intevation.de>
date Tue, 19 Feb 2019 13:45:39 +0100
parents 7cf8a276a80c
children c98491f9c466
files schema/gemma.sql
diffstat 1 files changed, 11 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- 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