Mercurial > gemma

diff schema/geoserver_views.sql @ 3666:db87f34805fb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Align bottleneck validity at gauges Ensuring the validity of a bottleneck version is always contained by the validity of the referenced gauge version allows to reliably determine matching reference values of the gauge at a point in time. Since this implies that a bottleneck version might be cut into more than one time ranges, the concept of having only one non-erased version is no longer applicable and replaced by using the 'current' version of a bottleneck. Fairway availability data are always kept with the 'current' bottleneck version to have them at hand alltogether for analyses over longer time ranges.
author Tom Gottfried <tom@intevation.de>
date Sat, 15 Jun 2019 14:36:50 +0200
parents 2a079d0a71c1
children eb11ada33fa7
line wrap: on
line diff
--- a/schema/geoserver_views.sql	Sat Jun 15 09:24:28 2019 +0200
+++ b/schema/geoserver_views.sql	Sat Jun 15 14:36:50 2019 +0200
@@ -136,7 +136,7 @@
             ON b.id = fal.bottleneck_id
         LEFT JOIN sounding_result_latest srl
             ON b.bottleneck_id = srl.bottleneck_id
-    WHERE NOT b.erased;
+    WHERE b.validity @> current_timestamp;
 
 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
     SELECT
@@ -201,7 +201,7 @@
         SELECT bottleneck_id, max(date_info) AS current
             FROM waterway.sounding_results
             GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
-    WHERE NOT bn.erased
+    WHERE bn.validity @> current_timestamp
     ORDER BY objnam;
 
 CREATE OR REPLACE VIEW waterway.sounding_differences AS