Mercurial > gemma

changeset 4358:a7196b55c064

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Remove VIEW columns ignored by GeoServer Removed columns were ignored with a warning on layer creation.
author Tom Gottfried <tom@intevation.de>
date Mon, 09 Sep 2019 16:13:26 +0200
parents e8af2ed8666e
children 057787583b12
files schema/gemma.sql schema/geoserver_views.sql schema/updates/1112/01.cleanup_views.sql
diffstat 3 files changed, 137 insertions(+), 9 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Mon Sep 09 16:12:43 2019 +0200
+++ b/schema/gemma.sql	Mon Sep 09 16:13:26 2019 +0200
@@ -777,8 +777,8 @@
 
 -- Configure primary keys for geoserver views
 INSERT INTO waterway.gt_pk_metadata VALUES
-  ('waterway', 'gauges_geoserver', 'location'),
-  ('waterway', 'distance_marks_geoserver', 'location_code'),
+  ('waterway', 'gauges_geoserver', 'isrs_code'),
+  ('waterway', 'distance_marks_geoserver', 'location'),
   ('waterway', 'distance_marks_ashore_geoserver', 'id'),
   ('waterway', 'bottlenecks_geoserver', 'id'),
   ('waterway', 'stretches_geoserver', 'id'),
--- a/schema/geoserver_views.sql	Mon Sep 09 16:12:43 2019 +0200
+++ b/schema/geoserver_views.sql	Mon Sep 09 16:13:26 2019 +0200
@@ -55,13 +55,11 @@
 
 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
     SELECT
-        location,
         isrs_code,
         objname,
         geom,
         applicability_from_km,
         applicability_to_km,
-        validity,
         zero_point,
         geodref,
         date_info,
@@ -76,7 +74,7 @@
     WHERE NOT erased;
 
 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS
-    SELECT location_code,
+    SELECT
        isrs_asText(location_code) AS location,
        geom::Geometry(POINT, 4326),
        related_enc,
@@ -99,7 +97,6 @@
         b.bottleneck_id,
         b.objnam,
         b.nobjnm,
-        b.stretch,
         b.area,
         b.rb,
         b.lb,
@@ -108,7 +105,6 @@
         b.limiting,
         b.date_info,
         b.source_organization,
-        g.location AS gauge_isrs_code,
         g.objname AS gauge_objname,
         g.reference_water_levels,
         fal.date_info AS fa_date_info,
@@ -155,7 +151,8 @@
         max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
         max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
     FROM waterway.stretches s
-        LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.stretch
+        LEFT JOIN waterway.gauges_geoserver g
+            ON isrs_fromtext(g.isrs_code) <@ s.stretch
     GROUP BY s.id;
 
 CREATE OR REPLACE VIEW waterway.sections_geoserver AS
@@ -175,7 +172,8 @@
         max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
         max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
     FROM waterway.sections s
-        LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.section
+        LEFT JOIN waterway.gauges_geoserver g
+            ON isrs_fromtext(g.isrs_code) <@ s.section
     GROUP BY s.id;
 
 CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1112/01.cleanup_views.sql	Mon Sep 09 16:13:26 2019 +0200
@@ -0,0 +1,130 @@
+DROP VIEW waterway.gauges_geoserver CASCADE;
+CREATE VIEW waterway.gauges_geoserver AS
+    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 waterway.gauges_base_view
+    WHERE NOT erased;
+
+DROP VIEW waterway.distance_marks_geoserver;
+CREATE VIEW waterway.distance_marks_geoserver AS
+    SELECT
+       isrs_asText(location_code) AS location,
+       geom::Geometry(POINT, 4326),
+       related_enc,
+       (location_code).hectometre
+    FROM waterway.distance_marks_virtual;
+
+DROP VIEW waterway.bottlenecks_geoserver;
+CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
+    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 waterway.gauges_base_view 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;
+
+CREATE VIEW waterway.stretches_geoserver AS
+    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 waterway.gauges_geoserver g
+            ON isrs_fromtext(g.isrs_code) <@ s.stretch
+    GROUP BY s.id;
+
+CREATE VIEW waterway.sections_geoserver AS
+    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 waterway.gauges_geoserver g
+            ON isrs_fromtext(g.isrs_code) <@ s.section
+    GROUP BY s.id;
+
+UPDATE waterway.gt_pk_metadata SET pk_column = 'isrs_code'
+    WHERE table_schema = 'waterway'
+        AND table_name = 'gauges_geoserver';
+UPDATE waterway.gt_pk_metadata SET pk_column = 'location'
+    WHERE table_schema = 'waterway'
+        AND table_name = 'distance_marks_geoserver';
+
+GRANT SELECT ON
+        waterway.gauges_geoserver,
+        waterway.distance_marks_geoserver,
+        waterway.bottlenecks_geoserver,
+        waterway.stretches_geoserver,
+        waterway.sections_geoserver
+    TO waterway_user;