Mercurial > gemma

diff schema/gemma.sql @ 4956:7cc79c65a9e5 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 b3b2ba09a450
children 1b309a8e7673
line wrap: on
line diff
--- 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,