Mercurial > gemma
view schema/updates/1204/03.geoserver-views.sql @ 4701:ef2cf9c413e9
Merged
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 16 Oct 2019 16:51:09 +0200 |
parents | 0b01fd83bc3b |
children |
line wrap: on
line source
CREATE OR REPLACE VIEW waterway.sounding_results_areas_geoserver AS SELECT bottleneck_id, date_info, height, CAST(areas AS geometry(multipolygon, 4326)) as areas FROM waterway.sounding_results_iso_areas ia JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id; 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, sdia.height AS height, CAST(sdia.areas AS geometry(multipolygon, 4326)) AS areas FROM caching.sounding_differences sd JOIN caching.sounding_differences_iso_areas sdia ON sd.id = sdia.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; GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;