Mercurial > gemma
view schema/updates/1421/01.fairway-marks_wms-t.sql @ 5736:55892008ec96 default tip
Fixed a bunch of corner cases in WG import.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Wed, 29 May 2024 19:02:42 +0200 |
parents | bb2123358bd8 |
children |
line wrap: on
line source
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';