Mercurial > gemma

changeset 2287:3154120af837

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
schema/gemma.sql: Added fairway_availability.critical and date_info to bottlenecks_geoserver view.
author Bjoern Schilberg <bjoern@intevation.de>
date Fri, 15 Feb 2019 14:08:42 +0100
parents d69bd6631362
children 3f9847c89491
files schema/gemma.sql
diffstat 1 files changed, 13 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Fri Feb 15 13:42:08 2019 +0100
+++ b/schema/gemma.sql	Fri Feb 15 14:08:42 2019 +0100
@@ -473,7 +473,12 @@
     )
 
     -- Published view for GeoServer
-    CREATE VIEW bottlenecks_geoserver AS SELECT
+    CREATE VIEW bottlenecks_geoserver AS
+    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)
+    SELECT
         b.id,
         b.bottleneck_id,
         b.objnam,
@@ -491,7 +496,9 @@
         g.objname AS gauge_objname,
         rwl_ldc.value AS ldc,
         rwl_mw.value AS mw,
-        rwl_hdc.value AS hdc
+        rwl_hdc.value AS hdc,
+        fal.date_info AS fa_date_info,
+        fal.critical AS fa_critical
     FROM bottlenecks b, gauges g,
         (SELECT gauge_id, value FROM gauges_reference_water_levels
            WHERE reference_water_level='LDC') rwl_ldc,
@@ -499,6 +506,10 @@
            WHERE reference_water_level='MW') rwl_mw,
         (SELECT gauge_id, value FROM gauges_reference_water_levels
            WHERE reference_water_level='HDC') rwl_hdc
+        LEFT JOIN LATERAL (
+            SELECT bottleneck_id,date_info,critical
+            FROM  fairway_availability_latest
+            WHERE b.id=bottleneck_id) fal 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