Mercurial > gemma
view schema/geoserver_views.sql @ 3008:f394e828a6d2
Separate view definitions for GeoServer from general schema definition
That way OR REPLACE can be used which makes development on the
views a lot easier, since the views can be altered without having
to set up a completely new database each time.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 11 Apr 2019 12:01:27 +0200 |
parents | |
children | ddc4d54b8331 |
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 LATERAL ( SELECT gauge_id, depth_reference, value FROM 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 AND NOT predicted 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;