# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1570545302 -7200
# Node ID 0b01fd83bc3bb639ce4d67909c347799d4d15473
# Parent  51dc19001808a59ee7a99c07a98f41f32c244bb0
Added views for sounding differences.

diff -r 51dc19001808 -r 0b01fd83bc3b schema/default_sysconfig.sql
--- a/schema/default_sysconfig.sql	Tue Oct 08 16:13:53 2019 +0200
+++ b/schema/default_sysconfig.sql	Tue Oct 08 16:35:02 2019 +0200
@@ -34,7 +34,8 @@
     ('waterway.bottleneck_overview'),
     ('waterway.waterway_axis'),
     ('waterway.waterway_area'),
-    ('waterway.waterway_profiles');
+    ('waterway.waterway_profiles'),
+    ('waterway.sounding_differences');
 
 --
 -- Settings
diff -r 51dc19001808 -r 0b01fd83bc3b schema/geoserver_views.sql
--- 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;
diff -r 51dc19001808 -r 0b01fd83bc3b schema/updates/1204/03.geoserver-views.sql
--- a/schema/updates/1204/03.geoserver-views.sql	Tue Oct 08 16:13:53 2019 +0200
+++ b/schema/updates/1204/03.geoserver-views.sql	Tue Oct 08 16:35:02 2019 +0200
@@ -6,4 +6,23 @@
   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;
+
 GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;
diff -r 51dc19001808 -r 0b01fd83bc3b schema/updates/1204/04.publish.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1204/04.publish.sql	Tue Oct 08 16:35:02 2019 +0200
@@ -0,0 +1,6 @@
+INSERT INTO sys_admin.published_services (name)
+   VALUES ('waterway.sounding_results_areas_geoserver'::regclass)
+   ON CONFLICT (name) DO NOTHING;
+INSERT INTO sys_admin.published_services (name)
+   VALUES ('waterway.sounding_differences'::regclass)
+   ON CONFLICT (name) DO NOTHING;