Mercurial > gemma
annotate schema/updates/1204/03.geoserver-views.sql @ 5364:1adbd6a5f849 extented-report
XLSX templater: Cache cell formulas for performance.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 23 Jun 2021 01:01:11 +0200 |
parents | 0b01fd83bc3b |
children |
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; |