Mercurial > gemma

changeset 3469:096968d5628f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added latest sounding result date to bottlenecks_geoserver view.
author Sascha Wilde <wilde@intevation.de>
date Mon, 27 May 2019 11:22:12 +0200
parents 850e81f13e96
children 104c4f683c74
files schema/geoserver_views.sql
diffstat 1 files changed, 10 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/schema/geoserver_views.sql	Mon May 27 11:21:11 2019 +0200
+++ b/schema/geoserver_views.sql	Mon May 27 11:22:12 2019 +0200
@@ -64,7 +64,12 @@
     fairway_availability_latest AS (
         SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
             FROM waterway.fairway_availability
-            ORDER BY bottleneck_id, date_info DESC)
+            ORDER BY bottleneck_id, date_info DESC),
+    sounding_result_latest AS (
+        SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max
+            FROM waterway.sounding_results
+            GROUP BY bottleneck_id
+            ORDER BY bottleneck_id DESC)
     SELECT
         b.id,
         b.bottleneck_id,
@@ -85,13 +90,16 @@
         fal.date_info AS fa_date_info,
         fal.critical AS fa_critical,
         g.gm_waterlevel,
+        srl.date_max,
         g.forecast_accuracy_3d,
         g.forecast_accuracy_1d
     FROM waterway.bottlenecks b
         LEFT JOIN waterway.gauges_geoserver g
             ON b.gauge_location = g.location AND b.gauge_validity = g.validity
         LEFT JOIN fairway_availability_latest fal
-            ON b.id = fal.bottleneck_id;
+            ON b.id = fal.bottleneck_id
+        LEFT JOIN sounding_result_latest srl
+            ON b.id = srl.bottleneck_id;
 
 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
     SELECT