Mercurial > gemma

comparison schema/updates/1300/02.views_to_geoservers.sql @ 4617:ae840f9eb4c8 geoserver_sql_views

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Advance schema version number Do a bigger step to allow more schema changes in default branch before we have to move here (branch geoserver_sql_views) again.
author Tom Gottfried <tom@intevation.de>
date Mon, 16 Sep 2019 16:56:11 +0200
parents schema/updates/1113/02.views_to_geoservers.sql@b605e91f08f0
children 0f2c3cb139cc
comparison
equal deleted inserted replaced
4616:b605e91f08f0 4617:ae840f9eb4c8
1 -- Copied from ../../default_sysconfig.sql
2 -- plus conflict resolution to achieve updates
3
4 CREATE TEMP TABLE base_views (name, def) AS VALUES (
5 'gauges_base_view', $$
6 SELECT
7 g.location,
8 isrs_asText(g.location) AS isrs_code,
9 g.objname,
10 g.geom,
11 g.applicability_from_km,
12 g.applicability_to_km,
13 g.validity,
14 g.zero_point,
15 g.geodref,
16 g.date_info,
17 g.source_organization,
18 g.erased,
19 r.rwls AS reference_water_levels,
20 wl.measure_date AS gm_measuredate,
21 wl.water_level AS gm_waterlevel,
22 wl_14d.n AS gm_n_14d,
23 fca.forecast_accuracy_3d,
24 fca.forecast_accuracy_1d
25 FROM waterway.gauges g
26 LEFT JOIN (SELECT location, validity,
27 json_strip_nulls(json_object_agg(
28 coalesce(depth_reference, 'empty'), value)) AS rwls
29 FROM waterway.gauges_reference_water_levels
30 GROUP BY location, validity) AS r
31 USING (location, validity)
32 LEFT JOIN (SELECT DISTINCT ON (location)
33 location,
34 measure_date,
35 water_level
36 FROM waterway.gauge_measurements
37 ORDER BY location, measure_date DESC) AS wl
38 USING (location)
39 LEFT JOIN (SELECT location, count(water_level) AS n
40 FROM waterway.gauge_measurements
41 -- consider all measurements within 14 days plus a tolerance
42 WHERE measure_date
43 >= current_timestamp - '14 days 00:15'::interval
44 GROUP BY location) AS wl_14d
45 USING (location)
46 LEFT JOIN (SELECT location,
47 max(acc) FILTER (WHERE
48 measure_date <= current_timestamp + '1 day'::interval)
49 AS forecast_accuracy_1d,
50 max(acc) AS forecast_accuracy_3d
51 FROM waterway.gauge_predictions,
52 GREATEST(water_level - lower(conf_interval),
53 upper(conf_interval) - water_level) AS acc (acc)
54 WHERE measure_date
55 BETWEEN current_timestamp
56 AND current_timestamp + '3 days'::interval
57 GROUP BY location) AS fca
58 USING (location)
59 $$);
60
61 INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
62 ('waterway', 'gauges_geoserver', 4326, $$
63 SELECT
64 isrs_code,
65 objname,
66 geom,
67 applicability_from_km,
68 applicability_to_km,
69 zero_point,
70 geodref,
71 date_info,
72 source_organization,
73 reference_water_levels,
74 gm_measuredate,
75 gm_waterlevel,
76 gm_n_14d,
77 forecast_accuracy_3d,
78 forecast_accuracy_1d
79 FROM (
80 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
81 ) AS gauges_base_view
82 WHERE NOT erased
83 $$),
84 ('waterway', 'sections_geoserver', 4326, $$
85 SELECT
86 s.id,
87 s.name,
88 (s.section).lower::varchar as lower,
89 (s.section).upper::varchar as upper,
90 s.area::Geometry(MULTIPOLYGON, 4326),
91 s.objnam,
92 s.nobjnam,
93 s.date_info,
94 s.source_organization,
95 s.staging_done,
96 min(g.gm_measuredate) AS gm_measuredate,
97 min(g.gm_n_14d) AS gm_n_14d,
98 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
99 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
100 FROM waterway.sections s
101 LEFT JOIN (
102 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
103 WHERE NOT erased) AS g
104 ON g.location <@ s.section
105 GROUP BY s.id
106 $$),
107 ('waterway', 'stretches_geoserver', 4326, $$
108 SELECT
109 s.id,
110 s.name,
111 (s.stretch).lower::varchar as lower,
112 (s.stretch).upper::varchar as upper,
113 s.area::Geometry(MULTIPOLYGON, 4326),
114 s.objnam,
115 s.nobjnam,
116 s.date_info,
117 s.source_organization,
118 (SELECT string_agg(country_code, ', ')
119 FROM waterway.stretch_countries
120 WHERE stretches_id = s.id) AS countries,
121 s.staging_done,
122 min(g.gm_measuredate) AS gm_measuredate,
123 min(g.gm_n_14d) AS gm_n_14d,
124 max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
125 max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
126 FROM waterway.stretches s
127 LEFT JOIN (
128 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
129 WHERE NOT erased) AS g
130 ON g.location <@ s.stretch
131 GROUP BY s.id
132 $$),
133 ('waterway', 'distance_marks_ashore_geoserver', 4326, $$
134 SELECT id,
135 country,
136 geom::Geometry(POINT, 4326),
137 related_enc,
138 hectom,
139 catdis,
140 position_code
141 FROM waterway.distance_marks
142 $$),
143 ('waterway', 'distance_marks_geoserver', 4326, $$
144 SELECT
145 isrs_asText(location_code) AS location,
146 geom::Geometry(POINT, 4326),
147 related_enc,
148 (location_code).hectometre
149 FROM waterway.distance_marks_virtual
150 $$),
151 ('waterway', 'sounding_results_contour_lines_geoserver', 4326, $$
152 SELECT bottleneck_id,
153 date_info,
154 height,
155 CAST(lines AS geometry(multilinestring, 4326)) AS lines
156 FROM waterway.sounding_results_contour_lines cl
157 JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id
158 $$),
159 ('waterway', 'bottlenecks_geoserver', 4326, $$
160 SELECT
161 b.id,
162 b.bottleneck_id,
163 b.objnam,
164 b.nobjnm,
165 b.area,
166 b.rb,
167 b.lb,
168 b.responsible_country,
169 b.revisiting_time,
170 b.limiting,
171 b.date_info,
172 b.source_organization,
173 g.objname AS gauge_objname,
174 g.reference_water_levels,
175 fal.date_info AS fa_date_info,
176 fal.critical AS fa_critical,
177 g.gm_measuredate,
178 g.gm_waterlevel,
179 g.gm_n_14d,
180 srl.date_max,
181 g.forecast_accuracy_3d,
182 g.forecast_accuracy_1d
183 FROM waterway.bottlenecks b
184 LEFT JOIN (
185 $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
186 ) AS g
187 ON b.gauge_location = g.location
188 AND g.validity @> current_timestamp
189 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
190 bottleneck_id, date_info, critical
191 FROM waterway.fairway_availability
192 ORDER BY bottleneck_id, date_info DESC) AS fal
193 ON b.bottleneck_id = fal.bottleneck_id
194 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
195 bottleneck_id, max(date_info) AS date_max
196 FROM waterway.sounding_results
197 GROUP BY bottleneck_id
198 ORDER BY bottleneck_id DESC) AS srl
199 ON b.bottleneck_id = srl.bottleneck_id
200 WHERE b.validity @> current_timestamp
201 $$),
202 ('waterway', 'bottleneck_overview', 4326, $$
203 SELECT
204 objnam AS name,
205 ST_Centroid(area)::Geometry(POINT, 4326) AS point,
206 (lower(stretch)).hectometre AS from,
207 (upper(stretch)).hectometre AS to,
208 sr.current::text,
209 responsible_country
210 FROM waterway.bottlenecks bn LEFT JOIN (
211 SELECT bottleneck_id, max(date_info) AS current
212 FROM waterway.sounding_results
213 GROUP BY bottleneck_id) sr
214 ON sr.bottleneck_id = bn.bottleneck_id
215 WHERE bn.validity @> current_timestamp
216 ORDER BY objnam
217 $$),
218 ('waterway', 'sounding_differences', 4326, $$
219 SELECT
220 sd.id AS id,
221 bn.objnam AS objnam,
222 srm.date_info AS minuend,
223 srs.date_info AS subtrahend,
224 sdcl.height AS height,
225 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
226 FROM caching.sounding_differences sd
227 JOIN caching.sounding_differences_contour_lines sdcl
228 ON sd.id = sdcl.sounding_differences_id
229 JOIN waterway.sounding_results srm
230 ON sd.minuend = srm.id
231 JOIN waterway.sounding_results srs
232 ON sd.subtrahend = srs.id
233 JOIN waterway.bottlenecks bn
234 ON srm.bottleneck_id = bn.bottleneck_id
235 AND srm.date_info::timestamptz <@ bn.validity
236 $$)
237 ON CONFLICT (schema, name) DO UPDATE SET
238 srid = EXCLUDED.srid,
239 view_def = EXCLUDED.view_def;
240
241 DROP VIEW
242 waterway.gauges_base_view,
243 waterway.gauges_geoserver,
244 waterway.distance_marks_geoserver,
245 waterway.distance_marks_ashore_geoserver,
246 waterway.bottlenecks_geoserver,
247 waterway.stretches_geoserver,
248 waterway.sections_geoserver,
249 waterway.sounding_results_contour_lines_geoserver,
250 waterway.bottleneck_overview,
251 waterway.sounding_differences;