Mercurial > gemma
diff schema/updates/1300/02.views_to_geoservers.sql @ 4617:ae840f9eb4c8 geoserver_sql_views
Advance schema version number
Do a bigger step to allow more schema changes in default
branch before we have to move here (branch geoserver_sql_views)
again.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 16 Sep 2019 16:56:11 +0200 |
parents | schema/updates/1113/02.views_to_geoservers.sql@b605e91f08f0 |
children | 0f2c3cb139cc |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1300/02.views_to_geoservers.sql Mon Sep 16 16:56:11 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;