# HG changeset patch
# User Markus Kottlaender <markus@intevation.de>
# Date 1558455046 -7200
# Node ID 4b8ca498c4cdb91f85f681647cf7a197fbc23c79
# Parent  bda4034304b104673cca11349a543a896cc7803a# Parent  3e7a670896bd157cb94419c91403931eb4c4ba6f
mergen latest-waterlevel-for-gauges in default

diff -r bda4034304b1 -r 4b8ca498c4cd schema/geoserver_views.sql
--- a/schema/geoserver_views.sql	Tue May 21 17:47:25 2019 +0200
+++ b/schema/geoserver_views.sql	Tue May 21 18:10:46 2019 +0200
@@ -1,4 +1,9 @@
 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
+    WITH
+    waterlevel_latest AS (
+        SELECT DISTINCT ON (location) location, water_level
+            FROM waterway.gauge_measurements
+            ORDER BY location, measure_date DESC)
     SELECT
         g.location,
         isrs_asText(g.location) AS isrs_code,
@@ -13,12 +18,15 @@
         g.source_organization,
         json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
                                                   r.value))
-            AS reference_water_levels
+            AS reference_water_levels,
+        wl.water_level AS gm_waterlevel
     FROM waterway.gauges g
         LEFT JOIN waterway.gauges_reference_water_levels r
             USING (location, validity)
+        LEFT JOIN waterlevel_latest wl
+            USING (location)
     WHERE NOT g.erased
-    GROUP BY g.location, g.validity;
+    GROUP BY g.location, g.validity, wl.water_level;
 
 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS
     SELECT location_code,