Mercurial > gemma
annotate schema/geoserver_views.sql @ 4606:dfe9cde6a20c geoserver_sql_views
Reflect database model changes for SQL views in backend
In principle, we could use many datasources with different database
schemas, but this would imply changing GeoServer initialization,
service filtering, endpoints and eventually more. Since we do not need
it, just hard-code the schema name as a constant.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 05 Sep 2019 12:23:31 +0200 |
parents | 0ba3fc89b499 |
children | a7196b55c064 |
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 location, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
59 isrs_code, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
60 objname, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
61 geom, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
62 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
|
63 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
|
64 validity, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
65 zero_point, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
66 geodref, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
67 date_info, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
68 source_organization, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
69 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
|
70 gm_measuredate, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
71 gm_waterlevel, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
72 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
|
73 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
|
74 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
|
75 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
|
76 WHERE NOT erased; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
77 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 SELECT location_code, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 isrs_asText(location_code) AS location, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 geom::Geometry(POINT, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 related_enc, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 (location_code).hectometre |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
84 FROM waterway.distance_marks_virtual; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
85 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
86 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
|
87 SELECT id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 country, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 geom::Geometry(POINT, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 related_enc, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 hectom, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
92 catdis, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 position_code |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
94 FROM waterway.distance_marks; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
95 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
96 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
|
97 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 b.id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 b.bottleneck_id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
100 b.objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
101 b.nobjnm, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 b.stretch, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
103 b.area, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
104 b.rb, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
105 b.lb, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
106 b.responsible_country, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
107 b.revisiting_time, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
108 b.limiting, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
109 b.date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 b.source_organization, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
111 g.location AS gauge_isrs_code, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
112 g.objname AS gauge_objname, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
113 g.reference_water_levels, |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
114 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
|
115 fal.critical AS fa_critical, |
3485
ef4803f87631
Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents:
3480
diff
changeset
|
116 g.gm_measuredate, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
117 g.gm_waterlevel, |
3485
ef4803f87631
Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents:
3480
diff
changeset
|
118 g.gm_n_14d, |
3469
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
119 srl.date_max, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
120 g.forecast_accuracy_3d, |
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
121 g.forecast_accuracy_1d |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
122 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
|
123 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
|
124 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
|
125 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
|
126 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
|
127 FROM waterway.fairway_availability |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
128 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
|
129 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
|
130 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
|
131 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
|
132 FROM waterway.sounding_results |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
133 GROUP BY bottleneck_id |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
134 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
|
135 ON b.bottleneck_id = srl.bottleneck_id |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
136 WHERE b.validity @> current_timestamp; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
137 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
138 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
|
139 SELECT |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
140 s.id, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
141 s.name, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
142 (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
|
143 (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
|
144 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
|
145 s.objnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
146 s.nobjnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
147 s.date_info, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
148 s.source_organization, |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
149 (SELECT string_agg(country_code, ', ') |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
150 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
|
151 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
|
152 s.staging_done, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
153 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
|
154 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
|
155 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
|
156 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
|
157 FROM waterway.stretches s |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
158 LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.stretch |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
159 GROUP BY s.id; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
160 |
3307
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
161 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
|
162 SELECT |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
163 s.id, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
164 s.name, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
165 (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
|
166 (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
|
167 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
|
168 s.objnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
169 s.nobjnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
170 s.date_info, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
171 s.source_organization, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
172 s.staging_done, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
173 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
|
174 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
|
175 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
|
176 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
|
177 FROM waterway.sections s |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
178 LEFT JOIN waterway.gauges_geoserver g ON g.location <@ s.section |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
179 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
|
180 |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
181 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
|
182 SELECT bottleneck_id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
183 date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
184 height, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
185 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
|
186 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
|
187 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
|
188 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
189 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
|
190 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
191 objnam AS name, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
192 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
|
193 (lower(stretch)).hectometre AS from, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
194 (upper(stretch)).hectometre AS to, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
195 sr.current::text, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
196 responsible_country |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
197 FROM waterway.bottlenecks bn LEFT JOIN ( |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
198 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
|
199 FROM waterway.sounding_results |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
200 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
|
201 WHERE bn.validity @> current_timestamp |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
202 ORDER BY objnam; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
203 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
204 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
|
205 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
206 sd.id AS id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
207 bn.objnam AS objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
208 srm.date_info AS minuend, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
209 srs.date_info AS subtrahend, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
210 sdcl.height AS height, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
211 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines |
3010
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
212 FROM caching.sounding_differences sd |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
213 JOIN caching.sounding_differences_contour_lines sdcl |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
214 ON sd.id = sdcl.sounding_differences_id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
215 JOIN waterway.sounding_results srm |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
216 ON sd.minuend = srm.id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
217 JOIN waterway.sounding_results srs |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
218 ON sd.subtrahend = srs.id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
219 JOIN waterway.bottlenecks bn |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
220 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
|
221 AND srm.date_info::timestamptz <@ bn.validity; |