Mercurial > gemma
annotate schema/geoserver_views.sql @ 3618:c03170a1c333
client: waterlevel: check if there is prediction data before drawing the predictionarea
author | Fadi Abbud <fadi.abbud@intevation.de> |
---|---|
date | Wed, 05 Jun 2019 15:47:30 +0200 |
parents | ef4803f87631 |
children | 7e7a2b501595 |
rev | line source |
---|---|
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS |
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, |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
14 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
|
15 wl.measure_date AS gm_measuredate, |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
16 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
|
17 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
|
18 fca.forecast_accuracy_3d, |
3432
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
19 fca.forecast_accuracy_1d |
3009
ddc4d54b8331
Remove unnecessary subselect
Tom Gottfried <tom@intevation.de>
parents:
3008
diff
changeset
|
20 FROM waterway.gauges g |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
21 LEFT JOIN (SELECT location, validity, |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
22 json_strip_nulls(json_object_agg( |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
23 coalesce(depth_reference, 'empty'), value)) AS rwls |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
24 FROM waterway.gauges_reference_water_levels |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
25 GROUP BY location, validity) AS r |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
26 USING (location, validity) |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
27 LEFT JOIN (SELECT DISTINCT ON (location) |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
28 location, |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
29 measure_date, |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
30 water_level |
3454
7f65790b6bd6
Avoid full sequential scan on measurements for every query
Tom Gottfried <tom@intevation.de>
parents:
3442
diff
changeset
|
31 FROM waterway.gauge_measurements |
7f65790b6bd6
Avoid full sequential scan on measurements for every query
Tom Gottfried <tom@intevation.de>
parents:
3442
diff
changeset
|
32 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
|
33 USING (location) |
3480
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
34 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
|
35 FROM waterway.gauge_measurements |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
36 -- 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
|
37 WHERE measure_date |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
38 >= 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
|
39 GROUP BY location) AS wl_14d |
965b2fbb1890
Colorize gauge symbol based on availability of measurements
Tom Gottfried <tom@intevation.de>
parents:
3469
diff
changeset
|
40 USING (location) |
3432
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
41 LEFT JOIN (SELECT location, |
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
42 max(acc) FILTER (WHERE |
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
43 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
|
44 AS forecast_accuracy_1d, |
3442
030dc48382c9
Adapt color scheme for waterlevel prediction accuracy
Tom Gottfried <tom@intevation.de>
parents:
3432
diff
changeset
|
45 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
|
46 FROM waterway.gauge_predictions, |
6e15c3c1e9b7
Add forecast accuracy within next day to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3431
diff
changeset
|
47 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
|
48 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
|
49 WHERE measure_date |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
50 BETWEEN current_timestamp |
3442
030dc48382c9
Adapt color scheme for waterlevel prediction accuracy
Tom Gottfried <tom@intevation.de>
parents:
3432
diff
changeset
|
51 AND current_timestamp + '3 days'::interval |
3431
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
52 GROUP BY location) AS fca |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
53 USING (location) |
adf06af7a79c
Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents:
3361
diff
changeset
|
54 WHERE NOT g.erased; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 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
|
57 SELECT location_code, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 isrs_asText(location_code) AS location, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
59 geom::Geometry(POINT, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 related_enc, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 (location_code).hectometre |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 FROM waterway.distance_marks_virtual; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
64 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
|
65 SELECT id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 country, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 geom::Geometry(POINT, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 related_enc, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
69 hectom, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
70 catdis, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 position_code |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 FROM waterway.distance_marks; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
73 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 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
|
75 WITH |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 fairway_availability_latest AS ( |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
77 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
|
78 FROM waterway.fairway_availability |
3469
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
79 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
|
80 sounding_result_latest AS ( |
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
81 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
|
82 FROM waterway.sounding_results |
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
83 GROUP BY bottleneck_id |
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
84 ORDER BY bottleneck_id DESC) |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
85 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
86 b.id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
87 b.bottleneck_id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 b.objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 b.nobjnm, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 b.stretch, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 b.area, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
92 b.rb, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 b.lb, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
94 b.responsible_country, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
95 b.revisiting_time, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
96 b.limiting, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
97 b.date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
98 b.source_organization, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 g.location AS gauge_isrs_code, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
100 g.objname AS gauge_objname, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
101 g.reference_water_levels, |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 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
|
103 fal.critical AS fa_critical, |
3485
ef4803f87631
Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents:
3480
diff
changeset
|
104 g.gm_measuredate, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
105 g.gm_waterlevel, |
3485
ef4803f87631
Add 'translation' and bottleneck attributes for measurement availability
Tom Gottfried <tom@intevation.de>
parents:
3480
diff
changeset
|
106 g.gm_n_14d, |
3469
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
107 srl.date_max, |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
108 g.forecast_accuracy_3d, |
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
109 g.forecast_accuracy_1d |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 FROM waterway.bottlenecks b |
3456
ca395be62023
Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents:
3454
diff
changeset
|
111 LEFT JOIN waterway.gauges_geoserver g |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
112 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
|
113 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
|
114 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
|
115 LEFT JOIN sounding_result_latest srl |
096968d5628f
Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents:
3456
diff
changeset
|
116 ON b.id = srl.bottleneck_id; |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
117 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
118 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
|
119 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
120 id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
121 name, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
122 (stretch).lower::varchar as lower, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
123 (stretch).upper::varchar as upper, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
124 area::Geometry(MULTIPOLYGON, 4326), |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
125 objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
126 nobjnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
127 date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
128 source_organization, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
129 (SELECT string_agg(country_code, ', ') |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
130 FROM waterway.stretch_countries |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
131 WHERE stretches_id = id) AS countries, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
132 staging_done |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
133 FROM waterway.stretches; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
134 |
3307
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
135 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
|
136 SELECT |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
137 id, |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
138 name, |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
139 (section).lower::varchar as lower, |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
140 (section).upper::varchar as upper, |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
141 area::Geometry(MULTIPOLYGON, 4326), |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
142 objnam, |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
143 nobjnam, |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
144 date_info, |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
145 source_organization, |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
146 staging_done |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
147 FROM waterway.sections; |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
148 |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
149 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
|
150 SELECT bottleneck_id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
151 date_info, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
152 height, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
153 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
|
154 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
|
155 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
|
156 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
157 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
|
158 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
159 objnam AS name, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
160 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
|
161 (lower(stretch)).hectometre AS from, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
162 (upper(stretch)).hectometre AS to, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
163 sr.current::text, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
164 responsible_country |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
165 FROM waterway.bottlenecks bn LEFT JOIN ( |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
166 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
|
167 FROM waterway.sounding_results |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
168 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
169 ORDER BY objnam; |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
170 |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
171 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
|
172 SELECT |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
173 sd.id AS id, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
174 bn.objnam AS objnam, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
175 srm.date_info AS minuend, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
176 srs.date_info AS subtrahend, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
177 sdcl.height AS height, |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
178 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines |
3010
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
179 FROM caching.sounding_differences sd |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
180 JOIN caching.sounding_differences_contour_lines sdcl |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
181 ON sd.id = sdcl.sounding_differences_id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
182 JOIN waterway.sounding_results srm |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
183 ON sd.minuend = srm.id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
184 JOIN waterway.sounding_results srs |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
185 ON sd.subtrahend = srs.id |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
186 JOIN waterway.bottlenecks bn |
293bdd05ffcd
Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents:
3009
diff
changeset
|
187 ON srm.bottleneck_id = bn.id; |