Mercurial > gemma

annotate schema/geoserver_views.sql @ 3480:965b2fbb1890

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Colorize gauge symbol based on availability of measurements
author Tom Gottfried <tom@intevation.de>
date Mon, 27 May 2019 15:45:55 +0200
parents 096968d5628f
children ef4803f87631
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
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,
3456
ca395be62023 Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents: 3454
diff changeset
104 g.gm_waterlevel,
3469
096968d5628f Added latest sounding result date to bottlenecks_geoserver view.
Sascha Wilde <wilde@intevation.de>
parents: 3456
diff changeset
105 srl.date_max,
3456
ca395be62023 Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents: 3454
diff changeset
106 g.forecast_accuracy_3d,
ca395be62023 Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents: 3454
diff changeset
107 g.forecast_accuracy_1d
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 FROM waterway.bottlenecks b
3456
ca395be62023 Add reference gauge forecast accuracy to bottlenecks view
Tom Gottfried <tom@intevation.de>
parents: 3454
diff changeset
109 LEFT JOIN waterway.gauges_geoserver g
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
110 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
111 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
112 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
113 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
114 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
115
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116 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
117 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
118 id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119 name,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120 (stretch).lower::varchar as lower,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121 (stretch).upper::varchar as upper,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122 area::Geometry(MULTIPOLYGON, 4326),
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
123 objnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
124 nobjnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
125 date_info,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
126 source_organization,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
127 (SELECT string_agg(country_code, ', ')
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
128 FROM waterway.stretch_countries
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 WHERE stretches_id = id) AS countries,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
130 staging_done
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
131 FROM waterway.stretches;
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
132
3307
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
133 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
134 SELECT
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
135 id,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
136 name,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
137 (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
138 (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
139 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
140 objnam,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
141 nobjnam,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
142 date_info,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
143 source_organization,
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
144 staging_done
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
145 FROM waterway.sections;
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
146
3008
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
147 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
148 SELECT bottleneck_id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
149 date_info,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
150 height,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
151 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
152 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
153 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
154
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
155 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
156 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
157 objnam AS name,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
158 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
159 (lower(stretch)).hectometre AS from,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160 (upper(stretch)).hectometre AS to,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
161 sr.current::text,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
162 responsible_country
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
163 FROM waterway.bottlenecks bn LEFT JOIN (
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
164 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
165 FROM waterway.sounding_results
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
166 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
167 ORDER BY objnam;
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
168
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
169 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
170 SELECT
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
171 sd.id AS id,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
172 bn.objnam AS objnam,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
173 srm.date_info AS minuend,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
174 srs.date_info AS subtrahend,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
175 sdcl.height AS height,
f394e828a6d2 Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
176 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
3010
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
177 FROM caching.sounding_differences sd
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
178 JOIN caching.sounding_differences_contour_lines sdcl
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
179 ON sd.id = sdcl.sounding_differences_id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
180 JOIN waterway.sounding_results srm
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
181 ON sd.minuend = srm.id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
182 JOIN waterway.sounding_results srs
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
183 ON sd.subtrahend = srs.id
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
184 JOIN waterway.bottlenecks bn
293bdd05ffcd Remove unnecessary indentation
Tom Gottfried <tom@intevation.de>
parents: 3009
diff changeset
185 ON srm.bottleneck_id = bn.id;