Mercurial > gemma
view schema/updates/1430/01.bottlenecks_geoserver_add_time.sql @ 5650:cb3c4ba0f052 DQR adjustments
Merged default branch
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 29 Jun 2023 13:18:54 +0200 |
parents | 53618d18e387 |
children |
line wrap: on
line source
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.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, date_issue, measure_date, water_level, count(*) OVER (PARTITION BY location) AS n FROM waterway.gauge_measurements WHERE measure_date >= current_timestamp - '14 days 00:15'::interval ORDER BY location, measure_date DESC) AS wl USING (location) LEFT JOIN (SELECT DISTINCT ON (location) location, date_issue, max(acc) FILTER (WHERE measure_date <= current_timestamp + '1 day'::interval) OVER loc_date_issue AS forecast_accuracy_1d, max(acc) OVER loc_date_issue AS forecast_accuracy_3d FROM (SELECT location, date_issue, measure_date, GREATEST(water_level - lower(conf_interval), upper(conf_interval) - water_level) AS acc FROM waterway.gauge_predictions WHERE date_issue >= current_timestamp - '14 days 00:15'::interval AND measure_date BETWEEN current_timestamp AND current_timestamp + '3 days'::interval) AS acc WINDOW loc_date_issue AS (PARTITION BY location, date_issue) ORDER BY location, date_issue DESC) AS fca ON fca.location = g.location AND fca.date_issue >= wl.date_issue $$); UPDATE sys_admin.published_services SET wmst_attribute = 'valid_from', wmst_end_attribute = 'valid_to', view_def = $$ SELECT b.id, lower(b.validity) AS valid_from, COALESCE(upper(b.validity), current_timestamp) AS valid_to, 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 schema = 'waterway' AND name = 'bottlenecks_geoserver';