Mercurial > gemma
view 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 source
-- 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;