Mercurial > gemma
comparison schema/geoserver_views.sql @ 3431:adf06af7a79c
Add forecast accuracy to gauges view
Forecast accuracy is calculated as the maximum deviation of confidence
interval bounds from the prediction within the next four days.
The JSON-aggregate of reference water levels is moved into a
sub-select to avoid having to add further independent columns to the
GROUP BY clause of the main statement.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 23 May 2019 17:29:34 +0200 |
parents | 3e7a670896bd |
children | 6e15c3c1e9b7 |
comparison
equal
deleted
inserted
replaced
3430:6994602d2935 | 3431:adf06af7a79c |
---|---|
14 g.validity, | 14 g.validity, |
15 g.zero_point, | 15 g.zero_point, |
16 g.geodref, | 16 g.geodref, |
17 g.date_info, | 17 g.date_info, |
18 g.source_organization, | 18 g.source_organization, |
19 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), | 19 r.rwls AS reference_water_levels, |
20 r.value)) | 20 wl.water_level AS gm_waterlevel, |
21 AS reference_water_levels, | 21 fca.forecast_accuracy |
22 wl.water_level AS gm_waterlevel | |
23 FROM waterway.gauges g | 22 FROM waterway.gauges g |
24 LEFT JOIN waterway.gauges_reference_water_levels r | 23 LEFT JOIN (SELECT location, validity, |
24 json_strip_nulls(json_object_agg( | |
25 coalesce(depth_reference, 'empty'), value)) AS rwls | |
26 FROM waterway.gauges_reference_water_levels | |
27 GROUP BY location, validity) AS r | |
25 USING (location, validity) | 28 USING (location, validity) |
26 LEFT JOIN waterlevel_latest wl | 29 LEFT JOIN waterlevel_latest wl |
27 USING (location) | 30 USING (location) |
28 WHERE NOT g.erased | 31 LEFT JOIN (SELECT location, max(GREATEST( |
29 GROUP BY g.location, g.validity, wl.water_level; | 32 water_level - lower(conf_interval), |
33 upper(conf_interval) - water_level)) AS forecast_accuracy | |
34 FROM waterway.gauge_predictions | |
35 WHERE measure_date | |
36 BETWEEN current_timestamp | |
37 AND current_timestamp + '4 days'::interval | |
38 GROUP BY location) AS fca | |
39 USING (location) | |
40 WHERE NOT g.erased; | |
30 | 41 |
31 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS | 42 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS |
32 SELECT location_code, | 43 SELECT location_code, |
33 isrs_asText(location_code) AS location, | 44 isrs_asText(location_code) AS location, |
34 geom::Geometry(POINT, 4326), | 45 geom::Geometry(POINT, 4326), |