Mercurial > gemma

comparison schema/geoserver_views.sql @ 3432:6e15c3c1e9b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add forecast accuracy within next day to gauges view
author Tom Gottfried <tom@intevation.de>
date Thu, 23 May 2019 18:36:26 +0200
parents adf06af7a79c
children 030dc48382c9
comparison
equal deleted inserted replaced
3431:adf06af7a79c 3432:6e15c3c1e9b7
16 g.geodref, 16 g.geodref,
17 g.date_info, 17 g.date_info,
18 g.source_organization, 18 g.source_organization,
19 r.rwls AS reference_water_levels, 19 r.rwls AS reference_water_levels,
20 wl.water_level AS gm_waterlevel, 20 wl.water_level AS gm_waterlevel,
21 fca.forecast_accuracy 21 fca.forecast_accuracy,
22 fca.forecast_accuracy_1d
22 FROM waterway.gauges g 23 FROM waterway.gauges g
23 LEFT JOIN (SELECT location, validity, 24 LEFT JOIN (SELECT location, validity,
24 json_strip_nulls(json_object_agg( 25 json_strip_nulls(json_object_agg(
25 coalesce(depth_reference, 'empty'), value)) AS rwls 26 coalesce(depth_reference, 'empty'), value)) AS rwls
26 FROM waterway.gauges_reference_water_levels 27 FROM waterway.gauges_reference_water_levels
27 GROUP BY location, validity) AS r 28 GROUP BY location, validity) AS r
28 USING (location, validity) 29 USING (location, validity)
29 LEFT JOIN waterlevel_latest wl 30 LEFT JOIN waterlevel_latest wl
30 USING (location) 31 USING (location)
31 LEFT JOIN (SELECT location, max(GREATEST( 32 LEFT JOIN (SELECT location,
32 water_level - lower(conf_interval), 33 max(acc) FILTER (WHERE
33 upper(conf_interval) - water_level)) AS forecast_accuracy 34 measure_date <= current_timestamp + '1 day'::interval)
34 FROM waterway.gauge_predictions 35 AS forecast_accuracy_1d,
36 max(acc) AS forecast_accuracy
37 FROM waterway.gauge_predictions,
38 GREATEST(water_level - lower(conf_interval),
39 upper(conf_interval) - water_level) AS acc (acc)
35 WHERE measure_date 40 WHERE measure_date
36 BETWEEN current_timestamp 41 BETWEEN current_timestamp
37 AND current_timestamp + '4 days'::interval 42 AND current_timestamp + '4 days'::interval
38 GROUP BY location) AS fca 43 GROUP BY location) AS fca
39 USING (location) 44 USING (location)