Mercurial > gemma

comparison schema/geoserver_views.sql @ 3431:adf06af7a79c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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),