Mercurial > gemma

annotate schema/geoserver_views.sql @ 4582:51dc19001808 iso-areas

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added geoserver view and named style to match.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 08 Oct 2019 16:13:53 +0200
parents c657dec6b0fa
children 0b01fd83bc3b
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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.bottleneck_overview AS
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
186 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
187 objnam AS name,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
188 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
189 (lower(stretch)).hectometre AS from,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
190 (upper(stretch)).hectometre AS to,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
191 sr.current::text,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
192 responsible_country
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
193 FROM waterway.bottlenecks bn LEFT JOIN (
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
194 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
195 FROM waterway.sounding_results
3656
2a079d0a71c1 Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents: 3645
diff changeset
196 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
197 WHERE bn.validity @> current_timestamp
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
198 ORDER BY objnam;
4582
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
199
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
200 CREATE OR REPLACE VIEW waterway.sounding_results_areas_geoserver AS
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
201 SELECT
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
202 bottleneck_id,
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
203 date_info,
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
204 height,
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
205 CAST(areas AS geometry(multipolygon, 4326)) as areas
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
206 FROM waterway.sounding_results_iso_areas ia
51dc19001808 Added geoserver view and named style to match.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4578
diff changeset
207 JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id;