Mercurial > gemma
changeset 4956:7cc79c65a9e5 fairway-marks-import
Keep the history of fairway marks
ToDo: Set end to validity period when an entry can no longer be found
in a data source.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Wed, 26 Feb 2020 12:18:15 +0100 |
| parents | 5c43427fc2bf |
| children | b0607611bcdf |
| files | pkg/imports/fm.go pkg/imports/pointwfs.go schema/gemma.sql schema/updates/1405/01.add_fm_validity.sql schema/updates/1405/02.recreate_fm_indexes.sql |
| diffstat | 5 files changed, 227 insertions(+), 31 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/imports/fm.go Wed Feb 26 11:25:28 2020 +0100 +++ b/pkg/imports/fm.go Wed Feb 26 12:18:15 2020 +0100 @@ -324,11 +324,41 @@ const ( // Format string to be completed with type and additional attributes + /* Instead of the row comparisons in the WHERE clauses + of the CTE with the UPDATE and the INSERT ... SELECT, we could have + used the row-based UNIQUE indexes as arbiter indexes + in an INSERT ... ON CONFLICT ... DO UPDATE, but that turned out + to be able to bypass the UNIQUE index in some cases. + */ insertFMSQLtmpl = ` WITH a AS ( SELECT users.current_user_area_utm() AS a +), +g AS ( + SELECT newfm + FROM ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326) AS newfm (newfm) + WHERE pg_has_role('sys_admin', 'MEMBER') + OR ST_Intersects((select a from a), + ST_Transform(newfm, (select ST_SRID(a) from a))) +), +t AS ( + -- Currently valid and otherwise identical entry's validity. + /* If there are no intermittent updates of validity, + there will always be only one currently valid and + otherwise identical entry. */ + UPDATE waterway.fairway_marks_%[1]s SET last_found = current_timestamp + WHERE validity @> current_timestamp + AND (geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + %[2]s + ) IS NOT DISTINCT FROM ( + (SELECT newfm FROM g), + $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, + %[3]s) + RETURNING validity ) -INSERT INTO waterway.fairway_marks_%s ( +INSERT INTO waterway.fairway_marks_%[1]s ( geom, datsta, datend, @@ -343,25 +373,42 @@ txtdsc, sordat, sorind, - %s + %[2]s ) SELECT newfm, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, - %s - FROM ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326) AS newfm (newfm) - WHERE pg_has_role('sys_admin', 'MEMBER') - OR ST_Intersects((select a from a), - ST_Transform(newfm, (select ST_SRID(a) from a))) -ON CONFLICT ( - CAST((geom, - datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, - scamin, picrep, txtdsc, sordat, sorind, - 0, %[2]s - ) AS waterway.fairway_marks_%[1]s) - ) - DO NOTHING + %[3]s + FROM g + WHERE NOT EXISTS(SELECT 1 FROM waterway.fairway_marks_%[1]s + WHERE ( + validity, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + %[2]s + ) IS NOT DISTINCT FROM ( + (SELECT validity FROM t), newfm, + $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, + %[3]s + ) + ) RETURNING id ` + // Assume validity ended for all entries not found in data source + // TODO: Apply this query after looping over all entries in data source + invalidateFairwayMarksSQLtmpl = ` +WITH + a AS ( + SELECT users.current_user_area_utm() AS a + ) +UPDATE waterway.fairway_marks_%s + SET validity = tstzrange(lower(validity), current_timestamp) + WHERE validity @> current_timestamp + AND last_found < current_timestamp + AND (pg_has_role('sys_admin', 'MEMBER') + OR ST_Intersects((select a from a), + ST_Transform(CAST(geom AS geometry), (select ST_SRID(a) from a)))) + ` + insertBcnlatDirimpSQL = ` INSERT INTO waterway.fairway_marks_bcnlat_dirimps (fm_bcnlat_id, dirimp) VALUES ($1, $2)
--- a/pkg/imports/pointwfs.go Wed Feb 26 11:25:28 2020 +0100 +++ b/pkg/imports/pointwfs.go Wed Feb 26 12:18:15 2020 +0100 @@ -206,7 +206,7 @@ if dupes > 0 { feedback.Info( - "Features outside responsibility area and duplicates: %d", + "Features outside responsibility area, duplicates or unchanged: %d", dupes) } @@ -222,15 +222,17 @@ feedback.Warn("Unsupported types found: %s", unsupported) } - if features == 0 { - return nil, UnchangedError("no valid new features found") - } - + // Commit before eventually returning UnchangedError because we might + // have updated last_found if err = consumer.Commit(); err == nil { feedback.Info("Storing %d features took %s", features, time.Since(start)) } + if features == 0 { + return nil, UnchangedError("no valid new features found") + } + return nil, nil }
--- a/schema/gemma.sql Wed Feb 26 11:25:28 2020 +0100 +++ b/schema/gemma.sql Wed Feb 26 12:18:15 2020 +0100 @@ -855,9 +855,13 @@ ) -- Attributes common to all fairway marks - -- according to IENC feature catalogue CREATE TABLE fairway_marks ( + validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) + CHECK (NOT isempty(validity)), + -- Last time an import job found this entry in a data source: + last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, geom geography(POINT, 4326) NOT NULL, + -- Attributes according to IENC Feature Catalogue: datsta varchar, datend varchar, persta varchar, @@ -886,7 +890,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows ON fairway_marks_bcnlat - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, condtn, bcnshp, catlam @@ -913,7 +917,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows ON fairway_marks_boycar - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp, catcam @@ -934,7 +938,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows ON fairway_marks_boylat - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp, catlam @@ -954,7 +958,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows ON fairway_marks_boysaw - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp @@ -975,7 +979,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows ON fairway_marks_boyspp - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp, catspm @@ -995,7 +999,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_daymar_distinct_rows ON fairway_marks_daymar - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, condtn, topshp, orient @@ -1030,7 +1034,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows ON fairway_marks_lights - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, condtn, orient, catlit, exclit, litchr, litvis, @@ -1050,7 +1054,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows ON fairway_marks_rtpbcn - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, condtn, siggrp, catrtb, radwal @@ -1069,7 +1073,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows ON fairway_marks_topmar - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, condtn, topshp @@ -1096,7 +1100,7 @@ -- (i.e. considering two NULL values in a field equal): CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows ON fairway_marks_notmrk - ((CAST((geom, + ((CAST((validity, last_found, geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, condtn, marsys, orient, status, addmrk, catnmk,
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1405/01.add_fm_validity.sql Wed Feb 26 12:18:15 2020 +0100 @@ -0,0 +1,51 @@ +CREATE TABLE waterway.fairway_marks_new ( + validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) + CHECK (NOT isempty(validity)), + last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, + LIKE waterway.fairway_marks +); + +DO LANGUAGE plpgsql +$$ +DECLARE table_suffix varchar; +BEGIN + /* Re-write tables inheriting from fairway_marks to include new columns + in correct position */ + FOREACH table_suffix IN ARRAY ARRAY[ + 'bcnlat', 'boycar', 'boylat', 'boysaw', 'boyspp', + 'daymar', 'lights', 'rtpbcn', 'topmar', 'notmrk'] + LOOP + EXECUTE format('CREATE TABLE waterway.fairway_marks_%s_new ' + '(LIKE waterway.fairway_marks_%1$s INCLUDING ALL) ' + 'INHERITS (waterway.fairway_marks_new)', table_suffix); + /* Drop index referring to wrong columns. See + https://www.postgresql.org/message-id/flat/16272-6e32da020e9a9381%40postgresql.org + */ + EXECUTE format('DROP INDEX waterway.fairway_marks_%s_new_row_idx', + table_suffix); + EXECUTE format('INSERT INTO waterway.fairway_marks_%s_new ' + 'SELECT tstzrange(current_timestamp, NULL), current_timestamp, * ' + 'FROM waterway.fairway_marks_%1$s', table_suffix); + EXECUTE format('DROP TABLE waterway.fairway_marks_%s CASCADE', + table_suffix); + EXECUTE format('ALTER TABLE waterway.fairway_marks_%s_new ' + 'RENAME TO fairway_marks_%1$s', table_suffix); + END LOOP; + + /* Recreate foreign key constraints */ + FOREACH table_suffix IN ARRAY ARRAY['bcnlat', 'daymar', 'notmrk'] + LOOP + EXECUTE format('ALTER TABLE waterway.fairway_marks_%1$s_dirimps ' + 'ADD FOREIGN KEY (fm_%1$s_id) ' + 'REFERENCES waterway.fairway_marks_%1$s', table_suffix); + END LOOP; +END; +$$; + +DROP TABLE waterway.fairway_marks; +ALTER TABLE waterway.fairway_marks_new RENAME TO fairway_marks; + +-- Restore GRANTs +GRANT SELECT on ALL tables in schema waterway TO waterway_user ; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1405/02.recreate_fm_indexes.sql Wed Feb 26 12:18:15 2020 +0100 @@ -0,0 +1,92 @@ +CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows + ON waterway.fairway_marks_bcnlat + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, bcnshp, catlam + ) AS waterway.fairway_marks_bcnlat) + )); + +CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows + ON waterway.fairway_marks_boycar + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catcam + ) AS waterway.fairway_marks_boycar) + )); + +CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows + ON waterway.fairway_marks_boylat + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catlam + ) AS waterway.fairway_marks_boylat) + )); + + +CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows + ON waterway.fairway_marks_boysaw + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp + ) AS waterway.fairway_marks_boysaw) + )); + +CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows + ON waterway.fairway_marks_boyspp + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catspm + ) AS waterway.fairway_marks_boyspp) + )); + +CREATE UNIQUE INDEX fairway_marks_daymar_distinct_rows + ON waterway.fairway_marks_daymar + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp, orient + ) AS waterway.fairway_marks_daymar) + )); + +CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows + ON waterway.fairway_marks_lights + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, condtn, orient, catlit, exclit, litchr, litvis, + mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status + ) AS waterway.fairway_marks_lights) + )); + +CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows + ON waterway.fairway_marks_rtpbcn + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, siggrp, catrtb, radwal + ) AS waterway.fairway_marks_rtpbcn) + )); + +CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows + ON waterway.fairway_marks_topmar + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp + ) AS waterway.fairway_marks_topmar) + )); + +CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows + ON waterway.fairway_marks_notmrk + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, marsys, orient, status, addmrk, catnmk, + disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw + ) AS waterway.fairway_marks_notmrk) + ));