Mercurial > gemma
changeset 4742:b88ab93dcb2c
Expose section country in GeoServer layer
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 18 Oct 2019 12:18:23 +0200 |
| parents | 5164b4450c42 |
| children | ac2c82719f7b |
| files | schema/default_sysconfig.sql schema/updates/1309/01.expose_section_country.sql schema/version.sql |
| diffstat | 3 files changed, 90 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/default_sysconfig.sql Fri Oct 18 12:11:45 2019 +0200 +++ b/schema/default_sysconfig.sql Fri Oct 18 12:18:23 2019 +0200 @@ -128,6 +128,7 @@ s.area, s.objnam, s.nobjnam, + s.country, s.date_info, s.source_organization, s.staging_done,
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1309/01.expose_section_country.sql Fri Oct 18 12:18:23 2019 +0200 @@ -0,0 +1,88 @@ +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 + -- consider all measurements within 14 days plus a tolerance + 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 + -- consider predictions made within last 14 days ... + WHERE date_issue + >= current_timestamp - '14 days 00:15'::interval + -- ... for the next three days from now + 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 + -- Show only forecasts issued with latest measurements or later + ON fca.location = g.location AND fca.date_issue >= wl.date_issue + $$); + +UPDATE sys_admin.published_services SET view_def = $$ + SELECT + s.id, + s.name, + (s.section).lower::varchar as lower, + (s.section).upper::varchar as upper, + s.area, + s.objnam, + s.nobjnam, + s.country, + 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 + $$ + WHERE schema = 'waterway' AND name = 'sections_geoserver'