Mercurial > gemma

changeset 2395:975e52210474

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Don't hard code the available water level ref. systems.
author Sascha Wilde <wilde@intevation.de>
date Wed, 27 Feb 2019 14:02:28 +0100
parents 85c47d63fdb9
children 6af41bb6656f
files schema/gemma.sql
diffstat 1 files changed, 7 insertions(+), 12 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Wed Feb 27 10:56:18 2019 +0100
+++ b/schema/gemma.sql	Wed Feb 27 14:02:28 2019 +0100
@@ -499,19 +499,15 @@
         b.source_organization,
         g.location AS gauge_isrs_code,
         g.objname AS gauge_objname,
-        rwl_ldc.value AS ldc,
-        rwl_mw.value AS mw,
-        rwl_hdc.value AS hdc,
+        json_object_agg(r.depth_reference, r.value) AS reference_water_levels,
         fal.date_info AS fa_date_info,
         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,
-        (SELECT gauge_id, value FROM gauges_reference_water_levels
-           WHERE depth_reference = 'MW') rwl_mw,
-        (SELECT gauge_id, value FROM gauges_reference_water_levels
-           WHERE depth_reference = 'HDC') rwl_hdc
+    FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location
+        LEFT JOIN LATERAL (
+            SELECT gauge_id,depth_reference,value
+            FROM gauges_reference_water_levels
+            ) r ON r.gauge_id = b.fk_g_fid
         LEFT JOIN LATERAL (
             SELECT bottleneck_id,date_info,critical
             FROM  fairway_availability_latest
@@ -520,8 +516,7 @@
             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
+    GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
 
     CREATE TABLE sounding_results (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,