Mercurial > gemma
changeset 3620:7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 05 Jun 2019 16:33:57 +0200 |
parents | ca7821e1f720 |
children | 30a9fdac70f0 |
files | schema/geoserver_views.sql |
diffstat | 1 files changed, 26 insertions(+), 4 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/geoserver_views.sql Wed Jun 05 15:52:42 2019 +0200 +++ b/schema/geoserver_views.sql Wed Jun 05 16:33:57 2019 +0200 @@ -1,4 +1,4 @@ -CREATE OR REPLACE VIEW waterway.gauges_geoserver AS +CREATE OR REPLACE VIEW waterway.gauges_base_view AS SELECT g.location, isrs_asText(g.location) AS isrs_code, @@ -11,6 +11,7 @@ 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, @@ -50,8 +51,29 @@ BETWEEN current_timestamp AND current_timestamp + '3 days'::interval GROUP BY location) AS fca - USING (location) - WHERE NOT g.erased; + USING (location); + +CREATE OR REPLACE VIEW waterway.gauges_geoserver AS + SELECT + location, + isrs_code, + objname, + geom, + applicability_from_km, + applicability_to_km, + validity, + zero_point, + geodref, + date_info, + source_organization, + reference_water_levels, + gm_measuredate, + gm_waterlevel, + gm_n_14d, + forecast_accuracy_3d, + forecast_accuracy_1d + FROM waterway.gauges_base_view + WHERE NOT erased; CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS SELECT location_code, @@ -108,7 +130,7 @@ g.forecast_accuracy_3d, g.forecast_accuracy_1d FROM waterway.bottlenecks b - LEFT JOIN waterway.gauges_geoserver g + LEFT JOIN waterway.gauges_base_view g ON b.gauge_location = g.location AND b.gauge_validity = g.validity LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id