Mercurial > gemma
view schema/geoserver_views.sql @ 4606:dfe9cde6a20c geoserver_sql_views
Reflect database model changes for SQL views in backend
In principle, we could use many datasources with different database
schemas, but this would imply changing GeoServer initialization,
service filtering, endpoints and eventually more. Since we do not need
it, just hard-code the schema name as a constant.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 05 Sep 2019 12:23:31 +0200 |
parents | 0ba3fc89b499 |
children | a7196b55c064 |
line wrap: on
line source
CREATE OR REPLACE VIEW waterway.gauges_base_view 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, g.erased, r.rwls AS reference_water_levels, wl.measure_date AS gm_measuredate, wl.water_level AS gm_waterlevel, wl_14d.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, measure_date, water_level FROM waterway.gauge_measurements ORDER BY location, measure_date DESC) AS wl USING (location) LEFT JOIN (SELECT location, count(water_level) 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 GROUP BY location) AS wl_14d USING (location) LEFT JOIN (SELECT location, max(acc) FILTER (WHERE measure_date <= current_timestamp + '1 day'::interval) AS forecast_accuracy_1d, max(acc) AS forecast_accuracy_3d FROM waterway.gauge_predictions, GREATEST(water_level - lower(conf_interval), upper(conf_interval) - water_level) AS acc (acc) WHERE measure_date BETWEEN current_timestamp AND current_timestamp + '3 days'::interval GROUP BY location) AS fca 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, 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 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, 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 waterway.gauges_base_view 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 WHERE b.validity @> current_timestamp; CREATE OR REPLACE VIEW waterway.stretches_geoserver AS SELECT s.id, s.name, (s.stretch).lower::varchar as lower, (s.stretch).upper::varchar as upper, s.area::Geometry(MULTIPOLYGON, 4326), s.objnam, s.nobjnam, s.date_info, s.source_organization, (SELECT string_agg(country_code, ', ') FROM waterway.stretch_countries WHERE stretches_id = s.id) AS countries, s.staging_done, min(g.gm_measuredate) AS gm_measuredate, min(g.gm_n_14d) AS gm_n_14d, max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, max(g.forecast_accuracy_1d) AS forecast_accuracy_1d FROM waterway.stretches s LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.stretch GROUP BY s.id; CREATE OR REPLACE VIEW waterway.sections_geoserver AS SELECT s.id, s.name, (s.section).lower::varchar as lower, (s.section).upper::varchar as upper, s.area::Geometry(MULTIPOLYGON, 4326), s.objnam, s.nobjnam, s.date_info, s.source_organization, s.staging_done, min(g.gm_measuredate) AS gm_measuredate, min(g.gm_n_14d) AS gm_n_14d, max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, max(g.forecast_accuracy_1d) AS forecast_accuracy_1d FROM waterway.sections s LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.section GROUP BY s.id; 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.bottleneck_id WHERE bn.validity @> current_timestamp 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.bottleneck_id AND srm.date_info::timestamptz <@ bn.validity;