Mercurial > gemma
changeset 4998:bb2123358bd8 wmst-config
Configure fairway marks layers with time support
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 09 Mar 2020 12:19:06 +0100 |
| parents | 52c88c7c00ec |
| children | bfaf7621d0ad |
| files | schema/default_sysconfig.sql schema/updates/1421/01.fairway-marks_wms-t.sql schema/version.sql |
| diffstat | 3 files changed, 210 insertions(+), 20 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/default_sysconfig.sql Fri Mar 06 18:51:00 2020 +0100 +++ b/schema/default_sysconfig.sql Mon Mar 09 12:19:06 2020 +0100 @@ -86,30 +86,40 @@ 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_axis'), + ('waterway', 'waterway_area'), + ('waterway', 'waterway_profiles'), + ('waterway', 'fairway_dimensions'); + +-- GeoServer SQL views without time support INSERT INTO sys_admin.published_services ( schema, name, srid, key_column, view_def ) VALUES - -- Directly accessed tables - ('waterway', 'waterway_axis', NULL, NULL, NULL), - ('waterway', 'waterway_area', NULL, NULL, NULL), - ('waterway', 'waterway_profiles', NULL, NULL, NULL), - ('waterway', 'fairway_dimensions', NULL, NULL, NULL), - ('waterway', 'fairway_marks_bcnlat_hydro', NULL, NULL, NULL), - ('waterway', 'fairway_marks_bcnlat_ienc', NULL, NULL, NULL), - ('waterway', 'fairway_marks_boycar', NULL, NULL, NULL), - ('waterway', 'fairway_marks_boylat_hydro', NULL, NULL, NULL), - ('waterway', 'fairway_marks_boylat_ienc', NULL, NULL, NULL), - ('waterway', 'fairway_marks_boysaw', NULL, NULL, NULL), - ('waterway', 'fairway_marks_boyspp', NULL, NULL, NULL), - ('waterway', 'fairway_marks_daymar_hydro', NULL, NULL, NULL), - ('waterway', 'fairway_marks_daymar_ienc', NULL, NULL, NULL), - ('waterway', 'fairway_marks_lights', NULL, NULL, NULL), - ('waterway', 'fairway_marks_rtpbcn', NULL, NULL, NULL), - ('waterway', 'fairway_marks_topmar', NULL, NULL, NULL), - ('waterway', 'fairway_marks_notmrk', NULL, NULL, NULL), - -- GeoServer SQL views ('waterway', 'gauges_geoserver', 4326, 'isrs_code', $$ SELECT isrs_code, @@ -288,6 +298,78 @@ 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', '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 --
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1421/01.fairway-marks_wms-t.sql Mon Mar 09 12:19:06 2020 +0100 @@ -0,0 +1,108 @@ +CREATE TEMP TABLE base_views (name, def) AS VALUES ( + '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 + $$); + +-- Settings common to all fairway marks layers +UPDATE sys_admin.published_services SET + srid = 4326, + key_column = 'id', + wmst_attribute = 'valid_from', + wmst_end_attribute = 'valid_to' + WHERE schema = 'waterway' AND name LIKE 'fairway_marks_%'; + +-- Add SQL view definition for fairway marks layers +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'colour, colpat, conrad, marsys, boyshp, catcam', 'boycar') + WHERE schema = 'waterway' AND name = 'fairway_marks_boycar'; + +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'colour, colpat, conrad, marsys, boyshp', 'boysaw') + WHERE schema = 'waterway' AND name = 'fairway_marks_boysaw'; + +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'colour, colpat, conrad, marsys, boyshp, catspm', 'boyspp') + WHERE schema = 'waterway' AND name = 'fairway_marks_boyspp'; + +UPDATE sys_admin.published_services SET view_def = 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') + WHERE schema = 'waterway' AND name = 'fairway_marks_lights'; + +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'condtn, siggrp, catrtb, radwal', 'rtpbcn') + WHERE schema = 'waterway' AND name = 'fairway_marks_rtpbcn'; + +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'colour, colpat, condtn, topshp', 'topmar') + WHERE schema = 'waterway' AND name = 'fairway_marks_topmar'; + +UPDATE sys_admin.published_services SET view_def = 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$$) + WHERE schema = 'waterway' AND name = 'fairway_marks_notmrk'; + +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'colour, colpat, condtn, bcnshp, catlam', 'bcnlat_hydro') + WHERE schema = 'waterway' AND name = 'fairway_marks_bcnlat_hydro'; + +UPDATE sys_admin.published_services SET view_def = 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$$) + WHERE schema = 'waterway' AND name = 'fairway_marks_bcnlat_ienc'; + +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_hydro') + WHERE schema = 'waterway' AND name = 'fairway_marks_boylat_hydro'; + +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_ienc') + WHERE schema = 'waterway' AND name = 'fairway_marks_boylat_ienc'; + +UPDATE sys_admin.published_services SET view_def = format( + (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'), + 'colour, colpat, condtn, topshp', 'daymar_hydro') + WHERE schema = 'waterway' AND name = 'fairway_marks_daymar_hydro'; + +UPDATE sys_admin.published_services SET view_def = 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$$) + WHERE schema = 'waterway' AND name = 'fairway_marks_daymar_ienc';