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