Mercurial > gemma

diff schema/default_sysconfig.sql @ 4620:f77a6f9216ae geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge default into geoserver_sql_views
author Tom Gottfried <tom@intevation.de>
date Fri, 20 Sep 2019 17:48:47 +0200
parents 4476ec4db818
children 30bb2d819d57
line wrap: on
line diff
--- a/schema/default_sysconfig.sql	Fri Sep 20 16:08:15 2019 +0200
+++ b/schema/default_sysconfig.sql	Fri Sep 20 17:48:47 2019 +0200
@@ -55,6 +55,7 @@
             USING (location, validity)
         LEFT JOIN (SELECT DISTINCT ON (location)
                     location,
+                    date_issue,
                     measure_date,
                     water_level,
                     count(*) OVER (PARTITION BY location) AS n
@@ -66,6 +67,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,
@@ -82,7 +84,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