Created
July 22, 2021 12:11
-
-
Save ThomasJunk/330484ed1cb16b92a0fc00fd34b76e8e to your computer and use it in GitHub Desktop.
Geoserver view
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
b.id, | |
lower(b.validity) AS valid_from, | |
COALESCE(upper(b.validity), current_timestamp) AS valid_to, | |
b.bottleneck_id, | |
b.objnam, | |
b.nobjnm, | |
b.area, | |
b.rb, | |
b.lb, | |
b.responsible_country, | |
b.revisiting_time, | |
b.limiting, | |
b.date_info, | |
b.source_organization, | |
g.objname AS gauge_objname, | |
g.reference_water_levels, | |
fal.date_info AS fa_date_info, | |
fal.critical AS fa_critical, | |
g.gm_measuredate, | |
g.gm_waterlevel, | |
g.gm_n_14d, | |
srl.date_max, | |
g.forecast_accuracy_3d, | |
g.forecast_accuracy_1d | |
FROM waterway.bottlenecks b | |
LEFT JOIN ( | |
SELECT | |
g.location, | |
isrs_asText(g.location) AS isrs_code, | |
g.objname, | |
g.geom, | |
g.applicability_from_km, | |
g.applicability_to_km, | |
g.validity, | |
g.zero_point, | |
g.geodref, | |
g.date_info, | |
g.source_organization, | |
g.erased, | |
r.rwls AS reference_water_levels, | |
wl.measure_date AS gm_measuredate, | |
wl.water_level AS gm_waterlevel, | |
wl.n AS gm_n_14d, | |
fca.forecast_accuracy_3d, | |
fca.forecast_accuracy_1d | |
FROM waterway.gauges g | |
LEFT JOIN (SELECT location, validity, | |
json_strip_nulls(json_object_agg( | |
coalesce(depth_reference, 'empty'), value)) AS rwls | |
FROM waterway.gauges_reference_water_levels | |
GROUP BY location, validity) AS r | |
USING (location, validity) | |
LEFT JOIN (SELECT DISTINCT ON (location) | |
location, | |
date_issue, | |
measure_date, | |
water_level, | |
count(*) OVER (PARTITION BY location) AS n | |
FROM waterway.gauge_measurements | |
-- consider all measurements within 14 days plus a tolerance | |
WHERE measure_date | |
>= current_timestamp - '14 days 00:15'::interval | |
ORDER BY location, measure_date DESC) AS wl | |
USING (location) | |
LEFT JOIN (SELECT DISTINCT ON (location) | |
location, | |
date_issue, | |
max(acc) FILTER (WHERE measure_date | |
<= current_timestamp + '1 day'::interval) | |
OVER loc_date_issue AS forecast_accuracy_1d, | |
max(acc) OVER loc_date_issue AS forecast_accuracy_3d | |
FROM (SELECT location, date_issue, measure_date, | |
GREATEST(water_level - lower(conf_interval), | |
upper(conf_interval) - water_level) AS acc | |
FROM waterway.gauge_predictions | |
-- consider predictions made within last 14 days ... | |
WHERE date_issue | |
>= current_timestamp - '14 days 00:15'::interval | |
-- ... for the next three days from now | |
AND measure_date BETWEEN current_timestamp | |
AND current_timestamp + '3 days'::interval) AS acc | |
WINDOW loc_date_issue AS (PARTITION BY location, date_issue) | |
ORDER BY location, date_issue DESC) AS fca | |
-- Show only forecasts issued with latest measurements or later | |
ON fca.location = g.location AND fca.date_issue >= wl.date_issue | |
) AS g | |
ON b.gauge_location = g.location | |
AND g.validity @> current_timestamp | |
LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) | |
bottleneck_id, date_info, critical | |
FROM waterway.fairway_availability | |
ORDER BY bottleneck_id, date_info DESC) AS fal | |
ON b.bottleneck_id = fal.bottleneck_id | |
LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) | |
bottleneck_id, max(date_info) AS date_max | |
FROM waterway.sounding_results | |
GROUP BY bottleneck_id | |
ORDER BY bottleneck_id DESC) AS srl | |
ON b.bottleneck_id = srl.bottleneck_id | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment