--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1113/02.views_to_geoservers.sql	Tue Sep 10 19:56:43 2019 +0200
@@ -0,0 +1,251 @@
+-- Copied from ../../default_sysconfig.sql
+-- plus conflict resolution to achieve updates
+
+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_14d.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,
+                    measure_date,
+                    water_level
+                FROM waterway.gauge_measurements
+                ORDER BY location, measure_date DESC) AS wl
+            USING (location)
+        LEFT JOIN (SELECT location, count(water_level) AS n
+                FROM waterway.gauge_measurements
+                -- consider all measurements within 14 days plus a tolerance
+                WHERE measure_date
+                    >= current_timestamp - '14 days 00:15'::interval
+                GROUP BY location) AS wl_14d
+            USING (location)
+        LEFT JOIN (SELECT location,
+                    max(acc) FILTER (WHERE
+                        measure_date <= current_timestamp + '1 day'::interval)
+                        AS forecast_accuracy_1d,
+                    max(acc) AS forecast_accuracy_3d
+                FROM waterway.gauge_predictions,
+                    GREATEST(water_level - lower(conf_interval),
+                        upper(conf_interval) - water_level) AS acc (acc)
+                WHERE measure_date
+                    BETWEEN current_timestamp
+                        AND current_timestamp + '3 days'::interval
+                GROUP BY location) AS fca
+            USING (location)
+    $$);
+
+INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
+    ('waterway', 'gauges_geoserver', 4326, $$
+        SELECT
+            isrs_code,
+            objname,
+            geom,
+            applicability_from_km,
+            applicability_to_km,
+            zero_point,
+            geodref,
+            date_info,
+            source_organization,
+            reference_water_levels,
+            gm_measuredate,
+            gm_waterlevel,
+            gm_n_14d,
+            forecast_accuracy_3d,
+            forecast_accuracy_1d
+        FROM (
+    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
+        ) AS gauges_base_view
+        WHERE NOT erased
+    $$),
+    ('waterway', 'sections_geoserver', 4326, $$
+        SELECT
+            s.id,
+            s.name,
+            (s.section).lower::varchar as lower,
+            (s.section).upper::varchar as upper,
+            s.area::Geometry(MULTIPOLYGON, 4326),
+            s.objnam,
+            s.nobjnam,
+            s.date_info,
+            s.source_organization,
+            s.staging_done,
+            min(g.gm_measuredate) AS gm_measuredate,
+            min(g.gm_n_14d) AS gm_n_14d,
+            max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
+            max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
+        FROM waterway.sections s
+            LEFT JOIN (
+    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
+            WHERE NOT erased) AS g
+                ON g.location <@ s.section
+        GROUP BY s.id
+    $$),
+    ('waterway', 'stretches_geoserver', 4326, $$
+        SELECT
+            s.id,
+            s.name,
+            (s.stretch).lower::varchar as lower,
+            (s.stretch).upper::varchar as upper,
+            s.area::Geometry(MULTIPOLYGON, 4326),
+            s.objnam,
+            s.nobjnam,
+            s.date_info,
+            s.source_organization,
+            (SELECT string_agg(country_code, ', ')
+                FROM waterway.stretch_countries
+                WHERE stretches_id = s.id) AS countries,
+            s.staging_done,
+            min(g.gm_measuredate) AS gm_measuredate,
+            min(g.gm_n_14d) AS gm_n_14d,
+            max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
+            max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
+        FROM waterway.stretches s
+            LEFT JOIN (
+    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
+            WHERE NOT erased) AS g
+                ON g.location <@ s.stretch
+        GROUP BY s.id
+    $$),
+    ('waterway', 'distance_marks_ashore_geoserver', 4326, $$
+        SELECT id,
+           country,
+           geom::Geometry(POINT, 4326),
+           related_enc,
+           hectom,
+           catdis,
+           position_code
+        FROM waterway.distance_marks
+    $$),
+    ('waterway', 'distance_marks_geoserver', 4326, $$
+        SELECT
+           isrs_asText(location_code) AS location,
+           geom::Geometry(POINT, 4326),
+           related_enc,
+           (location_code).hectometre
+        FROM waterway.distance_marks_virtual
+    $$),
+    ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$
+        SELECT bottleneck_id,
+            date_info,
+            height,
+            CAST(lines AS geometry(multilinestring, 4326)) AS lines
+        FROM waterway.sounding_results_contour_lines cl
+            JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id
+    $$),
+    ('waterway', 'bottlenecks_geoserver', 4326, $$
+        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, $$
+        SELECT
+            objnam AS name,
+            ST_Centroid(area)::Geometry(POINT, 4326) AS point,
+            (lower(stretch)).hectometre AS from,
+            (upper(stretch)).hectometre AS to,
+            sr.current::text,
+            responsible_country
+        FROM waterway.bottlenecks bn LEFT JOIN (
+            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 bn.validity @> current_timestamp
+        ORDER BY objnam
+    $$),
+    ('waterway', 'sounding_differences', 4326, $$
+        SELECT
+            sd.id           AS id,
+            bn.objnam       AS objnam,
+            srm.date_info   AS minuend,
+            srs.date_info   AS subtrahend,
+            sdcl.height     AS height,
+            CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
+        FROM caching.sounding_differences sd
+            JOIN caching.sounding_differences_contour_lines sdcl
+                ON sd.id = sdcl.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
+    $$)
+ON CONFLICT (schema, name) DO UPDATE SET
+    srid = EXCLUDED.srid,
+    view_def = EXCLUDED.view_def;
+
+DROP VIEW
+    waterway.gauges_base_view,
+    waterway.gauges_geoserver,
+    waterway.distance_marks_geoserver,
+    waterway.distance_marks_ashore_geoserver,
+    waterway.bottlenecks_geoserver,
+    waterway.stretches_geoserver,
+    waterway.sections_geoserver,
+    waterway.sounding_results_contour_lines_geoserver,
+    waterway.bottleneck_overview,
+    waterway.sounding_differences;