Mercurial > gemma
view schema/geoserver_views.sql @ 3277:232fc90e6ee2
Disentangle gauge measurements and predictions
Representing both in one table has led to the necessity to make the
distinction at many places such as statements, definitions of partial
indexes and application code. At least in one place in the AGM
import the distinction in application code was too late and
measurements matching an approved measurement could have been missed.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 15 May 2019 19:08:49 +0200 |
parents | 293bdd05ffcd |
children | 831193935739 |
line wrap: on
line source
CREATE OR REPLACE VIEW waterway.gauges_geoserver AS 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, json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), r.value)) AS reference_water_levels FROM waterway.gauges g LEFT JOIN waterway.gauges_reference_water_levels r ON r.gauge_id = g.location GROUP BY g.location; CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS SELECT location_code, isrs_asText(location_code) AS location, geom::Geometry(POINT, 4326), related_enc, (location_code).hectometre FROM waterway.distance_marks_virtual; CREATE OR REPLACE VIEW waterway.distance_marks_ashore_geoserver AS SELECT id, country, geom::Geometry(POINT, 4326), related_enc, hectom, catdis, position_code FROM waterway.distance_marks; CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS WITH fairway_availability_latest AS ( SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical FROM waterway.fairway_availability ORDER BY bottleneck_id, date_info DESC), waterlevel_latest AS ( SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level FROM waterway.gauge_measurements WHERE is_waterlevel ORDER BY fk_gauge_id, measure_date DESC) SELECT b.id, b.bottleneck_id, b.objnam, b.nobjnm, b.stretch, b.area, b.rb, b.lb, b.responsible_country, b.revisiting_time, b.limiting, b.date_info, b.source_organization, g.location AS gauge_isrs_code, g.objname AS gauge_objname, json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'), r.value)) AS reference_water_levels, fal.date_info AS fa_date_info, fal.critical AS fa_critical, wl.water_level AS gm_waterlevel FROM waterway.bottlenecks b LEFT JOIN waterway.gauges g ON b.fk_g_fid = g.location LEFT JOIN waterway.gauges_reference_water_levels r ON g.location = r.gauge_id LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id LEFT JOIN waterlevel_latest wl ON b.fk_g_fid = wl.fk_gauge_id GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level; CREATE OR REPLACE VIEW waterway.stretches_geoserver AS SELECT id, name, (stretch).lower::varchar as lower, (stretch).upper::varchar as upper, area::Geometry(MULTIPOLYGON, 4326), objnam, nobjnam, date_info, source_organization, (SELECT string_agg(country_code, ', ') FROM waterway.stretch_countries WHERE stretches_id = id) AS countries, staging_done FROM waterway.stretches; CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS SELECT bottleneck_id, date_info, height, CAST(lines AS geometry(multilinestring, 4326)) AS lines FROM waterway.sounding_results_contour_lines cl JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id; CREATE OR REPLACE VIEW waterway.bottleneck_overview AS SELECT objnam AS name, ST_Centroid(area)::Geometry(POINT, 4326) AS point, (lower(stretch)).hectometre AS from, (upper(stretch)).hectometre AS to, sr.current::text, responsible_country FROM waterway.bottlenecks bn LEFT JOIN ( SELECT bottleneck_id, max(date_info) AS current FROM waterway.sounding_results GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id ORDER BY objnam; CREATE OR REPLACE VIEW waterway.sounding_differences AS SELECT sd.id AS id, bn.objnam AS objnam, srm.date_info AS minuend, srs.date_info AS subtrahend, sdcl.height AS height, CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines FROM caching.sounding_differences sd JOIN caching.sounding_differences_contour_lines sdcl ON sd.id = sdcl.sounding_differences_id JOIN waterway.sounding_results srm ON sd.minuend = srm.id JOIN waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN waterway.bottlenecks bn ON srm.bottleneck_id = bn.id;