Mercurial > gemma
annotate schema/geoserver_views.sql @ 4542:56f4e8cbfab7 iso-areas
Sew together triangles that totally inside a single class.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 26 Sep 2019 18:11:38 +0200 |
parents | 9b9f60e26b39 |
children | c657dec6b0fa |
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, |
4449
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
18 wl.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, |
4453
9b9f60e26b39
Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents:
4449
diff
changeset
|
30 date_issue, |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
31 measure_date, |
4449
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
32 water_level, |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
33 count(*) OVER (PARTITION BY location) AS n |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
34 FROM waterway.gauge_measurements |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
35 -- 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
|
36 WHERE measure_date |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
37 >= current_timestamp - '14 days 00:15'::interval |
4449
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
38 ORDER BY location, measure_date DESC) AS wl |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
39 USING (location) |
4449
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
40 LEFT JOIN (SELECT DISTINCT ON (location) |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
41 location, |
4453
9b9f60e26b39
Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents:
4449
diff
changeset
|
42 date_issue, |
4449
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
43 max(acc) FILTER (WHERE measure_date |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
44 <= current_timestamp + '1 day'::interval) |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
45 OVER loc_date_issue AS forecast_accuracy_1d, |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
46 max(acc) OVER loc_date_issue AS forecast_accuracy_3d |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
47 FROM (SELECT location, date_issue, measure_date, |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
48 GREATEST(water_level - lower(conf_interval), |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
49 upper(conf_interval) - water_level) AS acc |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
50 FROM waterway.gauge_predictions |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
51 -- consider predictions made within last 14 days ... |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
52 WHERE date_issue |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
53 >= current_timestamp - '14 days 00:15'::interval |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
54 -- ... for the next three days from now |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
55 AND measure_date BETWEEN current_timestamp |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
56 AND current_timestamp + '3 days'::interval) AS acc |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
57 WINDOW loc_date_issue AS (PARTITION BY location, date_issue) |
ce884af9f42f
Fix forecast accuracy values and improve performance
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
58 ORDER BY location, date_issue DESC) AS fca |
4453
9b9f60e26b39
Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents:
4449
diff
changeset
|
59 -- Show only forecasts issued with latest measurements or later |
9b9f60e26b39
Show only forecasts issued with latest measurements or later
Tom Gottfried <tom@intevation.de>
parents:
4449
diff
changeset
|
60 ON fca.location = g.location AND fca.date_issue >= wl.date_issue; |
3620
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
61 |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
62 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
|
63 SELECT |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
64 isrs_code, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
65 objname, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
66 geom, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
67 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
|
68 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
|
69 zero_point, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
70 geodref, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
71 date_info, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
72 source_organization, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
73 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
|
74 gm_measuredate, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
75 gm_waterlevel, |
7e7a2b501595
Show gauge data in bottlenecks view regardless of whether gauge is erased
Tom Gottfried <tom@intevation.de>
parents:
3485
diff
changeset
|
76 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
|
77 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
|
78 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
|
79 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
|
80 WHERE NOT erased; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 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
|
83 SELECT |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
84 isrs_asText(location_code) AS location, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
85 geom::Geometry(POINT, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
86 related_enc, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
87 (location_code).hectometre |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 FROM waterway.distance_marks_virtual; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 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
|
91 SELECT id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
92 country, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 geom::Geometry(POINT, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
94 related_enc, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
95 hectom, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
96 catdis, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
97 position_code |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 FROM waterway.distance_marks; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
100 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
|
101 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 b.id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
103 b.bottleneck_id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
104 b.objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
105 b.nobjnm, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
106 b.area, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
107 b.rb, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
108 b.lb, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
109 b.responsible_country, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 b.revisiting_time, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
111 b.limiting, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
112 b.date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
113 b.source_organization, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
114 g.objname AS gauge_objname, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
115 g.reference_water_levels, |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
116 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
|
117 fal.critical AS fa_critical, |
3485
ef4803f87631
Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents:
3480
diff
changeset
|
118 g.gm_measuredate, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
119 g.gm_waterlevel, |
3485
ef4803f87631
Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents:
3480
diff
changeset
|
120 g.gm_n_14d, |
3469
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
121 srl.date_max, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
122 g.forecast_accuracy_3d, |
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
123 g.forecast_accuracy_1d |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
124 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
|
125 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
|
126 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
|
127 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
|
128 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
|
129 FROM waterway.fairway_availability |
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, 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
|
131 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
|
132 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
|
133 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
|
134 FROM waterway.sounding_results |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
135 GROUP BY bottleneck_id |
eb11ada33fa7
Avoid full sequential scans on some tables for every query
Tom Gottfried <tom@intevation.de>
parents:
3666
diff
changeset
|
136 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
|
137 ON b.bottleneck_id = srl.bottleneck_id |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
138 WHERE b.validity @> current_timestamp; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
139 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
140 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
|
141 SELECT |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
142 s.id, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
143 s.name, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
144 (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
|
145 (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
|
146 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
|
147 s.objnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
148 s.nobjnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
149 s.date_info, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
150 s.source_organization, |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
151 (SELECT string_agg(country, ', ') |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
152 FROM users.stretch_countries |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
153 WHERE stretch_id = s.id) AS countries, |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
154 s.staging_done, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
155 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
|
156 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
|
157 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
|
158 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
159 FROM users.stretches s |
4358
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
160 LEFT JOIN waterway.gauges_geoserver g |
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
161 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
|
162 GROUP BY s.id; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
163 |
3307
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
164 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
|
165 SELECT |
3636
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
166 s.id, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
167 s.name, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
168 (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
|
169 (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
|
170 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
|
171 s.objnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
172 s.nobjnam, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
173 s.date_info, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
174 s.source_organization, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
175 s.staging_done, |
89a39783c20a
Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents:
3620
diff
changeset
|
176 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
|
177 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
|
178 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
|
179 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
|
180 FROM waterway.sections s |
4358
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
181 LEFT JOIN waterway.gauges_geoserver g |
a7196b55c064
Remove VIEW columns ignored by GeoServer
Tom Gottfried <tom@intevation.de>
parents:
4067
diff
changeset
|
182 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
|
183 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
|
184 |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
185 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
|
186 SELECT bottleneck_id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
187 date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
188 height, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
189 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
|
190 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
|
191 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
|
192 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
193 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
|
194 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
195 objnam AS name, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
196 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
|
197 (lower(stretch)).hectometre AS from, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
198 (upper(stretch)).hectometre AS to, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
199 sr.current::text, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
200 responsible_country |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
201 FROM waterway.bottlenecks bn LEFT JOIN ( |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
202 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
|
203 FROM waterway.sounding_results |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
204 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
|
205 WHERE bn.validity @> current_timestamp |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
206 ORDER BY objnam; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
207 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
208 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
|
209 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
210 sd.id AS id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
211 bn.objnam AS objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
212 srm.date_info AS minuend, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
213 srs.date_info AS subtrahend, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
214 sdcl.height AS height, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
215 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines |
3010
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
216 FROM caching.sounding_differences sd |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
217 JOIN caching.sounding_differences_contour_lines sdcl |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
218 ON sd.id = sdcl.sounding_differences_id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
219 JOIN waterway.sounding_results srm |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
220 ON sd.minuend = srm.id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
221 JOIN waterway.sounding_results srs |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
222 ON sd.subtrahend = srs.id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
223 JOIN waterway.bottlenecks bn |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
224 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
|
225 AND srm.date_info::timestamptz <@ bn.validity; |