Mercurial > gemma

annotate schema/updates/1204/03.geoserver-views.sql @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents 0b01fd83bc3b
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4581
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE VIEW waterway.sounding_results_areas_geoserver AS
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 SELECT bottleneck_id,
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 date_info,
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 height,
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 CAST(areas AS geometry(multipolygon, 4326)) as areas
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 FROM waterway.sounding_results_iso_areas ia
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id;
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8
4583
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
9 CREATE OR REPLACE VIEW waterway.sounding_differences AS
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
10 SELECT
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
11 sd.id AS id,
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
12 bn.objnam AS objnam,
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
13 srm.date_info AS minuend,
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
14 srs.date_info AS subtrahend,
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
15 sdia.height AS height,
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
16 CAST(sdia.areas AS geometry(multipolygon, 4326)) AS areas
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
17 FROM caching.sounding_differences sd
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
18 JOIN caching.sounding_differences_iso_areas sdia
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
19 ON sd.id = sdia.sounding_differences_id
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
20 JOIN waterway.sounding_results srm
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
21 ON sd.minuend = srm.id
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
22 JOIN waterway.sounding_results srs
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
23 ON sd.subtrahend = srs.id
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
24 JOIN waterway.bottlenecks bn
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
25 ON srm.bottleneck_id = bn.bottleneck_id
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
26 AND srm.date_info::timestamptz <@ bn.validity;
0b01fd83bc3b Added views for sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4581
diff changeset
27
4581
01ea1e547c28 Added geoserver view for areas.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28 GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;