Mercurial > gemma
changeset 5098:52aac557cbd7 queued-stage-done
Merged default intp 'queued-stage-done' branch.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 24 Mar 2020 13:07:24 +0100 |
| parents | 6d5f711dff61 (diff) 8dc27fc1d05c (current diff) |
| children | 3cd736acbad3 |
| files | schema/updates/1431/01.bottlenecks_geoserver_add_time.sql |
| diffstat | 5 files changed, 111 insertions(+), 110 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Tue Mar 24 12:32:42 2020 +0100 +++ b/schema/gemma.sql Tue Mar 24 13:07:24 2020 +0100 @@ -1217,7 +1217,7 @@ 'queued', 'running', 'failed', 'unchanged', 'pending', - 'accepted', 'declined' + 'accepted', 'declined', 'reviewed' ); CREATE TYPE log_type AS ENUM ('info', 'warn', 'error');
--- a/schema/updates/1430/01.bottlenecks_geoserver_add_time.sql Tue Mar 24 12:32:42 2020 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,108 +0,0 @@ -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';
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1431/01.add_state.sql Tue Mar 24 13:07:24 2020 +0100 @@ -0,0 +1,1 @@ +ALTER TYPE import_state ADD VALUE 'reviewed';
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1431/01.bottlenecks_geoserver_add_time.sql Tue Mar 24 13:07:24 2020 +0100 @@ -0,0 +1,108 @@ +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';