Mercurial > gemma
view schema/updates/1430/01.bottlenecks_geoserver_add_time.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
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';