Mercurial > gemma
view schema/default_sysconfig.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
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;