Mercurial > gemma
changeset 4962:1b309a8e7673 fairway-marks-import
Distinguish more clearly between BCNLAT HYDRO and IENC features
This allows importing them from different sources while keeping
the history of data intact. Additionally, storing them in different
tables also allows to have different attributes (here only
dirimp via an m:n-table) and different constraints (currently
not implemented) according to the IENC feature catalogue.
Since both new tables inherit from a table with the same name as the old
table, all entries still can be accessed via a table of the same name.
Thus, no changes to GeoServer layers are necessary.
ToDo: solve layout problems in the client SPA.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 27 Feb 2020 21:05:09 +0100 |
| parents | 67d78b74fe43 |
| children | 1e47ba2a58f2 |
| files | client/src/components/importconfiguration/types/Fairwaymarks.vue client/src/store/importschedule.js pkg/controllers/routes.go pkg/imports/fm.go pkg/imports/modelconvert.go schema/gemma.sql schema/updates/1406/01.distinguish_bcnlat_hydro_ienc.sql schema/version.sql |
| diffstat | 8 files changed, 208 insertions(+), 62 deletions(-) [+] |
line wrap: on
line diff
--- a/client/src/components/importconfiguration/types/Fairwaymarks.vue Wed Feb 26 19:28:21 2020 +0100 +++ b/client/src/components/importconfiguration/types/Fairwaymarks.vue Thu Feb 27 21:05:09 2020 +0100 @@ -125,7 +125,8 @@ }, FAIRWAYMARKS: { BCNISD: "Beacon, isolated danger (MARITIME/Hydro feature)", - BCNLAT: "Beacon, lateral (MARITIME/Hydro feature)", + BCNLAT_hydro: "Beacon, lateral (MARITIME/Hydro feature)", + bcnlat_ienc: "Beacon, lateral (IENC feature)", BOYCAR: "Buoy, cardinal (MARITIME/Hydro feature)", BOYISD: "Buoy, isolated danger (MARITIME/Hydro feature)", BOYLAT: "Buoy, lateral (MARITIME/Hydro feature)",
--- a/client/src/store/importschedule.js Wed Feb 26 19:28:21 2020 +0100 +++ b/client/src/store/importschedule.js Thu Feb 27 21:05:09 2020 +0100 @@ -62,7 +62,8 @@ const FAIRWAYMARKKINDS = { fm_bcnisd: "BCNISD", - fm_bcnlat: "BCNLAT", + fm_bcnlat_hydro: "BCNLAT_hydro", + fm_bcnlat_ienc: "bcnlat_ienc", fm_boycar: "BOYCAR", fm_boyisd: "BOYISD", fm_boylat: "BOYLAT",
--- a/pkg/controllers/routes.go Wed Feb 26 19:28:21 2020 +0100 +++ b/pkg/controllers/routes.go Thu Feb 27 21:05:09 2020 +0100 @@ -243,7 +243,8 @@ "bn", "gm", "fa", "wx", "wa", "wg", "dmv", "fd", "dma", "sec", "dsec", "dst", "dsr", - "fm_bcnlat", + "fm_bcnlat_hydro", + "fm_bcnlat_ienc", "fm_boycar", "fm_boylat", "fm_boysaw",
--- a/pkg/imports/fm.go Wed Feb 26 19:28:21 2020 +0100 +++ b/pkg/imports/fm.go Thu Feb 27 21:05:09 2020 +0100 @@ -42,13 +42,21 @@ bcnlatProperties struct { fairwayMarksProperties - Colour *string `json:"hydro_colour"` - Colpat *string `json:"hydro_colpat"` - Condtn *int `json:"hydro_condtn"` - Bcnshp *int `json:"hydro_bcnshp"` - HydroCatlam *int64 `json:"hydro_catlam,omitempty"` - IENCCatlam *int64 `json:"ienc_catlam,omitempty"` - Dirimp *string `json:"ienc_dirimp,omitempty"` + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Condtn *int `json:"hydro_condtn"` + Bcnshp *int `json:"hydro_bcnshp"` + } + + bcnlatHydroProperties struct { + bcnlatProperties + Catlam *int64 `json:"hydro_catlam"` + } + + bcnlatIencProperties struct { + bcnlatProperties + Catlam *int64 `json:"ienc_catlam"` + Dirimp *string `json:"ienc_dirimp"` } boylatProperties struct { @@ -155,32 +163,48 @@ ) const ( - BCNLATJobKind JobKind = "fm_bcnlat" - BOYLATJobKind JobKind = "fm_boylat" - BOYCARJobKind JobKind = "fm_boycar" - BOYSAWJobKind JobKind = "fm_boysaw" - BOYSPPJobKind JobKind = "fm_boyspp" - DAYMARJobKind JobKind = "fm_daymar" - LIGHTSJobKind JobKind = "fm_lights" - NOTMRKJobKind JobKind = "fm_notmrk" - RTPBCNJobKind JobKind = "fm_rtpbcn" - TOPMARJobKind JobKind = "fm_topmar" + BCNLATHYDROJobKind JobKind = "fm_bcnlat_hydro" + BCNLATIENCJobKind JobKind = "fm_bcnlat_ienc" + BOYLATJobKind JobKind = "fm_boylat" + BOYCARJobKind JobKind = "fm_boycar" + BOYSAWJobKind JobKind = "fm_boysaw" + BOYSPPJobKind JobKind = "fm_boyspp" + DAYMARJobKind JobKind = "fm_daymar" + LIGHTSJobKind JobKind = "fm_lights" + NOTMRKJobKind JobKind = "fm_notmrk" + RTPBCNJobKind JobKind = "fm_rtpbcn" + TOPMARJobKind JobKind = "fm_topmar" ) func init() { - RegisterJobCreator(BCNLATJobKind, + RegisterJobCreator(BCNLATHYDROJobKind, &PointWFSJobCreator{ - description: "fairway marks bcnlat", - depends: [2][]string{{"fairway_marks_bcnlat"}, {}}, + description: "fairway marks bcnlat (HYDRO)", + depends: [2][]string{{"fairway_marks_bcnlat_hydro"}, {}}, newConsumer: newSQLConsumer( prepareStmnts( - createInsertFMSQL("bcnlat", + createInsertFMSQL("bcnlat_hydro", + "colour", "colpat", "condtn", "bcnshp", "catlam"), + ), + consumeBCNLATHydro, + createInvalidation("bcnlat_hydro"), + func() interface{} { return new(bcnlatHydroProperties) }, + ), + }) + + RegisterJobCreator(BCNLATIENCJobKind, + &PointWFSJobCreator{ + description: "fairway marks bcnlat (IENC)", + depends: [2][]string{{"fairway_marks_bcnlat_ienc"}, {}}, + newConsumer: newSQLConsumer( + prepareStmnts( + createInsertFMSQL("bcnlat_ienc", "colour", "colpat", "condtn", "bcnshp", "catlam"), insertBcnlatDirimpSQL, ), - consumeBCNLAT, - createInvalidation("bcnlat"), - func() interface{} { return new(bcnlatProperties) }, + consumeBCNLATIenc, + createInvalidation("bcnlat_ienc"), + func() interface{} { return new(bcnlatIencProperties) }, ), }) @@ -499,15 +523,13 @@ } } -func consumeBCNLAT( +func consumeBCNLATHydro( spc *SQLPointConsumer, points pointSlice, properties interface{}, epsg int, ) error { - props := properties.(*bcnlatProperties) - - catlam := coalesceInt64(props.HydroCatlam, props.IENCCatlam) + props := properties.(*bcnlatHydroProperties) var fmid int64 err := spc.savepoint(func() error { @@ -532,7 +554,52 @@ props.Colpat, props.Condtn, props.Bcnshp, - catlam, + props.Catlam, + ).Scan(&fmid) + }) + switch { + case err == sql.ErrNoRows: + return ErrFeatureDuplicated + // ignore -> filtered by responsibility area or a duplicate + case err != nil: + spc.feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + return ErrFeatureIgnored + } + return nil +} + +func consumeBCNLATIenc( + spc *SQLPointConsumer, + points pointSlice, + properties interface{}, + epsg int, +) error { + props := properties.(*bcnlatIencProperties) + + var fmid int64 + err := spc.savepoint(func() error { + return spc.stmts[0].QueryRowContext( + spc.ctx, + points.asWKB(), + epsg, + props.Datsta, + props.Datend, + props.Persta, + props.Perend, + props.Objnam, + props.Nobjnm, + props.Inform, + props.Ninfom, + props.Scamin, + props.Picrep, + props.Txtdsc, + props.Sordat, + props.Sorind, + props.Colour, + props.Colpat, + props.Condtn, + props.Bcnshp, + props.Catlam, ).Scan(&fmid) }) switch {
--- a/pkg/imports/modelconvert.go Wed Feb 26 19:28:21 2020 +0100 +++ b/pkg/imports/modelconvert.go Thu Feb 27 21:05:09 2020 +0100 @@ -18,30 +18,31 @@ ) var kindToImportModel = map[JobKind]func() interface{}{ - BNJobKind: func() interface{} { return new(models.BottleneckImport) }, - GMJobKind: func() interface{} { return new(models.GaugeMeasurementImport) }, - FAJobKind: func() interface{} { return new(models.FairwayAvailabilityImport) }, - WXJobKind: func() interface{} { return new(models.WaterwayAxisImport) }, - WAJobKind: func() interface{} { return new(models.WaterwayAreaImport) }, - WGJobKind: func() interface{} { return new(models.WaterwayGaugeImport) }, - DMVJobKind: func() interface{} { return new(models.DistanceMarksVirtualImport) }, - FDJobKind: func() interface{} { return new(models.FairwayDimensionImport) }, - DMAJobKind: func() interface{} { return new(models.DistanceMarksAshoreImport) }, - BCNLATJobKind: func() interface{} { return FindJobCreator(BCNLATJobKind).Create() }, - BOYCARJobKind: func() interface{} { return FindJobCreator(BOYCARJobKind).Create() }, - BOYLATJobKind: func() interface{} { return FindJobCreator(BOYLATJobKind).Create() }, - BOYSAWJobKind: func() interface{} { return FindJobCreator(BOYSAWJobKind).Create() }, - BOYSPPJobKind: func() interface{} { return FindJobCreator(BOYSPPJobKind).Create() }, - DAYMARJobKind: func() interface{} { return FindJobCreator(DAYMARJobKind).Create() }, - LIGHTSJobKind: func() interface{} { return FindJobCreator(LIGHTSJobKind).Create() }, - RTPBCNJobKind: func() interface{} { return FindJobCreator(RTPBCNJobKind).Create() }, - TOPMARJobKind: func() interface{} { return FindJobCreator(TOPMARJobKind).Create() }, - NOTMRKJobKind: func() interface{} { return FindJobCreator(NOTMRKJobKind).Create() }, - STJobKind: func() interface{} { return new(models.StretchImport) }, - SECJobKind: func() interface{} { return new(models.SectionImport) }, - DSECJobKind: func() interface{} { return new(models.SectionDelete) }, - DSTJobKind: func() interface{} { return new(models.StretchDelete) }, - DSRJobKind: func() interface{} { return new(models.SoundingResultDelete) }, + BNJobKind: func() interface{} { return new(models.BottleneckImport) }, + GMJobKind: func() interface{} { return new(models.GaugeMeasurementImport) }, + FAJobKind: func() interface{} { return new(models.FairwayAvailabilityImport) }, + WXJobKind: func() interface{} { return new(models.WaterwayAxisImport) }, + WAJobKind: func() interface{} { return new(models.WaterwayAreaImport) }, + WGJobKind: func() interface{} { return new(models.WaterwayGaugeImport) }, + DMVJobKind: func() interface{} { return new(models.DistanceMarksVirtualImport) }, + FDJobKind: func() interface{} { return new(models.FairwayDimensionImport) }, + DMAJobKind: func() interface{} { return new(models.DistanceMarksAshoreImport) }, + BCNLATHYDROJobKind: func() interface{} { return FindJobCreator(BCNLATHYDROJobKind).Create() }, + BCNLATIENCJobKind: func() interface{} { return FindJobCreator(BCNLATIENCJobKind).Create() }, + BOYCARJobKind: func() interface{} { return FindJobCreator(BOYCARJobKind).Create() }, + BOYLATJobKind: func() interface{} { return FindJobCreator(BOYLATJobKind).Create() }, + BOYSAWJobKind: func() interface{} { return FindJobCreator(BOYSAWJobKind).Create() }, + BOYSPPJobKind: func() interface{} { return FindJobCreator(BOYSPPJobKind).Create() }, + DAYMARJobKind: func() interface{} { return FindJobCreator(DAYMARJobKind).Create() }, + LIGHTSJobKind: func() interface{} { return FindJobCreator(LIGHTSJobKind).Create() }, + RTPBCNJobKind: func() interface{} { return FindJobCreator(RTPBCNJobKind).Create() }, + TOPMARJobKind: func() interface{} { return FindJobCreator(TOPMARJobKind).Create() }, + NOTMRKJobKind: func() interface{} { return FindJobCreator(NOTMRKJobKind).Create() }, + STJobKind: func() interface{} { return new(models.StretchImport) }, + SECJobKind: func() interface{} { return new(models.SectionImport) }, + DSECJobKind: func() interface{} { return new(models.SectionDelete) }, + DSTJobKind: func() interface{} { return new(models.StretchDelete) }, + DSRJobKind: func() interface{} { return new(models.SoundingResultDelete) }, } // ImportModelForJobKind returns the constructor function to
--- a/schema/gemma.sql Wed Feb 26 19:28:21 2020 +0100 +++ b/schema/gemma.sql Thu Feb 27 21:05:09 2020 +0100 @@ -879,26 +879,43 @@ -- 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 ) INHERITS (fairway_marks) + + CREATE TABLE fairway_marks_bcnlat_hydro ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ) INHERITS (fairway_marks_bcnlat) -- Prevent identical entries using composite type comparison -- (i.e. considering two NULL values in a field equal): - CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows + CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows ON 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 fairway_marks_bcnlat) + colour, colpat, condtn, bcnshp, catlam, 0 + ) AS fairway_marks_bcnlat_hydro) + )) + + CREATE TABLE fairway_marks_bcnlat_ienc ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ) INHERITS (fairway_marks_bcnlat) + -- Prevent identical entries using composite type comparison + -- (i.e. considering two NULL values in a field equal): + CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows + ON fairway_marks_bcnlat + ((CAST((validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, bcnshp, catlam, 0 + ) AS fairway_marks_bcnlat_ienc) )) CREATE TABLE fairway_marks_bcnlat_dirimps ( - fm_bcnlat_id int REFERENCES fairway_marks_bcnlat, + fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc, dirimp smallint REFERENCES dirimps, PRIMARY KEY (fm_bcnlat_id, dirimp) )
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1406/01.distinguish_bcnlat_hydro_ienc.sql Thu Feb 27 21:05:09 2020 +0100 @@ -0,0 +1,58 @@ +CREATE TABLE waterway.fairway_marks_bcnlat_new ( + colour varchar, + colpat varchar, + condtn int, + bcnshp int, + catlam int +) INHERITS (waterway.fairway_marks); + +CREATE TABLE waterway.fairway_marks_bcnlat_hydro ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY +) INHERITS (waterway.fairway_marks_bcnlat_new); +CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_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, + colour, colpat, condtn, bcnshp, catlam, 0 + ) AS waterway.fairway_marks_bcnlat_hydro) + )); + +CREATE TABLE waterway.fairway_marks_bcnlat_ienc ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY +) INHERITS (waterway.fairway_marks_bcnlat_new); +CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_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, + colour, colpat, condtn, bcnshp, catlam, 0 + ) AS waterway.fairway_marks_bcnlat_ienc) + )); + +-- Assume all features are IENC features, since there is currently no known +-- data source for HYDRO features +INSERT INTO waterway.fairway_marks_bcnlat_ienc + SELECT validity, last_found, geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + colour, colpat, condtn, bcnshp, catlam, id + FROM waterway.fairway_marks_bcnlat; +SELECT setval( + pg_get_serial_sequence('waterway.fairway_marks_bcnlat_ienc', 'id'), + max(id)) + FROM waterway.fairway_marks_bcnlat_ienc; + +-- Let foreign key constraint point to new table +ALTER TABLE waterway.fairway_marks_bcnlat_dirimps + DROP CONSTRAINT fairway_marks_bcnlat_dirimps_fm_bcnlat_id_fkey, + ADD FOREIGN KEY (fm_bcnlat_id) + REFERENCES waterway.fairway_marks_bcnlat_ienc; + +-- Finally +DROP TABLE waterway.fairway_marks_bcnlat; +ALTER TABLE waterway.fairway_marks_bcnlat_new RENAME TO fairway_marks_bcnlat; + +GRANT SELECT on ALL tables in schema waterway TO waterway_user; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;