Mercurial > gemma
changeset 5003:ec5f333ba264
Merge wmst-config into default
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Wed, 11 Mar 2020 10:58:53 +0100 |
| parents | e513afec49d4 (current diff) b4b913a36d89 (diff) |
| children | 5269fd823ca7 |
| files | |
| diffstat | 7 files changed, 245 insertions(+), 36 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/geoserver/boot.go Wed Mar 11 10:38:33 2020 +0100 +++ b/pkg/geoserver/boot.go Wed Mar 11 10:58:53 2020 +0100 @@ -327,15 +327,20 @@ entries = append(entries, entry) } - /* XXX: Experimental - if table == "sounding_differences" { + if attr := tables[i].WMSTAttribute; attr != nil { di := map[string]interface{}{ "enabled": true, - "attribute": "minuend", - "endAttribute": "subtrahend", + "attribute": *attr, "presentation": "CONTINUOUS_INTERVAL", "units": "ISO8601", "nearestMatchEnabled": false, + "defaultValue": map[string]string{ + "strategy": "FIXED", + "referenceValue": "PT1M/PRESENT", + }, + } + if endAttr := tables[i].WMSTEndAttribute; endAttr != nil { + di["endAttribute"] = *endAttr } entry := map[string]interface{}{ "@key": "time", @@ -343,7 +348,6 @@ } entries = append(entries, entry) } - */ if len(entries) > 0 { ft["metadata"] = map[string]interface{}{
--- a/pkg/models/intservices.go Wed Mar 11 10:38:33 2020 +0100 +++ b/pkg/models/intservices.go Wed Mar 11 10:58:53 2020 +0100 @@ -29,14 +29,16 @@ type ( IntEntry struct { - Schema string `json:"schema"` - Name string `json:"name"` - SQL *string `json:"sql"` - KeyColumn *string `json:"keycolumn"` - SRS *string `json:"srs"` - Style bool `json:"style"` - WMS bool `json:"wms"` - WFS bool `json:"wfs"` + Schema string `json:"schema"` + Name string `json:"name"` + SQL *string `json:"sql"` + KeyColumn *string `json:"keycolumn"` + SRS *string `json:"srs"` + Style bool `json:"style"` + WMS bool `json:"wms"` + WFS bool `json:"wfs"` + WMSTAttribute *string `json:"wmst-attribute"` + WMSTEndAttribute *string `json:"wmst-end-attribute"` } LayerGroup struct { @@ -53,9 +55,17 @@ const ( selectServicesSQL = ` -SELECT schema, name, - view_def, key_column, auth_name || ':' || auth_srid, - style IS NOT NULL, as_wms, as_wfs +SELECT + schema, + name, + view_def, + key_column, + auth_name || ':' || auth_srid, + style IS NOT NULL, + as_wms, + as_wfs, + wmst_attribute, + wmst_end_attribute FROM sys_admin.published_services LEFT JOIN spatial_ref_sys USING (srid) WHERE schema = $1 @@ -181,6 +191,7 @@ &entry.Schema, &entry.Name, &entry.SQL, &entry.KeyColumn, &entry.SRS, &entry.Style, &entry.WMS, &entry.WFS, + &entry.WMSTAttribute, &entry.WMSTEndAttribute, ); err != nil { return err }
--- a/schema/default_sysconfig.sql Wed Mar 11 10:38:33 2020 +0100 +++ b/schema/default_sysconfig.sql Wed Mar 11 10:58:53 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 --
--- a/schema/gemma.sql Wed Mar 11 10:38:33 2020 +0100 +++ b/schema/gemma.sql Wed Mar 11 10:58:53 2020 +0100 @@ -409,6 +409,8 @@ style bytea, as_wms boolean NOT NULL DEFAULT TRUE, as_wfs boolean NOT NULL DEFAULT TRUE, + wmst_attribute varchar DEFAULT NULL, + wmst_end_attribute varchar DEFAULT NULL, -- Either give a valid relation or a SQL statement: CHECK (to_regclass(schema || '.' || name) IS NOT NULL OR view_def IS NOT NULL)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1420/01.wmst_attributes.sql Wed Mar 11 10:58:53 2020 +0100 @@ -0,0 +1,2 @@ +ALTER TABLE sys_admin.published_services ADD COLUMN wmst_attribute varchar DEFAULT NULL; +ALTER TABLE sys_admin.published_services ADD COLUMN wmst_end_attribute varchar DEFAULT NULL;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1421/01.fairway-marks_wms-t.sql Wed Mar 11 10:58:53 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';