Mercurial > gemma
annotate schema/geoserver_views.sql @ 4365:e739a4806d7c
rounding to full days: Fixed typos. Make golint happy with the comments. Unexport local type. Simplify and tightend code.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Mon, 09 Sep 2019 18:07:10 +0200 |
parents | a7196b55c064 |
children | 5e38667f740c |
rev | line source |
---|---|
3620
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
1 CREATE OR REPLACE VIEW waterway.gauges_base_view AS |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 g.location, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 isrs_asText(g.location) AS isrs_code, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 g.objname, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 g.geom, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 g.applicability_from_km, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 g.applicability_to_km, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 g.validity, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 g.zero_point, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 g.geodref, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 g.date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 g.source_organization, |
3620
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
14 g.erased, |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
15 r.rwls AS reference_water_levels, |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
16 wl.measure_date AS gm_measuredate, |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
17 wl.water_level AS gm_waterlevel, |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
18 wl_14d.n AS gm_n_14d, |
3442
030dc48382c9
Adapt color scheme for waterlevel prediction accuracy
Tom Gottfried <tom@intevation.de>
parents:
3432
diff
changeset
|
19 fca.forecast_accuracy_3d, |
3432
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
20 fca.forecast_accuracy_1d |
3009
ddc4d54b8331
Remove unnecessary subselect
Tom Gottfried <tom@intevation.de>
parents:
3008
diff
changeset
|
21 FROM waterway.gauges g |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
22 LEFT JOIN (SELECT location, validity, |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
23 json_strip_nulls(json_object_agg( |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
24 coalesce(depth_reference, 'empty'), value)) AS rwls |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
25 FROM waterway.gauges_reference_water_levels |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
26 GROUP BY location, validity) AS r |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
27 USING (location, validity) |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
28 LEFT JOIN (SELECT DISTINCT ON (location) |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
29 location, |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
30 measure_date, |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
31 water_level |
3454
7f65790b6bd6
Avoid full sequential scan on measurements for every query
Tom Gottfried <tom@intevation.de>
parents:
3442
diff
changeset
|
32 FROM waterway.gauge_measurements |
7f65790b6bd6
Avoid full sequential scan on measurements for every query
Tom Gottfried <tom@intevation.de>
parents:
3442
diff
changeset
|
33 ORDER BY location, measure_date DESC) AS wl |
3361
3e7a670896bd
added latest waterlevel measurement to guages_geoserver view
Markus Kottlaender <markus@intevation.de>
parents:
3307
diff
changeset
|
34 USING (location) |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
35 LEFT JOIN (SELECT location, count(water_level) AS n |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
36 FROM waterway.gauge_measurements |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
37 -- consider all measurements within 14 days plus a tolerance |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
38 WHERE measure_date |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
39 >= current_timestamp - '14 days 00:15'::interval |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
40 GROUP BY location) AS wl_14d |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
41 USING (location) |
3432
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
42 LEFT JOIN (SELECT location, |
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
43 max(acc) FILTER (WHERE |
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
44 measure_date <= current_timestamp + '1 day'::interval) |
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
45 AS forecast_accuracy_1d, |
3442
030dc48382c9
Adapt color scheme for waterlevel prediction accuracy
Tom Gottfried <tom@intevation.de>
parents:
3432
diff
changeset
|
46 max(acc) AS forecast_accuracy_3d |
3432
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
47 FROM waterway.gauge_predictions, |
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
48 GREATEST(water_level - lower(conf_interval), |
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
49 upper(conf_interval) - water_level) AS acc (acc) |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
50 WHERE measure_date |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
51 BETWEEN current_timestamp |
3442
030dc48382c9
Adapt color scheme for waterlevel prediction accuracy
Tom Gottfried <tom@intevation.de>
parents:
3432
diff
changeset
|
52 AND current_timestamp + '3 days'::interval |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
53 GROUP BY location) AS fca |
3620
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
54 USING (location); |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
55 |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
56 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
57 SELECT |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
58 isrs_code, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
59 objname, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
60 geom, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
61 applicability_from_km, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
62 applicability_to_km, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
63 zero_point, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
64 geodref, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
65 date_info, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
66 source_organization, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
67 reference_water_levels, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
68 gm_measuredate, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
69 gm_waterlevel, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
70 gm_n_14d, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
71 forecast_accuracy_3d, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
72 forecast_accuracy_1d |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
73 FROM waterway.gauges_base_view |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
74 WHERE NOT erased; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS |
4358
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
77 SELECT |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 isrs_asText(location_code) AS location, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 geom::Geometry(POINT, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 related_enc, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 (location_code).hectometre |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 FROM waterway.distance_marks_virtual; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
84 CREATE OR REPLACE VIEW waterway.distance_marks_ashore_geoserver AS |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
85 SELECT id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
86 country, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
87 geom::Geometry(POINT, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 related_enc, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 hectom, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 catdis, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 position_code |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
92 FROM waterway.distance_marks; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
94 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
95 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
96 b.id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
97 b.bottleneck_id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 b.objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 b.nobjnm, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
100 b.area, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
101 b.rb, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 b.lb, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
103 b.responsible_country, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
104 b.revisiting_time, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
105 b.limiting, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
106 b.date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
107 b.source_organization, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
108 g.objname AS gauge_objname, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
109 g.reference_water_levels, |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 fal.date_info AS fa_date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
111 fal.critical AS fa_critical, |
3485
ef4803f87631
Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents:
3480
diff
changeset
|
112 g.gm_measuredate, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
113 g.gm_waterlevel, |
3485
ef4803f87631
Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents:
3480
diff
changeset
|
114 g.gm_n_14d, |
3469
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
115 srl.date_max, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
116 g.forecast_accuracy_3d, |
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
117 g.forecast_accuracy_1d |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
118 FROM waterway.bottlenecks b |
3620
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
119 LEFT JOIN waterway.gauges_base_view g |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4002
diff
changeset
|
120 ON b.gauge_location = g.location AND g.validity @> current_timestamp |
4002
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
121 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
122 bottleneck_id, date_info, critical |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
123 FROM waterway.fairway_availability |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
124 ORDER BY bottleneck_id, date_info DESC) AS fal |
4067
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
125 ON b.bottleneck_id = fal.bottleneck_id |
4002
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
126 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
127 bottleneck_id, max(date_info) AS date_max |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
128 FROM waterway.sounding_results |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
129 GROUP BY bottleneck_id |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
130 ORDER BY bottleneck_id DESC) AS srl |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
131 ON b.bottleneck_id = srl.bottleneck_id |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
132 WHERE b.validity @> current_timestamp; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
133 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
134 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
135 SELECT |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
136 s.id, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
137 s.name, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
138 (s.stretch).lower::varchar as lower, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
139 (s.stretch).upper::varchar as upper, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
140 s.area::Geometry(MULTIPOLYGON, 4326), |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
141 s.objnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
142 s.nobjnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
143 s.date_info, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
144 s.source_organization, |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
145 (SELECT string_agg(country_code, ', ') |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
146 FROM waterway.stretch_countries |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
147 WHERE stretches_id = s.id) AS countries, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
148 s.staging_done, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
149 min(g.gm_measuredate) AS gm_measuredate, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
150 min(g.gm_n_14d) AS gm_n_14d, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
151 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
152 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
153 FROM waterway.stretches s |
4358
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
154 LEFT JOIN waterway.gauges_geoserver g |
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
155 ON isrs_fromtext(g.isrs_code) <@ s.stretch |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
156 GROUP BY s.id; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
157 |
3307
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
158 CREATE OR REPLACE VIEW waterway.sections_geoserver AS |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
159 SELECT |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
160 s.id, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
161 s.name, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
162 (s.section).lower::varchar as lower, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
163 (s.section).upper::varchar as upper, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
164 s.area::Geometry(MULTIPOLYGON, 4326), |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
165 s.objnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
166 s.nobjnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
167 s.date_info, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
168 s.source_organization, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
169 s.staging_done, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
170 min(g.gm_measuredate) AS gm_measuredate, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
171 min(g.gm_n_14d) AS gm_n_14d, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
172 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
173 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
174 FROM waterway.sections s |
4358
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
175 LEFT JOIN waterway.gauges_geoserver g |
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
176 ON isrs_fromtext(g.isrs_code) <@ s.section |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
177 GROUP BY s.id; |
3307
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
178 |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
179 CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
180 SELECT bottleneck_id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
181 date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
182 height, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
183 CAST(lines AS geometry(multilinestring, 4326)) AS lines |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
184 FROM waterway.sounding_results_contour_lines cl |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
185 JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
186 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
187 CREATE OR REPLACE VIEW waterway.bottleneck_overview AS |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
188 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
189 objnam AS name, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
190 ST_Centroid(area)::Geometry(POINT, 4326) AS point, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
191 (lower(stretch)).hectometre AS from, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
192 (upper(stretch)).hectometre AS to, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
193 sr.current::text, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
194 responsible_country |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
195 FROM waterway.bottlenecks bn LEFT JOIN ( |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
196 SELECT bottleneck_id, max(date_info) AS current |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
197 FROM waterway.sounding_results |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
198 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
199 WHERE bn.validity @> current_timestamp |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
200 ORDER BY objnam; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
201 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
202 CREATE OR REPLACE VIEW waterway.sounding_differences AS |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
203 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
204 sd.id AS id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
205 bn.objnam AS objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
206 srm.date_info AS minuend, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
207 srs.date_info AS subtrahend, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
208 sdcl.height AS height, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
209 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines |
3010
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
210 FROM caching.sounding_differences sd |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
211 JOIN caching.sounding_differences_contour_lines sdcl |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
212 ON sd.id = sdcl.sounding_differences_id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
213 JOIN waterway.sounding_results srm |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
214 ON sd.minuend = srm.id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
215 JOIN waterway.sounding_results srs |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
216 ON sd.subtrahend = srs.id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
217 JOIN waterway.bottlenecks bn |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
218 ON srm.bottleneck_id = bn.bottleneck_id |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
219 AND srm.date_info::timestamptz <@ bn.validity; |