Mercurial > gemma
changeset 4583:0b01fd83bc3b iso-areas
Added views for sounding differences.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 08 Oct 2019 16:35:02 +0200 |
| parents | 51dc19001808 |
| children | 44b032028e48 |
| files | schema/default_sysconfig.sql schema/geoserver_views.sql schema/updates/1204/03.geoserver-views.sql schema/updates/1204/04.publish.sql |
| diffstat | 4 files changed, 46 insertions(+), 1 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/default_sysconfig.sql Tue Oct 08 16:13:53 2019 +0200 +++ b/schema/default_sysconfig.sql Tue Oct 08 16:35:02 2019 +0200 @@ -34,7 +34,8 @@ ('waterway.bottleneck_overview'), ('waterway.waterway_axis'), ('waterway.waterway_area'), - ('waterway.waterway_profiles'); + ('waterway.waterway_profiles'), + ('waterway.sounding_differences'); -- -- Settings
--- a/schema/geoserver_views.sql Tue Oct 08 16:13:53 2019 +0200 +++ b/schema/geoserver_views.sql Tue Oct 08 16:35:02 2019 +0200 @@ -205,3 +205,22 @@ 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;
--- a/schema/updates/1204/03.geoserver-views.sql Tue Oct 08 16:13:53 2019 +0200 +++ b/schema/updates/1204/03.geoserver-views.sql Tue Oct 08 16:35:02 2019 +0200 @@ -6,4 +6,23 @@ 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;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1204/04.publish.sql Tue Oct 08 16:35:02 2019 +0200 @@ -0,0 +1,6 @@ +INSERT INTO sys_admin.published_services (name) + VALUES ('waterway.sounding_results_areas_geoserver'::regclass) + ON CONFLICT (name) DO NOTHING; +INSERT INTO sys_admin.published_services (name) + VALUES ('waterway.sounding_differences'::regclass) + ON CONFLICT (name) DO NOTHING;