Mercurial > gemma

annotate schema/geoserver_views.sql @ 3678:8f58851927c0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: make layer factory only return new layer config for individual maps instead of each time it is invoked. The purpose of the factory was to support multiple maps with individual layers. But returning a new config each time it is invoked leads to bugs that rely on the layer's state. Now this factory reuses the same objects it created before, per map.
author Markus Kottlaender <markus@intevation.de>
date Mon, 17 Jun 2019 17:31:35 +0200
parents db87f34805fb
children eb11ada33fa7
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,
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 WITH
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 fairway_availability_latest AS (
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 FROM waterway.fairway_availability
3469
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
101 ORDER BY bottleneck_id, date_info DESC),
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
102 sounding_result_latest AS (
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
103 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
104 FROM waterway.sounding_results
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
105 GROUP BY bottleneck_id
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
106 ORDER BY bottleneck_id DESC)
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 b.id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 b.bottleneck_id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 b.objnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 b.nobjnm,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 b.stretch,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 b.area,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 b.rb,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 b.lb,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116 b.responsible_country,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
117 b.revisiting_time,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
118 b.limiting,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119 b.date_info,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120 b.source_organization,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121 g.location AS gauge_isrs_code,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122 g.objname AS gauge_objname,
3456
ca395be62023 Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents: 3454
diff changeset
123 g.reference_water_levels,
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
124 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
125 fal.critical AS fa_critical,
3485
ef4803f87631 Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents: 3480
diff changeset
126 g.gm_measuredate,
3456
ca395be62023 Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents: 3454
diff changeset
127 g.gm_waterlevel,
3485
ef4803f87631 Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents: 3480
diff changeset
128 g.gm_n_14d,
3469
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
129 srl.date_max,
3456
ca395be62023 Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents: 3454
diff changeset
130 g.forecast_accuracy_3d,
ca395be62023 Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents: 3454
diff changeset
131 g.forecast_accuracy_1d
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
132 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
133 LEFT JOIN waterway.gauges_base_view g
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
134 ON b.gauge_location = g.location AND b.gauge_validity = g.validity
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
135 LEFT JOIN fairway_availability_latest fal
3469
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
136 ON b.id = fal.bottleneck_id
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
137 LEFT JOIN sounding_result_latest srl
3656
2a079d0a71c1 Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents: 3645
diff changeset
138 ON b.bottleneck_id = srl.bottleneck_id
3666
db87f34805fb Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
139 WHERE b.validity @> current_timestamp;
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
140
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
141 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
142 SELECT
3636
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
143 s.id,
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
144 s.name,
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
145 (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
146 (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
147 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
148 s.objnam,
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
149 s.nobjnam,
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
150 s.date_info,
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
151 s.source_organization,
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
152 (SELECT string_agg(country_code, ', ')
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
153 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
154 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
155 s.staging_done,
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
156 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
157 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
158 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
159 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
160 FROM waterway.stretches s
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
161 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
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
89a39783c20a Add forecast and measurement quality parameters to sections and stretches
Tom Gottfried <tom@intevation.de>
parents: 3620
diff changeset
181 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
182 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
183
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
184 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
185 SELECT bottleneck_id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
186 date_info,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
187 height,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
188 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
189 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
190 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
191
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
192 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
193 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
194 objnam AS name,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
195 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
196 (lower(stretch)).hectometre AS from,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
197 (upper(stretch)).hectometre AS to,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
198 sr.current::text,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
199 responsible_country
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
200 FROM waterway.bottlenecks bn LEFT JOIN (
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
201 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
202 FROM waterway.sounding_results
3656
2a079d0a71c1 Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents: 3645
diff changeset
203 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
204 WHERE bn.validity @> current_timestamp
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
205 ORDER BY objnam;
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
206
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
207 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
208 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
209 sd.id AS id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
210 bn.objnam AS objnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
211 srm.date_info AS minuend,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
212 srs.date_info AS subtrahend,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
213 sdcl.height AS height,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
214 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
3010
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
215 FROM caching.sounding_differences sd
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
216 JOIN caching.sounding_differences_contour_lines sdcl
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
217 ON sd.id = sdcl.sounding_differences_id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
218 JOIN waterway.sounding_results srm
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
219 ON sd.minuend = srm.id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
220 JOIN waterway.sounding_results srs
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
221 ON sd.subtrahend = srs.id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
222 JOIN waterway.bottlenecks bn
3656
2a079d0a71c1 Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents: 3645
diff changeset
223 ON srm.bottleneck_id = bn.bottleneck_id
2a079d0a71c1 Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents: 3645
diff changeset
224 AND srm.bottleneck_validity = bn.validity;