Mercurial > gemma
diff schema/default_sysconfig.sql @ 5092:8dc27fc1d05c
Add temporal validity attributes and WMS-T config to bottlenecks layer
Attributes related to data availability and quality still refer to
the current situation.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Tue, 24 Mar 2020 12:32:42 +0100 |
parents | cf25b23e3eec |
children | f11b9b50fcc9 |
line wrap: on
line diff
--- a/schema/default_sysconfig.sql Tue Mar 24 11:23:26 2020 +0100 +++ b/schema/default_sysconfig.sql Tue Mar 24 12:32:42 2020 +0100 @@ -218,49 +218,6 @@ FROM waterway.sounding_results_iso_areas ia JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id $$), - ('waterway', 'bottlenecks_geoserver', 4326, 'id', $$ - 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, NULL, $$ SELECT objnam AS name, @@ -304,6 +261,51 @@ wmst_attribute, wmst_end_attribute, view_def ) VALUES + ('waterway', 'bottlenecks_geoserver', 4326, 'id', + 'valid_from', 'valid_to', $$ + 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 + $$), ('waterway', 'waterway_axis', 4326, 'id', 'valid_from', 'valid_to', $$ SELECT id,