Mercurial > gemma

changeset 4909:6f244b5eb716 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use table inheritance to define common fairway marks attributes
author Tom Gottfried <tom@intevation.de>
date Mon, 10 Feb 2020 14:56:43 +0100
parents 52c31d3b5131
children ab184888d58b
files pkg/imports/fm_bcnlat.go schema/gemma.sql
diffstat 2 files changed, 22 insertions(+), 13 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/fm_bcnlat.go	Fri Feb 07 19:42:09 2020 +0100
+++ b/pkg/imports/fm_bcnlat.go	Mon Feb 10 14:56:43 2020 +0100
@@ -51,7 +51,7 @@
 
 func (bcnlatJobCreator) Depends() [2][]string {
 	return [2][]string{
-		{"fairway_marks"},
+		{"fairway_marks_bcnlat"},
 		{},
 	}
 }
@@ -85,7 +85,7 @@
 with a as (
   select users.current_user_area_utm() AS a
 )
-INSERT INTO waterway.fairway_marks (
+INSERT INTO waterway.fairway_marks_bcnlat (
   geom,
   datsta,
   datend,
@@ -114,10 +114,11 @@
     OR ST_Intersects((select a from a),
       ST_Transform(newfm, (select ST_SRID(a) from a)))
 ON CONFLICT (
-    CAST((0, geom,
+  CAST((geom,
       datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
-      scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn,
-      bcnshp, catlam, dirimp) AS waterway.fairway_marks)
+      scamin, picrep, txtdsc, sordat, sorind,
+      0, colour, colpat, condtn, bcnshp, catlam, dirimp
+    ) AS waterway.fairway_marks_bcnlat)
   )
   DO NOTHING
 RETURNING id
--- a/schema/gemma.sql	Fri Feb 07 19:42:09 2020 +0100
+++ b/schema/gemma.sql	Mon Feb 10 14:56:43 2020 +0100
@@ -841,8 +841,9 @@
             OR value_lifetime IS NOT NULL)
     )
 
+    -- Attributes common to all fairway marks
+    -- according to IENC feature catalogue
     CREATE TABLE fairway_marks (
-        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         geom geography(POINT, 4326) NOT NULL,
         datsta varchar,
         datend varchar,
@@ -856,21 +857,28 @@
         picrep varchar,
         txtdsc varchar,
         sordat varchar,
-        sorind varchar,
+        sorind varchar
+    )
+
+    -- Additional attributes for IENC features BCNLAT/bcnlat
+    CREATE TABLE fairway_marks_bcnlat (
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         colour varchar,
         colpat varchar,
         condtn int,
         bcnshp int,
         catlam int,
         dirimp smallint REFERENCES dirimps
-    )
+    ) INHERITS (fairway_marks)
     -- Prevent identical entries using composite type comparison
     -- (i.e. considering two NULL values in a field equal):
-    CREATE UNIQUE INDEX fairway_marks_distinct_rows ON fairway_marks
-        ((CAST((0, geom,
-            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
-            scamin, picrep, txtdsc, sordat, sorind, colour, colpat, condtn,
-            bcnshp, catlam, dirimp) AS fairway_marks)
+    CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows
+        ON fairway_marks_bcnlat
+        ((CAST((geom,
+                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
+                scamin, picrep, txtdsc, sordat, sorind,
+                0, colour, colpat, condtn, bcnshp, catlam, dirimp
+            ) AS fairway_marks_bcnlat)
         ))
 ;