# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1554915020 -7200
# Node ID 92818da6133d55f816b18fb8cc375103b3a991ec
# Parent  46ab2cb39471480f04f416b96e3e0922dd44140e
Cleanup VIEW definition

NULLS LAST is not necessary on non-nullable columns;
avoid materializing unused columns in a CTE;
use boolean value directly instead of comparing to auto-casted string;
gauge_measurements might contain not only waterlevels;
avoid unnecessary subselects.

diff -r 46ab2cb39471 -r 92818da6133d schema/gemma.sql
--- a/schema/gemma.sql	Wed Apr 10 17:46:50 2019 +0200
+++ b/schema/gemma.sql	Wed Apr 10 18:50:20 2019 +0200
@@ -663,15 +663,16 @@
 
     -- Published view for GeoServer
     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),
-    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)
+    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),
+    waterlevel_latest AS (
+        SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level
+            FROM gauge_measurements
+            WHERE is_waterlevel AND NOT predicted
+            ORDER BY fk_gauge_id, measure_date DESC)
     SELECT
         b.id,
         b.bottleneck_id,
@@ -693,21 +694,13 @@
             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 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
-            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
-    GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
+        wl.water_level AS gm_waterlevel
+    FROM bottlenecks b
+        LEFT JOIN gauges g ON b.fk_g_fid = g.location
+        LEFT JOIN gauges_reference_water_levels r ON g.location = r.gauge_id
+        LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id
+        LEFT JOIN waterlevel_latest wl ON b.fk_g_fid = wl.fk_gauge_id
+    GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level
 ;
 
 -- Configure primary keys for geoserver views