Mercurial > gemma
diff schema/updates/1300/02.views_to_geoservers.sql @ 4620:f77a6f9216ae geoserver_sql_views
Merge default into geoserver_sql_views
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 20 Sep 2019 17:48:47 +0200 |
parents | 0f2c3cb139cc |
children | b03aa1502736 |
line wrap: on
line diff
--- a/schema/updates/1300/02.views_to_geoservers.sql Fri Sep 20 16:08:15 2019 +0200 +++ b/schema/updates/1300/02.views_to_geoservers.sql Fri Sep 20 17:48:47 2019 +0200 @@ -31,6 +31,7 @@ USING (location, validity) LEFT JOIN (SELECT DISTINCT ON (location) location, + date_issue, measure_date, water_level, count(*) OVER (PARTITION BY location) AS n @@ -42,6 +43,7 @@ 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, @@ -58,7 +60,8 @@ 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 - USING (location) + -- Show only forecasts issued with latest measurements or later + ON fca.location = g.location AND fca.date_issue >= wl.date_issue $$); INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES