Mercurial > gemma

annotate schema/geoserver_views.sql @ 3431:adf06af7a79c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add forecast accuracy to gauges view Forecast accuracy is calculated as the maximum deviation of confidence interval bounds from the prediction within the next four days. The JSON-aggregate of reference water levels is moved into a sub-select to avoid having to add further independent columns to the GROUP BY clause of the main statement.
author Tom Gottfried <tom@intevation.de>
date Thu, 23 May 2019 17:29:34 +0200
parents 3e7a670896bd
children 6e15c3c1e9b7
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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
3361
3e7a670896bd added latest waterlevel measurement to guages_geoserver view
Markus Kottlaender <markus@intevation.de>
parents: 3307
diff changeset
2 WITH
3e7a670896bd added latest waterlevel measurement to guages_geoserver view
Markus Kottlaender <markus@intevation.de>
parents: 3307
diff changeset
3 waterlevel_latest AS (
3e7a670896bd added latest waterlevel measurement to guages_geoserver view
Markus Kottlaender <markus@intevation.de>
parents: 3307
diff changeset
4 SELECT DISTINCT ON (location) location, water_level
3e7a670896bd added latest waterlevel measurement to guages_geoserver view
Markus Kottlaender <markus@intevation.de>
parents: 3307
diff changeset
5 FROM waterway.gauge_measurements
3e7a670896bd added latest waterlevel measurement to guages_geoserver view
Markus Kottlaender <markus@intevation.de>
parents: 3307
diff changeset
6 ORDER BY location, measure_date DESC)
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 g.location,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 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
10 g.objname,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 g.geom,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 g.applicability_from_km,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 g.applicability_to_km,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 g.validity,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 g.zero_point,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 g.geodref,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 g.date_info,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 g.source_organization,
3431
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
19 r.rwls AS reference_water_levels,
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
20 wl.water_level AS gm_waterlevel,
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
21 fca.forecast_accuracy
3009
ddc4d54b8331 Remove unnecessary subselect
Tom Gottfried <tom@intevation.de>
parents: 3008
diff changeset
22 FROM waterway.gauges g
3431
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
23 LEFT JOIN (SELECT location, validity,
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
24 json_strip_nulls(json_object_agg(
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
25 coalesce(depth_reference, 'empty'), value)) AS rwls
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
26 FROM waterway.gauges_reference_water_levels
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
27 GROUP BY location, validity) AS r
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
28 USING (location, validity)
3361
3e7a670896bd added latest waterlevel measurement to guages_geoserver view
Markus Kottlaender <markus@intevation.de>
parents: 3307
diff changeset
29 LEFT JOIN waterlevel_latest wl
3e7a670896bd added latest waterlevel measurement to guages_geoserver view
Markus Kottlaender <markus@intevation.de>
parents: 3307
diff changeset
30 USING (location)
3431
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
31 LEFT JOIN (SELECT location, max(GREATEST(
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
32 water_level - lower(conf_interval),
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
33 upper(conf_interval) - water_level)) AS forecast_accuracy
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
34 FROM waterway.gauge_predictions
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
35 WHERE measure_date
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
36 BETWEEN current_timestamp
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
37 AND current_timestamp + '4 days'::interval
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
38 GROUP BY location) AS fca
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
39 USING (location)
adf06af7a79c Add forecast accuracy to gauges view
Tom Gottfried <tom@intevation.de>
parents: 3361
diff changeset
40 WHERE NOT g.erased;
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 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
43 SELECT location_code,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 isrs_asText(location_code) AS location,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 geom::Geometry(POINT, 4326),
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 related_enc,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 (location_code).hectometre
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 FROM waterway.distance_marks_virtual;
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 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
51 SELECT id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 country,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 geom::Geometry(POINT, 4326),
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 related_enc,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 hectom,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 catdis,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 position_code
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 FROM waterway.distance_marks;
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 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
61 WITH
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 fairway_availability_latest AS (
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 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
64 FROM waterway.fairway_availability
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 ORDER BY bottleneck_id, date_info DESC),
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 waterlevel_latest AS (
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
67 SELECT DISTINCT ON (location) location, water_level
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 FROM waterway.gauge_measurements
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
69 ORDER BY location, measure_date DESC)
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 b.id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 b.bottleneck_id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 b.objnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 b.nobjnm,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 b.stretch,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 b.area,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 b.rb,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 b.lb,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 b.responsible_country,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 b.revisiting_time,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 b.limiting,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 b.date_info,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 b.source_organization,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 g.location AS gauge_isrs_code,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 g.objname AS gauge_objname,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 r.value))
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 AS reference_water_levels,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 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
90 fal.critical AS fa_critical,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 wl.water_level AS gm_waterlevel
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 FROM waterway.bottlenecks b
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 LEFT JOIN waterway.gauges g
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
94 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
95 LEFT JOIN waterway.gauges_reference_water_levels r
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
96 USING (location, validity)
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 LEFT JOIN fairway_availability_latest fal
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 ON b.id = fal.bottleneck_id
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 LEFT JOIN waterlevel_latest wl
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
100 USING (location)
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
101 WHERE NOT g.erased
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
102 GROUP BY b.id, g.location, g.validity,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
103 fal.date_info, fal.critical, wl.water_level;
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 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
106 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 name,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 (stretch).lower::varchar as lower,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 (stretch).upper::varchar as upper,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 area::Geometry(MULTIPOLYGON, 4326),
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 objnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 nobjnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 date_info,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 source_organization,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116 (SELECT string_agg(country_code, ', ')
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
117 FROM waterway.stretch_countries
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
118 WHERE stretches_id = id) AS countries,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119 staging_done
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120 FROM waterway.stretches;
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121
3307
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
122 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
123 SELECT
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
124 id,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
125 name,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
126 (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
127 (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
128 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
129 objnam,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
130 nobjnam,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
131 date_info,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
132 source_organization,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
133 staging_done
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
134 FROM waterway.sections;
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
135
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
136 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
137 SELECT bottleneck_id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
138 date_info,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
139 height,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
140 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
141 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
142 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
143
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
144 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
145 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
146 objnam AS name,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
147 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
148 (lower(stretch)).hectometre AS from,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
149 (upper(stretch)).hectometre AS to,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
150 sr.current::text,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
151 responsible_country
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
152 FROM waterway.bottlenecks bn LEFT JOIN (
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
153 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
154 FROM waterway.sounding_results
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
155 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
156 ORDER BY objnam;
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
157
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
158 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
159 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160 sd.id AS id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
161 bn.objnam AS objnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
162 srm.date_info AS minuend,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
163 srs.date_info AS subtrahend,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
164 sdcl.height AS height,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
165 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
3010
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
166 FROM caching.sounding_differences sd
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
167 JOIN caching.sounding_differences_contour_lines sdcl
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
168 ON sd.id = sdcl.sounding_differences_id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
169 JOIN waterway.sounding_results srm
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
170 ON sd.minuend = srm.id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
171 JOIN waterway.sounding_results srs
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
172 ON sd.subtrahend = srs.id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
173 JOIN waterway.bottlenecks bn
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
174 ON srm.bottleneck_id = bn.id;