Mercurial > gemma

comparison schema/default_sysconfig.sql @ 5092:8dc27fc1d05c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add temporal validity attributes and WMS-T config to bottlenecks layer Attributes related to data availability and quality still refer to the current situation.
author Tom Gottfried <tom@intevation.de>
date Tue, 24 Mar 2020 12:32:42 +0100
parents cf25b23e3eec
children f11b9b50fcc9
comparison
equal deleted inserted replaced
5091:1154b73328ec 5092:8dc27fc1d05c
216 areas, 216 areas,
217 surtyp 217 surtyp
218 FROM waterway.sounding_results_iso_areas ia 218 FROM waterway.sounding_results_iso_areas ia
219 JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id 219 JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id
220 $$), 220 $$),
221 ('waterway', 'bottlenecks_geoserver', 4326, 'id', $$ 221 ('waterway', 'bottleneck_overview', 4326, NULL, $$
222 SELECT
223 objnam AS name,
224 ST_Centroid(area) AS point,
225 (lower(stretch)).hectometre AS from,
226 (upper(stretch)).hectometre AS to,
227 sr.current::text,
228 responsible_country
229 FROM waterway.bottlenecks bn LEFT JOIN (
230 SELECT bottleneck_id, max(date_info) AS current
231 FROM waterway.sounding_results
232 GROUP BY bottleneck_id) sr
233 ON sr.bottleneck_id = bn.bottleneck_id
234 WHERE bn.validity @> current_timestamp
235 ORDER BY objnam
236 $$),
237 ('waterway', 'sounding_differences', 4326, NULL, $$
238 SELECT
239 sd.id AS id,
240 bn.objnam AS objnam,
241 srm.date_info AS minuend,
242 srs.date_info AS subtrahend,
243 sdia.height AS height,
244 sdia.areas AS areas
245 FROM caching.sounding_differences sd
246 JOIN caching.sounding_differences_iso_areas sdia
247 ON sd.id = sdia.sounding_differences_id
248 JOIN waterway.sounding_results srm
249 ON sd.minuend = srm.id
250 JOIN waterway.sounding_results srs
251 ON sd.subtrahend = srs.id
252 JOIN waterway.bottlenecks bn
253 ON srm.bottleneck_id = bn.bottleneck_id
254 AND srm.date_info::timestamptz <@ bn.validity
255 $$);
256
257
258 -- GeoServer SQL views with time support
259 INSERT INTO sys_admin.published_services (
260 schema, name, srid, key_column,
261 wmst_attribute, wmst_end_attribute,
262 view_def
263 ) VALUES
264 ('waterway', 'bottlenecks_geoserver', 4326, 'id',
265 'valid_from', 'valid_to', $$
222 SELECT 266 SELECT
223 b.id, 267 b.id,
268 lower(b.validity) AS valid_from,
269 COALESCE(upper(b.validity), current_timestamp) AS valid_to,
224 b.bottleneck_id, 270 b.bottleneck_id,
225 b.objnam, 271 b.objnam,
226 b.nobjnm, 272 b.nobjnm,
227 b.area, 273 b.area,
228 b.rb, 274 b.rb,
257 bottleneck_id, max(date_info) AS date_max 303 bottleneck_id, max(date_info) AS date_max
258 FROM waterway.sounding_results 304 FROM waterway.sounding_results
259 GROUP BY bottleneck_id 305 GROUP BY bottleneck_id
260 ORDER BY bottleneck_id DESC) AS srl 306 ORDER BY bottleneck_id DESC) AS srl
261 ON b.bottleneck_id = srl.bottleneck_id 307 ON b.bottleneck_id = srl.bottleneck_id
262 WHERE b.validity @> current_timestamp 308 $$),
263 $$),
264 ('waterway', 'bottleneck_overview', 4326, NULL, $$
265 SELECT
266 objnam AS name,
267 ST_Centroid(area) AS point,
268 (lower(stretch)).hectometre AS from,
269 (upper(stretch)).hectometre AS to,
270 sr.current::text,
271 responsible_country
272 FROM waterway.bottlenecks bn LEFT JOIN (
273 SELECT bottleneck_id, max(date_info) AS current
274 FROM waterway.sounding_results
275 GROUP BY bottleneck_id) sr
276 ON sr.bottleneck_id = bn.bottleneck_id
277 WHERE bn.validity @> current_timestamp
278 ORDER BY objnam
279 $$),
280 ('waterway', 'sounding_differences', 4326, NULL, $$
281 SELECT
282 sd.id AS id,
283 bn.objnam AS objnam,
284 srm.date_info AS minuend,
285 srs.date_info AS subtrahend,
286 sdia.height AS height,
287 sdia.areas AS areas
288 FROM caching.sounding_differences sd
289 JOIN caching.sounding_differences_iso_areas sdia
290 ON sd.id = sdia.sounding_differences_id
291 JOIN waterway.sounding_results srm
292 ON sd.minuend = srm.id
293 JOIN waterway.sounding_results srs
294 ON sd.subtrahend = srs.id
295 JOIN waterway.bottlenecks bn
296 ON srm.bottleneck_id = bn.bottleneck_id
297 AND srm.date_info::timestamptz <@ bn.validity
298 $$);
299
300
301 -- GeoServer SQL views with time support
302 INSERT INTO sys_admin.published_services (
303 schema, name, srid, key_column,
304 wmst_attribute, wmst_end_attribute,
305 view_def
306 ) VALUES
307 ('waterway', 'waterway_axis', 4326, 'id', 309 ('waterway', 'waterway_axis', 4326, 'id',
308 'valid_from', 'valid_to', $$ 310 'valid_from', 'valid_to', $$
309 SELECT id, 311 SELECT id,
310 lower(validity) AS valid_from, 312 lower(validity) AS valid_from,
311 COALESCE(upper(validity), current_timestamp) AS valid_to, 313 COALESCE(upper(validity), current_timestamp) AS valid_to,