Mercurial > gemma
changeset 4616:b605e91f08f0 geoserver_sql_views
Use GeoServer's SQL view feature instead of in-database VIEWs
VIEWs are executed with the rigths of their owner, but we need to
access the data with the rights of the current_user in order to
have row level security policies applied correctly. Moving the
former VIEW definitions into GeoServer SQL views achieves this, as
the latter are executed as subqueries in usual statements.
Note that a fresh GeoServer setup is required to make this work,
which can be achieved e.g. by starting gemma with the
geoserver-clean flag. Otherwise, GeoServer will keep trying to
access the dropped VIEWs.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Tue, 10 Sep 2019 19:56:43 +0200 |
| parents | 32d3e0cecf4f |
| children | ae840f9eb4c8 |
| files | schema/default_sysconfig.sql schema/geoserver_views.sql schema/install-db.sh schema/updates/1113/02.views_to_geoservers.sql |
| diffstat | 4 files changed, 492 insertions(+), 234 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/default_sysconfig.sql Mon Sep 09 17:46:51 2019 +0200 +++ b/schema/default_sysconfig.sql Tue Sep 10 19:56:43 2019 +0200 @@ -23,20 +23,247 @@ -- -- Tables and views published via GeoServer -- -INSERT INTO sys_admin.published_services (schema, name) VALUES - ('waterway', 'sections_geoserver'), - ('waterway', 'stretches_geoserver'), - ('waterway', 'fairway_dimensions'), - ('waterway', 'gauges_geoserver'), - ('waterway', 'distance_marks_ashore_geoserver'), - ('waterway', 'distance_marks_geoserver'), - ('waterway', 'sounding_results_contour_lines_geoserver'), - ('waterway', 'bottlenecks_geoserver'), - ('waterway', 'bottleneck_overview'), - ('waterway', 'waterway_axis'), - ('waterway', 'waterway_area'), - ('waterway', 'waterway_profiles'), - ('waterway', 'sounding_differences'); + +-- temporary table to store portions of SQL shared by multiple SQL views +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 + -- Directly accessed tables + ('waterway', 'waterway_axis', NULL, NULL), + ('waterway', 'waterway_area', NULL, NULL), + ('waterway', 'waterway_profiles', NULL, NULL), + ('waterway', 'fairway_dimensions', NULL, NULL), + -- GeoServer SQL views + ('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 + $$); -- -- Settings
--- a/schema/geoserver_views.sql Mon Sep 09 17:46:51 2019 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,219 +0,0 @@ -CREATE OR REPLACE VIEW waterway.gauges_base_view AS - 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); - -CREATE OR REPLACE 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; - -CREATE OR REPLACE 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; - -CREATE OR REPLACE VIEW waterway.distance_marks_ashore_geoserver AS - SELECT id, - country, - geom::Geometry(POINT, 4326), - related_enc, - hectom, - catdis, - position_code - FROM waterway.distance_marks; - -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 OR REPLACE 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 OR REPLACE 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; - -CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS - 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; - -CREATE OR REPLACE VIEW waterway.bottleneck_overview AS - 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; - -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, - 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;
--- a/schema/install-db.sh Mon Sep 09 17:46:51 2019 +0200 +++ b/schema/install-db.sh Tue Sep 10 19:56:43 2019 +0200 @@ -130,7 +130,6 @@ -f "$BASEDIR/search_functions.sql" \ -f "$BASEDIR/geonames.sql" \ -f "$BASEDIR/manage_users.sql" \ - -f "$BASEDIR/geoserver_views.sql" \ -f "$BASEDIR/auth.sql" \ -f "$BASEDIR/isrs_functions.sql" \ -f "$BASEDIR/default_sysconfig.sql" \
--- /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;