Mercurial > gemma

diff schema/geoserver_views.sql @ 4583:0b01fd83bc3b iso-areas

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added views for sounding differences.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 08 Oct 2019 16:35:02 +0200
parents 51dc19001808
children
line wrap: on
line diff
--- a/schema/geoserver_views.sql	Tue Oct 08 16:13:53 2019 +0200
+++ b/schema/geoserver_views.sql	Tue Oct 08 16:35:02 2019 +0200
@@ -205,3 +205,22 @@
     CAST(areas AS geometry(multipolygon, 4326)) as areas
   FROM waterway.sounding_results_iso_areas ia
   JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id;
+
+CREATE OR REPLACE VIEW waterway.sounding_differences AS
+    SELECT
+        sd.id           AS id,
+        bn.objnam       AS objnam,
+        srm.date_info   AS minuend,
+        srs.date_info   AS subtrahend,
+        sdia.height     AS height,
+        CAST(sdia.areas AS geometry(multipolygon, 4326)) AS areas
+    FROM caching.sounding_differences sd
+        JOIN caching.sounding_differences_iso_areas sdia
+            ON sd.id = sdia.sounding_differences_id
+        JOIN waterway.sounding_results srm
+            ON sd.minuend = srm.id
+        JOIN waterway.sounding_results srs
+            ON sd.subtrahend = srs.id
+        JOIN waterway.bottlenecks bn
+            ON srm.bottleneck_id = bn.bottleneck_id
+                AND srm.date_info::timestamptz <@ bn.validity;