Mercurial > gemma
changeset 4909:6f244b5eb716 fairway-marks-import
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) )) ;