Mercurial > gemma
changeset 2396:6af41bb6656f
Using a table in a view before it is created is not possible
The view bottlenecks_geoserver used the table fairway_availability before
it was created.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 27 Feb 2019 16:20:22 +0100 |
parents | 975e52210474 |
children | b95234702ee9 |
files | schema/gemma.sql |
diffstat | 1 files changed, 46 insertions(+), 45 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Feb 27 14:02:28 2019 +0100 +++ b/schema/gemma.sql Wed Feb 27 16:20:22 2019 +0100 @@ -473,51 +473,6 @@ PRIMARY KEY (bottleneck_id, riverbed) ) - -- Published view for GeoServer - CREATE VIEW bottlenecks_geoserver AS - WITH fairway_availability_latest AS ( - SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical - FROM fairway_availability - ORDER BY bottleneck_id, date_info DESC NULLS LAST), - gauge_measurements_waterlevel AS ( - SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id,measure_date,predicted,water_level - FROM gauge_measurements WHERE predicted ='false' - ORDER BY fk_gauge_id, measure_date DESC NULLS LAST) - 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_object_agg(r.depth_reference, r.value) AS reference_water_levels, - fal.date_info AS fa_date_info, - fal.critical AS fa_critical, - gmw.water_level as gm_waterlevel - FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location - LEFT JOIN LATERAL ( - SELECT gauge_id,depth_reference,value - FROM gauges_reference_water_levels - ) r ON r.gauge_id = b.fk_g_fid - LEFT JOIN LATERAL ( - SELECT bottleneck_id,date_info,critical - FROM fairway_availability_latest - WHERE b.id=bottleneck_id) fal ON TRUE - LEFT JOIN LATERAL ( - SELECT water_level - FROM gauge_measurements_waterlevel - WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE - GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level; - CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id int NOT NULL REFERENCES bottlenecks(id), @@ -625,6 +580,52 @@ SELECT bottleneck_id, max(date_info) AS current FROM sounding_results GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id ORDER BY objnam + + -- Published view for GeoServer + CREATE VIEW bottlenecks_geoserver AS + WITH fairway_availability_latest AS ( + SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical + FROM fairway_availability + ORDER BY bottleneck_id, date_info DESC NULLS LAST), + gauge_measurements_waterlevel AS ( + SELECT DISTINCT ON (fk_gauge_id) + fk_gauge_id, measure_date, predicted, water_level + FROM gauge_measurements WHERE predicted ='false' + ORDER BY fk_gauge_id, measure_date DESC NULLS LAST) + 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_object_agg(r.depth_reference, r.value) AS reference_water_levels, + fal.date_info AS fa_date_info, + fal.critical AS fa_critical, + gmw.water_level as gm_waterlevel + FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location + LEFT JOIN LATERAL ( + SELECT gauge_id,depth_reference,value + FROM gauges_reference_water_levels + ) r ON r.gauge_id = b.fk_g_fid + LEFT JOIN LATERAL ( + SELECT bottleneck_id,date_info,critical + FROM fairway_availability_latest + WHERE b.id=bottleneck_id) fal ON TRUE + LEFT JOIN LATERAL ( + SELECT water_level + FROM gauge_measurements_waterlevel + WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE + GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level; ; -- Configure primary keys for geoserver views