Mercurial > gemma
changeset 4358:a7196b55c064
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;