Mercurial > gemma
view schema/default_sysconfig.sql @ 5654:358130887c72
Added tag v5.7.1 for changeset 9b3779f649c2
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Tue, 08 Aug 2023 15:07:00 +0200 |
parents | 67ffa7517888 |
children |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019, 2020 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Sascha Wilde <wilde@intevation.de> -- * Bernhard Reiter <bernhard.reiter@intevation.de> -- * Fadi Abbund <fadi.abbud@intevation.de> -- * Tom Gottfried <tom@intevation.de> BEGIN; -- -- Default system configurations for the GEMMA database -- -- -- Tables and views published via GeoServer -- -- temporary table to store portions of SQL shared by multiple SQL views CREATE TEMP TABLE base_views (name, def) AS VALUES ( 'gauges_base_view', $$ SELECT g.location, isrs_asText(g.location) AS isrs_code, g.objname, g.geom, g.applicability_from_km, g.applicability_to_km, g.validity, g.zero_point, g.geodref, g.date_info, g.source_organization, g.erased, r.rwls AS reference_water_levels, wl.measure_date AS gm_measuredate, wl.water_level AS gm_waterlevel, wl.n AS gm_n_14d, fca.forecast_accuracy_3d, fca.forecast_accuracy_1d FROM waterway.gauges g LEFT JOIN (SELECT location, validity, json_strip_nulls(json_object_agg( coalesce(depth_reference, 'empty'), value)) AS rwls FROM waterway.gauges_reference_water_levels GROUP BY location, validity) AS r USING (location, validity) LEFT JOIN (SELECT DISTINCT ON (location) location, date_issue, measure_date, water_level, count(*) OVER (PARTITION BY location) AS n FROM waterway.gauge_measurements -- consider all measurements within 14 days plus a tolerance WHERE measure_date >= current_timestamp - '14 days 00:15'::interval ORDER BY location, measure_date DESC) AS wl USING (location) LEFT JOIN (SELECT DISTINCT ON (location) location, date_issue, max(acc) FILTER (WHERE measure_date <= current_timestamp + '1 day'::interval) OVER loc_date_issue AS forecast_accuracy_1d, max(acc) OVER loc_date_issue AS forecast_accuracy_3d FROM (SELECT location, date_issue, measure_date, GREATEST(water_level - lower(conf_interval), upper(conf_interval) - water_level) AS acc FROM waterway.gauge_predictions -- consider predictions made within last 14 days ... WHERE date_issue >= current_timestamp - '14 days 00:15'::interval -- ... for the next three days from now AND measure_date BETWEEN current_timestamp AND current_timestamp + '3 days'::interval) AS acc WINDOW loc_date_issue AS (PARTITION BY location, date_issue) ORDER BY location, date_issue DESC) AS fca -- Show only forecasts issued with latest measurements or later ON fca.location = g.location AND fca.date_issue >= wl.date_issue $$), ( 'fairway_marks_tmpl', $$ SELECT id, lower(validity) AS valid_from, COALESCE(upper(validity), current_timestamp) AS valid_to, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, %s FROM waterway.fairway_marks_%s $$); -- Directly accessed tables INSERT INTO sys_admin.published_services (schema, name) VALUES ('waterway', 'waterway_area'), ('waterway', 'waterway_profiles'); -- GeoServer SQL views without time support INSERT INTO sys_admin.published_services ( schema, name, srid, key_column, view_def ) VALUES ('waterway', 'gauges_geoserver', 4326, 'isrs_code', $$ SELECT isrs_code, objname, geom, applicability_from_km, applicability_to_km, zero_point, geodref, date_info, source_organization, reference_water_levels, gm_measuredate, gm_waterlevel, gm_n_14d, forecast_accuracy_3d, forecast_accuracy_1d FROM ( $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ ) AS gauges_base_view WHERE NOT erased $$), ('waterway', 'sections_geoserver', 4326, 'id', $$ SELECT s.id, s.name, (s.section).lower::varchar as lower, (s.section).upper::varchar as upper, s.area, s.objnam, s.nobjnam, s.country, s.date_info, s.source_organization, s.staging_done, min(g.gm_measuredate) AS gm_measuredate, min(g.gm_n_14d) AS gm_n_14d, max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, max(g.forecast_accuracy_1d) AS forecast_accuracy_1d FROM waterway.sections s LEFT JOIN ( $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ WHERE NOT erased) AS g ON g.location <@ s.section GROUP BY s.id $$), ('waterway', 'stretches_geoserver', 4326, 'id', $$ SELECT s.id, s.name, (s.stretch).lower::varchar as lower, (s.stretch).upper::varchar as upper, s.area, s.objnam, s.nobjnam, s.date_info, s.source_organization, (SELECT string_agg(country, ', ') FROM users.stretch_countries WHERE stretch_id = s.id) AS countries, s.staging_done, min(g.gm_measuredate) AS gm_measuredate, min(g.gm_n_14d) AS gm_n_14d, max(g.forecast_accuracy_3d) AS forecast_accuracy_3d, max(g.forecast_accuracy_1d) AS forecast_accuracy_1d FROM users.stretches s LEFT JOIN ( $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ WHERE NOT erased) AS g ON g.location <@ s.stretch GROUP BY s.id $$), ('waterway', 'distance_marks_ashore_geoserver', 4326, 'id', $$ SELECT id, country, geom, related_enc, hectom, catdis, position_code FROM waterway.distance_marks $$), ('waterway', 'distance_marks_geoserver', 4326, 'location', $$ SELECT isrs_asText(location_code) AS location, geom, related_enc, (location_code).hectometre FROM waterway.distance_marks_virtual $$), ('waterway', 'sounding_results_areas_geoserver', 4326, NULL, $$ SELECT bottleneck_id, to_char(date_info, 'YYYY-MM-DD') AS date_info, height, areas, surtyp, zpg_exception FROM waterway.sounding_results_iso_areas ia JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id $$), ('waterway', 'sounding_results_marking_points_geoserver', 4326, NULL, $$ SELECT bottleneck_id, to_char(date_info, 'YYYY-MM-DD') AS date_info, height, points::geometry(MULTIPOINTZ, 4326), surtyp, zpg_exception FROM waterway.sounding_results_marking_points mp JOIN waterway.sounding_results sr ON sr.id = mp.sounding_result_id $$), ('waterway', 'bottleneck_overview', 4326, NULL, $$ SELECT objnam AS name, bn.bottleneck_id, ST_Centroid(area) AS point, (lower(stretch)).hectometre AS from, (upper(stretch)).hectometre AS to, sr.current::text, responsible_country FROM waterway.bottlenecks bn LEFT JOIN ( SELECT bottleneck_id, max(date_info) AS current FROM waterway.sounding_results GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id WHERE bn.validity @> current_timestamp ORDER BY objnam $$), ('waterway', 'sounding_differences', 4326, NULL, $$ SELECT sd.id AS id, bn.objnam AS objnam, srm.date_info AS minuend, srs.date_info AS subtrahend, sdia.height AS height, sdia.areas AS areas FROM caching.sounding_differences sd JOIN caching.sounding_differences_iso_areas sdia ON sd.id = sdia.sounding_differences_id JOIN waterway.sounding_results srm ON sd.minuend = srm.id JOIN waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN waterway.bottlenecks bn ON srm.bottleneck_id = bn.bottleneck_id AND srm.date_info::timestamptz <@ bn.validity $$); -- GeoServer SQL views with time support INSERT INTO sys_admin.published_services ( schema, name, srid, key_column, wmst_attribute, wmst_end_attribute, view_def ) VALUES ('waterway', 'bottlenecks_geoserver', 4326, 'id', 'valid_from', 'valid_to', $$ SELECT b.id, lower(b.validity) AS valid_from, COALESCE(upper(b.validity), current_timestamp) AS valid_to, b.bottleneck_id, b.objnam, b.nobjnm, b.area, b.rb, b.lb, b.responsible_country, b.revisiting_time, b.limiting, b.date_info, b.source_organization, g.objname AS gauge_objname, g.reference_water_levels, fal.date_info AS fa_date_info, fal.critical AS fa_critical, g.gm_measuredate, g.gm_waterlevel, g.gm_n_14d, srl.date_max, g.forecast_accuracy_3d, g.forecast_accuracy_1d FROM waterway.bottlenecks b LEFT JOIN ( $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$ ) AS g ON b.gauge_location = g.location AND g.validity @> current_timestamp LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical FROM waterway.fairway_availability ORDER BY bottleneck_id, date_info DESC) AS fal ON b.bottleneck_id = fal.bottleneck_id LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max FROM waterway.sounding_results GROUP BY bottleneck_id ORDER BY bottleneck_id DESC) AS srl ON b.bottleneck_id = srl.bottleneck_id $$), ('waterway', 'fairway_dimensions', 4326, 'id', 'valid_from', 'valid_to', $$ SELECT id, lower(validity) AS valid_from, COALESCE(upper(validity), current_timestamp) AS valid_to, area, level_of_service, min_width, max_width, min_depth, date_info, source_organization, staging_done FROM waterway.fairway_dimensions $$), ('waterway', 'waterway_axis', 4326, 'id', 'valid_from', 'valid_to', $$ SELECT id, lower(validity) AS valid_from, COALESCE(upper(validity), current_timestamp) AS valid_to, wtwaxs, objnam, nobjnam FROM waterway.waterway_axis $$), ('waterway', 'fairway_marks_bcnlat_hydro', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'colour, colpat, condtn, bcnshp, catlam', 'bcnlat_hydro')), ('waterway', 'fairway_marks_bcnlat_ienc', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), $$colour, colpat, condtn, bcnshp, catlam, string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$, $$bcnlat_ienc LEFT JOIN waterway.fairway_marks_bcnlat_dirimps ON id = fm_bcnlat_id GROUP BY id$$)), ('waterway', 'fairway_marks_boycar', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'colour, colpat, conrad, marsys, boyshp, catcam', 'boycar')), ('waterway', 'fairway_marks_boylat_hydro', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_hydro')), ('waterway', 'fairway_marks_boylat_ienc', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_ienc')), ('waterway', 'fairway_marks_boysaw', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'colour, colpat, conrad, marsys, boyshp', 'boysaw')), ('waterway', 'fairway_marks_boyspp', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'colour, colpat, conrad, marsys, boyshp, catspm', 'boyspp')), ('waterway', 'fairway_marks_daymar_hydro', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'colour, colpat, condtn, topshp', 'daymar_hydro')), ('waterway', 'fairway_marks_daymar_ienc', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), $$colour, colpat, condtn, topshp, orient, string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$, $$daymar_ienc LEFT JOIN waterway.fairway_marks_daymar_dirimps ON id = fm_daymar_id GROUP BY id$$)), ('waterway', 'fairway_marks_lights', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), $$colour, condtn, orient, catlit, exclit, litchr, litvis, mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status$$, 'lights')), ('waterway', 'fairway_marks_rtpbcn', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'condtn, siggrp, catrtb, radwal', 'rtpbcn')), ('waterway', 'fairway_marks_topmar', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), 'colour, colpat, condtn, topshp', 'topmar')), ('waterway', 'fairway_marks_notmrk', 4326, 'id', 'valid_from', 'valid_to', format( (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), $$condtn, marsys, orient, status, addmrk, catnmk, disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw, string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$, $$notmrk LEFT JOIN waterway.fairway_marks_notmrk_dirimps ON id = fm_notmrk_id GROUP BY id$$)); -- -- group layers -- INSERT INTO sys_admin.layer_groups VALUES ('fairway_marks'), ('sounding_results'); INSERT INTO sys_admin.grouped_layers VALUES ('fairway_marks', 'waterway', 'fairway_marks_boylat_hydro', 0), ('fairway_marks', 'waterway', 'fairway_marks_boylat_ienc', 0), ('fairway_marks', 'waterway', 'fairway_marks_boycar', 1), ('fairway_marks', 'waterway', 'fairway_marks_bcnlat_hydro', 2), ('fairway_marks', 'waterway', 'fairway_marks_bcnlat_ienc', 2), ('fairway_marks', 'waterway', 'fairway_marks_boysaw', 3), ('fairway_marks', 'waterway', 'fairway_marks_boyspp', 4), ('fairway_marks', 'waterway', 'fairway_marks_daymar_hydro', 5), ('fairway_marks', 'waterway', 'fairway_marks_daymar_ienc', 5), ('fairway_marks', 'waterway', 'fairway_marks_lights', 6), ('fairway_marks', 'waterway', 'fairway_marks_rtpbcn', 7), ('fairway_marks', 'waterway', 'fairway_marks_topmar', 8), ('fairway_marks', 'waterway', 'fairway_marks_notmrk', 9), ('sounding_results', 'waterway', 'sounding_results_areas_geoserver', 0), ('sounding_results', 'waterway', 'sounding_results_marking_points_geoserver', 1); -- -- Settings -- INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_url', 'https://service.d4d-portal.info/wms/'); INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_params', '{"LAYERS": "d4d", "VERSION": "1.1.1", "TILED": true}'); INSERT INTO sys_admin.system_config VALUES ('bn_revtime_multiplier', 1.5); INSERT INTO sys_admin.system_config VALUES ('gm_min_values_14d', 1224); INSERT INTO sys_admin.system_config VALUES ('gm_latest_hours', 24); INSERT INTO sys_admin.system_config VALUES ('gm_forecast_offset_24h', 15); INSERT INTO sys_admin.system_config VALUES ('gm_forecast_offset_72h', 15); INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_24h', -12.5); INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_72h', -12.5); INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks', '-2.5:#5A3300,-2,-1.5,-1,-0.5,0:#D8B050,0.5:#FF0000,1,1.5,2:#FF9999,2.5:#A6B8FA,3,3.5,4,4.5,5,6:#0101FF,7:#88DD55,8,9,10,11,12:#146E33'); INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks_compare', '-2:#06b100,-1.8,-1.6,-1.4,-1.2,-1:#1cc68e,-0.8,-0.6,-0.4,-0.2,0:#c2c2c2,0.2,0.4,0.6,0.8,1:#fff01a,1.2,1.4,1.6,1.8,2:#f80012'); INSERT INTO sys_admin.system_config VALUES ('bottlenecks_stroke','#fa28ff'); INSERT INTO sys_admin.system_config VALUES ('bottlenecks_fill','#ff25c424'); INSERT INTO sys_admin.system_config VALUES ('stretches_stroke','#fac800cc'); INSERT INTO sys_admin.system_config VALUES ('stretches_fill','#fac80a4d'); INSERT INTO sys_admin.system_config VALUES ('sections_stroke','#ff960acc'); INSERT INTO sys_admin.system_config VALUES ('sections_fill','#ff96004d'); INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos1_stroke','#0000ffcc'); INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos1_fill','#f0e60033'); INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos2_stroke','#0000ffe6'); INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos2_fill','#f0e6001a'); INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos3_stroke','#0000ff'); INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos3_fill','#ffffff66'); INSERT INTO sys_admin.system_config VALUES ('waterwayprofiles_stroke','#0000ff80'); INSERT INTO sys_admin.system_config VALUES ('distance_marks_fill', '#ff99994d'); INSERT INTO sys_admin.system_config VALUES ('distance_marks_stroke', '#6666ff'); INSERT INTO sys_admin.system_config VALUES ('distance_marks_ashore_fill', '#8888ff1a'); INSERT INTO sys_admin.system_config VALUES ('distance_marks_ashore_stroke', '#3333ff'); INSERT INTO sys_admin.system_config VALUES ('waterway_axis_stroke', '#0000ff'); INSERT INTO sys_admin.system_config VALUES ('waterway_area_stroke', '#006600'); COMMIT;