Mercurial > gemma

comparison schema/geoserver_views.sql @ 3442:030dc48382c9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adapt color scheme for waterlevel prediction accuracy
author Tom Gottfried <tom@intevation.de>
date Fri, 24 May 2019 11:37:24 +0200
parents 6e15c3c1e9b7
children 7f65790b6bd6
comparison
equal deleted inserted replaced
3441:d7ec52232702 3442:030dc48382c9
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_3d,
22 fca.forecast_accuracy_1d 22 fca.forecast_accuracy_1d
23 FROM waterway.gauges g 23 FROM waterway.gauges g
24 LEFT JOIN (SELECT location, validity, 24 LEFT JOIN (SELECT location, validity,
25 json_strip_nulls(json_object_agg( 25 json_strip_nulls(json_object_agg(
26 coalesce(depth_reference, 'empty'), value)) AS rwls 26 coalesce(depth_reference, 'empty'), value)) AS rwls
31 USING (location) 31 USING (location)
32 LEFT JOIN (SELECT location, 32 LEFT JOIN (SELECT location,
33 max(acc) FILTER (WHERE 33 max(acc) FILTER (WHERE
34 measure_date <= current_timestamp + '1 day'::interval) 34 measure_date <= current_timestamp + '1 day'::interval)
35 AS forecast_accuracy_1d, 35 AS forecast_accuracy_1d,
36 max(acc) AS forecast_accuracy 36 max(acc) AS forecast_accuracy_3d
37 FROM waterway.gauge_predictions, 37 FROM waterway.gauge_predictions,
38 GREATEST(water_level - lower(conf_interval), 38 GREATEST(water_level - lower(conf_interval),
39 upper(conf_interval) - water_level) AS acc (acc) 39 upper(conf_interval) - water_level) AS acc (acc)
40 WHERE measure_date 40 WHERE measure_date
41 BETWEEN current_timestamp 41 BETWEEN current_timestamp
42 AND current_timestamp + '4 days'::interval 42 AND current_timestamp + '3 days'::interval
43 GROUP BY location) AS fca 43 GROUP BY location) AS fca
44 USING (location) 44 USING (location)
45 WHERE NOT g.erased; 45 WHERE NOT g.erased;
46 46
47 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS 47 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS