changeset 5098:52aac557cbd7 queued-stage-done

Merged default intp 'queued-stage-done' branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 24 Mar 2020 13:07:24 +0100
parents 6d5f711dff61 (current diff) 8dc27fc1d05c (diff)
children 3cd736acbad3
files schema/updates/1431/01.bottlenecks_geoserver_add_time.sql
diffstat 2 files changed, 153 insertions(+), 43 deletions(-) [+]
line wrap: on
line diff
--- a/schema/default_sysconfig.sql	Tue Mar 24 13:03:38 2020 +0100
+++ b/schema/default_sysconfig.sql	Tue Mar 24 13:07:24 2020 +0100
@@ -218,49 +218,6 @@
         FROM waterway.sounding_results_iso_areas ia
             JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id
     $$),
-    ('waterway', 'bottlenecks_geoserver', 4326, 'id', $$
-        SELECT
-            b.id,
-            b.bottleneck_id,
-            b.objnam,
-            b.nobjnm,
-            b.area,
-            b.rb,
-            b.lb,
-            b.responsible_country,
-            b.revisiting_time,
-            b.limiting,
-            b.date_info,
-            b.source_organization,
-            g.objname AS gauge_objname,
-            g.reference_water_levels,
-            fal.date_info AS fa_date_info,
-            fal.critical AS fa_critical,
-            g.gm_measuredate,
-            g.gm_waterlevel,
-            g.gm_n_14d,
-            srl.date_max,
-            g.forecast_accuracy_3d,
-            g.forecast_accuracy_1d
-        FROM waterway.bottlenecks b
-            LEFT JOIN (
-    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
-            ) AS g
-                ON b.gauge_location = g.location
-                    AND g.validity @> current_timestamp
-            LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
-                        bottleneck_id, date_info, critical
-                    FROM waterway.fairway_availability
-                    ORDER BY bottleneck_id, date_info DESC) AS fal
-                ON b.bottleneck_id = fal.bottleneck_id
-            LEFT JOIN (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) AS srl
-                ON b.bottleneck_id = srl.bottleneck_id
-        WHERE b.validity @> current_timestamp
-    $$),
     ('waterway', 'bottleneck_overview', 4326, NULL, $$
         SELECT
             objnam AS name,
@@ -304,6 +261,51 @@
     wmst_attribute, wmst_end_attribute,
     view_def
 ) VALUES
+    ('waterway', 'bottlenecks_geoserver', 4326, 'id',
+        'valid_from', 'valid_to', $$
+        SELECT
+            b.id,
+            lower(b.validity) AS valid_from,
+            COALESCE(upper(b.validity), current_timestamp) AS valid_to,
+            b.bottleneck_id,
+            b.objnam,
+            b.nobjnm,
+            b.area,
+            b.rb,
+            b.lb,
+            b.responsible_country,
+            b.revisiting_time,
+            b.limiting,
+            b.date_info,
+            b.source_organization,
+            g.objname AS gauge_objname,
+            g.reference_water_levels,
+            fal.date_info AS fa_date_info,
+            fal.critical AS fa_critical,
+            g.gm_measuredate,
+            g.gm_waterlevel,
+            g.gm_n_14d,
+            srl.date_max,
+            g.forecast_accuracy_3d,
+            g.forecast_accuracy_1d
+        FROM waterway.bottlenecks b
+            LEFT JOIN (
+    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
+            ) AS g
+                ON b.gauge_location = g.location
+                    AND g.validity @> current_timestamp
+            LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
+                        bottleneck_id, date_info, critical
+                    FROM waterway.fairway_availability
+                    ORDER BY bottleneck_id, date_info DESC) AS fal
+                ON b.bottleneck_id = fal.bottleneck_id
+            LEFT JOIN (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) AS srl
+                ON b.bottleneck_id = srl.bottleneck_id
+    $$),
     ('waterway', 'waterway_axis', 4326, 'id',
         'valid_from', 'valid_to', $$
         SELECT id,
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1431/01.bottlenecks_geoserver_add_time.sql	Tue Mar 24 13:07:24 2020 +0100
@@ -0,0 +1,108 @@
+CREATE TEMP TABLE base_views (name, def) AS VALUES (
+    'gauges_base_view', $$
+    SELECT
+        g.location,
+        isrs_asText(g.location) AS isrs_code,
+        g.objname,
+        g.geom,
+        g.applicability_from_km,
+        g.applicability_to_km,
+        g.validity,
+        g.zero_point,
+        g.geodref,
+        g.date_info,
+        g.source_organization,
+        g.erased,
+        r.rwls AS reference_water_levels,
+        wl.measure_date AS gm_measuredate,
+        wl.water_level AS gm_waterlevel,
+        wl.n AS gm_n_14d,
+        fca.forecast_accuracy_3d,
+        fca.forecast_accuracy_1d
+    FROM waterway.gauges g
+        LEFT JOIN (SELECT location, validity,
+                    json_strip_nulls(json_object_agg(
+                        coalesce(depth_reference, 'empty'), value)) AS rwls
+                FROM waterway.gauges_reference_water_levels
+                GROUP BY location, validity) AS r
+            USING (location, validity)
+        LEFT JOIN (SELECT DISTINCT ON (location)
+                    location,
+                    date_issue,
+                    measure_date,
+                    water_level,
+                    count(*) OVER (PARTITION BY location) AS n
+                FROM waterway.gauge_measurements
+                WHERE measure_date
+                    >= current_timestamp - '14 days 00:15'::interval
+                ORDER BY location, measure_date DESC) AS wl
+            USING (location)
+        LEFT JOIN (SELECT DISTINCT ON (location)
+                    location,
+                    date_issue,
+                    max(acc) FILTER (WHERE measure_date
+                            <= current_timestamp + '1 day'::interval)
+                        OVER loc_date_issue AS forecast_accuracy_1d,
+                    max(acc) OVER loc_date_issue AS forecast_accuracy_3d
+                FROM (SELECT location, date_issue, measure_date,
+                        GREATEST(water_level - lower(conf_interval),
+                            upper(conf_interval) - water_level) AS acc
+                    FROM waterway.gauge_predictions
+                    WHERE date_issue
+                        >= current_timestamp - '14 days 00:15'::interval
+                        AND measure_date BETWEEN current_timestamp
+                            AND current_timestamp + '3 days'::interval) AS acc
+                WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
+                ORDER BY location, date_issue DESC) AS fca
+            ON fca.location = g.location AND fca.date_issue >= wl.date_issue
+    $$);
+
+UPDATE sys_admin.published_services
+    SET
+        wmst_attribute = 'valid_from',
+        wmst_end_attribute = 'valid_to',
+        view_def = $$
+        SELECT
+            b.id,
+            lower(b.validity) AS valid_from,
+            COALESCE(upper(b.validity), current_timestamp) AS valid_to,
+            b.bottleneck_id,
+            b.objnam,
+            b.nobjnm,
+            b.area,
+            b.rb,
+            b.lb,
+            b.responsible_country,
+            b.revisiting_time,
+            b.limiting,
+            b.date_info,
+            b.source_organization,
+            g.objname AS gauge_objname,
+            g.reference_water_levels,
+            fal.date_info AS fa_date_info,
+            fal.critical AS fa_critical,
+            g.gm_measuredate,
+            g.gm_waterlevel,
+            g.gm_n_14d,
+            srl.date_max,
+            g.forecast_accuracy_3d,
+            g.forecast_accuracy_1d
+        FROM waterway.bottlenecks b
+            LEFT JOIN (
+    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
+            ) AS g
+                ON b.gauge_location = g.location
+                    AND g.validity @> current_timestamp
+            LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
+                        bottleneck_id, date_info, critical
+                    FROM waterway.fairway_availability
+                    ORDER BY bottleneck_id, date_info DESC) AS fal
+                ON b.bottleneck_id = fal.bottleneck_id
+            LEFT JOIN (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) AS srl
+                ON b.bottleneck_id = srl.bottleneck_id
+            $$
+    WHERE schema = 'waterway' AND name = 'bottlenecks_geoserver';