Mercurial > gemma
comparison schema/geoserver_views.sql @ 3446:4a0fab451f87 fairway-avail-csv
Merged default into fairway-avail-csv branch
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 24 May 2019 12:14:57 +0200 |
parents | 030dc48382c9 |
children | 7f65790b6bd6 |
comparison
equal
deleted
inserted
replaced
3445:e07b18f2482e | 3446:4a0fab451f87 |
---|---|
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 |