Mercurial > gemma
view schema/updates/1112/01.cleanup_views.sql @ 5221:6794e85dc2dd new-fwa
Write headers.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 12 May 2020 10:29:25 +0200 |
parents | 81dc260b38aa |
children |
line wrap: on
line source
-- Avoid orphaned entries due to changed OIDs ALTER TABLE sys_admin.published_services ADD _name varchar; UPDATE sys_admin.published_services SET _name = name; DROP VIEW waterway.gauges_geoserver CASCADE; CREATE VIEW waterway.gauges_geoserver AS SELECT isrs_code, objname, geom, applicability_from_km, applicability_to_km, 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; DROP VIEW waterway.distance_marks_geoserver; CREATE VIEW waterway.distance_marks_geoserver AS SELECT isrs_asText(location_code) AS location, geom::Geometry(POINT, 4326), related_enc, (location_code).hectometre FROM waterway.distance_marks_virtual; DROP VIEW waterway.bottlenecks_geoserver; CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS SELECT b.id, b.bottleneck_id, b.objnam, b.nobjnm, b.area, b.rb, b.lb, b.responsible_country, b.revisiting_time, b.limiting, b.date_info, b.source_organization, 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 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 isrs_fromtext(g.isrs_code) <@ s.stretch GROUP BY s.id; CREATE 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 isrs_fromtext(g.isrs_code) <@ s.section GROUP BY s.id; -- Avoid orphaned entries due to changed OIDs UPDATE sys_admin.published_services SET name = _name; ALTER TABLE sys_admin.published_services DROP _name; UPDATE waterway.gt_pk_metadata SET pk_column = 'isrs_code' WHERE table_schema = 'waterway' AND table_name = 'gauges_geoserver'; UPDATE waterway.gt_pk_metadata SET pk_column = 'location' WHERE table_schema = 'waterway' AND table_name = 'distance_marks_geoserver'; GRANT SELECT ON waterway.gauges_geoserver, waterway.distance_marks_geoserver, waterway.bottlenecks_geoserver, waterway.stretches_geoserver, waterway.sections_geoserver TO waterway_user;