Mercurial > gemma
changeset 4943:4c57d80ad7a7 fairway-marks-import
merge
| author | Thomas Junk <thomas.junk@intevation.de> |
|---|---|
| date | Tue, 18 Feb 2020 09:10:27 +0100 |
| parents | c4d84be3a476 (current diff) df6c8a485979 (diff) |
| children | 6256d1bca8f6 |
| files | |
| diffstat | 17 files changed, 1659 insertions(+), 13 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/controllers/routes.go Tue Feb 18 09:10:05 2020 +0100 +++ b/pkg/controllers/routes.go Tue Feb 18 09:10:27 2020 +0100 @@ -246,6 +246,13 @@ "fm_bcnlat", "fm_boycar", "fm_boylat", + "fm_boysaw", + "fm_boyspp", + "fm_daymar", + "fm_lights", + "fm_rtpbcn", + "fm_topmar", + "fm_notmrk", }, "|") api.Handle("/imports/{kind:"+kinds+"}", waterwayAdmin(&mw.JSONHandler{
--- a/pkg/imports/fm.go Tue Feb 18 09:10:05 2020 +0100 +++ b/pkg/imports/fm.go Tue Feb 18 09:10:27 2020 +0100 @@ -101,7 +101,7 @@ ) // Create INSERT statement for specific fairway marks type -func getFMInsertSQL(fmType string, attributes []string) string { +func getFMInsertSQL(fmType string, attributes ...string) string { attNums := "$16" for i := 1; i < len(attributes); i++ { attNums += fmt.Sprintf(",$%d", 16+i)
--- a/pkg/imports/fm_bcnlat.go Tue Feb 18 09:10:05 2020 +0100 +++ b/pkg/imports/fm_bcnlat.go Tue Feb 18 09:10:27 2020 +0100 @@ -80,7 +80,7 @@ } const ( - insertDirimpSQL = ` + insertBcnlatDirimpSQL = ` INSERT INTO waterway.fairway_marks_bcnlat_dirimps (fm_bcnlat_id, dirimp) VALUES ($1, $2) ` @@ -111,19 +111,20 @@ insertStmt, err := tx.PrepareContext( ctx, - getFMInsertSQL("bcnlat", []string{ - "colour", "colpat", "condtn", "bcnshp", "catlam"}), + getFMInsertSQL("bcnlat", + "colour", "colpat", "condtn", "bcnshp", "catlam"), ) if err != nil { return } defer insertStmt.Close() - insertDirimpStmt, err := tx.PrepareContext(ctx, insertDirimpSQL) + insertBcnlatDirimpStmt, err := tx.PrepareContext( + ctx, insertBcnlatDirimpSQL) if err != nil { return } - defer insertDirimpStmt.Close() + defer insertBcnlatDirimpStmt.Close() savepoint := Savepoint(ctx, tx, "feature") @@ -181,12 +182,15 @@ dirimps := strings.Split(*f.props.Dirimp, ",") for _, dirimp := range dirimps { if err := savepoint(func() error { - _, err := insertDirimpStmt.ExecContext( + _, err := insertBcnlatDirimpStmt.ExecContext( ctx, fmid, dirimp) return err }); err != nil { feedback.Warn( pgxutils.ReadableError{Err: err}.Error()) + feedback.Info( + "Tried to import '%s' as dirimp value", + dirimp) } } }
--- a/pkg/imports/fm_boycar.go Tue Feb 18 09:10:05 2020 +0100 +++ b/pkg/imports/fm_boycar.go Tue Feb 18 09:10:27 2020 +0100 @@ -102,9 +102,9 @@ insertStmt, err := tx.PrepareContext( ctx, - getFMInsertSQL("boycar", []string{ + getFMInsertSQL("boycar", "colour", "colpat", "conrad", - "marsys", "boyshp", "catcam"}), + "marsys", "boyshp", "catcam"), ) if err != nil { return
--- a/pkg/imports/fm_boylat.go Tue Feb 18 09:10:05 2020 +0100 +++ b/pkg/imports/fm_boylat.go Tue Feb 18 09:10:27 2020 +0100 @@ -104,9 +104,9 @@ insertStmt, err := tx.PrepareContext( ctx, - getFMInsertSQL("boylat", []string{ + getFMInsertSQL("boylat", "colour", "colpat", "conrad", - "marsys", "boyshp", "catlam"}), + "marsys", "boyshp", "catlam"), ) if err != nil { return
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm_boysaw.go Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,159 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> + +package imports + +import ( + "context" + "database/sql" + + "gemma.intevation.de/gemma/pkg/pgxutils" +) + +// Boysaw is an import job to import +// fairway marks of type BOYSAW in form of point geometries +// and attribute data from a WFS service. +type Boysaw struct { + FairwayMarks +} + +// Description gives a short info about relevant facts of this import. +func (boysaw *Boysaw) Description() (string, error) { + return boysaw.URL + "|" + boysaw.FeatureType, nil +} + +// BOYSAWJobKind is the import queue type identifier. +const BOYSAWJobKind JobKind = "fm_boysaw" + +type boysawJobCreator struct{} + +func init() { + RegisterJobCreator(BOYSAWJobKind, boysawJobCreator{}) +} + +func (boysawJobCreator) Description() string { return "fairway marks boysaw" } + +func (boysawJobCreator) AutoAccept() bool { return true } + +func (boysawJobCreator) Create() Job { return new(Boysaw) } + +func (boysawJobCreator) Depends() [2][]string { + return [2][]string{ + {"fairway_marks_boysaw"}, + {}, + } +} + +// StageDone is a NOP for fairway marks imports. +func (boysawJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +// CleanUp for fairway marks imports is a NOP. +func (*Boysaw) CleanUp() error { return nil } + +type boysawProperties struct { + fairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Conrad *int `json:"hydro_conrad"` + Marsys *int64 `json:"hydro_marsys"` + Boyshp *int `json:"hydro_boyshp"` +} + +type boysawFeaturetype struct { + geom pointSlice + props *boysawProperties +} + +// Do executes the actual import. +func (fm *Boysaw) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + err := getFMFeatures( + ctx, + conn, + feedback, + fm.FairwayMarks, + func() interface{} { return new(boysawProperties) }, + func(p pointSlice, props interface{}) interface{} { + return &boysawFeaturetype{p, props.(*boysawProperties)} + }, + func( + tx *sql.Tx, epsg int, fms []interface{}, + ) (outsideOrDup int, features int, err error) { + + feedback.Info("Store fairway marks of type BOYSAW") + + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("boysaw", + "colour", "colpat", "conrad", "marsys", "boyshp"), + ) + if err != nil { + return + } + defer insertStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + + for _, fm := range fms { + + f := fm.(*boysawFeaturetype) + + var fmid int64 + err := savepoint(func() error { + err := insertStmt.QueryRowContext( + ctx, + f.geom.asWKB(), + epsg, + f.props.Datsta, + f.props.Datend, + f.props.Persta, + f.props.Perend, + f.props.Objnam, + f.props.Nobjnm, + f.props.Inform, + f.props.Ninfom, + f.props.Scamin, + f.props.Picrep, + f.props.Txtdsc, + f.props.Sordat, + f.props.Sorind, + f.props.Colour, + f.props.Colpat, + f.props.Conrad, + f.props.Marsys, + f.props.Boyshp, + ).Scan(&fmid) + return err + }) + switch { + case err == sql.ErrNoRows: + outsideOrDup++ + // ignore -> filtered by responsibility_areas + case err != nil: + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + default: + features++ + } + } + return + }) + + return nil, err +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm_boyspp.go Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,162 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> + +package imports + +import ( + "context" + "database/sql" + + "gemma.intevation.de/gemma/pkg/pgxutils" +) + +// Boyspp is an import job to import +// fairway marks of type BOYSPP in form of point geometries +// and attribute data from a WFS service. +type Boyspp struct { + FairwayMarks +} + +// Description gives a short info about relevant facts of this import. +func (boyspp *Boyspp) Description() (string, error) { + return boyspp.URL + "|" + boyspp.FeatureType, nil +} + +// BOYSPPJobKind is the import queue type identifier. +const BOYSPPJobKind JobKind = "fm_boyspp" + +type boysppJobCreator struct{} + +func init() { + RegisterJobCreator(BOYSPPJobKind, boysppJobCreator{}) +} + +func (boysppJobCreator) Description() string { return "fairway marks boyspp" } + +func (boysppJobCreator) AutoAccept() bool { return true } + +func (boysppJobCreator) Create() Job { return new(Boyspp) } + +func (boysppJobCreator) Depends() [2][]string { + return [2][]string{ + {"fairway_marks_boyspp"}, + {}, + } +} + +// StageDone is a NOP for fairway marks imports. +func (boysppJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +// CleanUp for fairway marks imports is a NOP. +func (*Boyspp) CleanUp() error { return nil } + +type boysppProperties struct { + fairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Conrad *int `json:"hydro_conrad"` + Marsys *int64 `json:"hydro_marsys"` + Boyshp *int `json:"hydro_boyshp"` + Catspm *string `json:"hydro_catspm"` +} + +type boysppFeaturetype struct { + geom pointSlice + props *boysppProperties +} + +// Do executes the actual import. +func (fm *Boyspp) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + err := getFMFeatures( + ctx, + conn, + feedback, + fm.FairwayMarks, + func() interface{} { return new(boysppProperties) }, + func(p pointSlice, props interface{}) interface{} { + return &boysppFeaturetype{p, props.(*boysppProperties)} + }, + func( + tx *sql.Tx, epsg int, fms []interface{}, + ) (outsideOrDup int, features int, err error) { + + feedback.Info("Store fairway marks of type BOYSPP") + + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("boyspp", + "colour", "colpat", "conrad", + "marsys", "boyshp", "catspm"), + ) + if err != nil { + return + } + defer insertStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + + for _, fm := range fms { + + f := fm.(*boysppFeaturetype) + + var fmid int64 + err := savepoint(func() error { + err := insertStmt.QueryRowContext( + ctx, + f.geom.asWKB(), + epsg, + f.props.Datsta, + f.props.Datend, + f.props.Persta, + f.props.Perend, + f.props.Objnam, + f.props.Nobjnm, + f.props.Inform, + f.props.Ninfom, + f.props.Scamin, + f.props.Picrep, + f.props.Txtdsc, + f.props.Sordat, + f.props.Sorind, + f.props.Colour, + f.props.Colpat, + f.props.Conrad, + f.props.Marsys, + f.props.Boyshp, + f.props.Catspm, + ).Scan(&fmid) + return err + }) + switch { + case err == sql.ErrNoRows: + outsideOrDup++ + // ignore -> filtered by responsibility_areas + case err != nil: + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + default: + features++ + } + } + return + }) + + return nil, err +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm_daymar.go Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,193 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> + +package imports + +import ( + "context" + "database/sql" + "strings" + + "gemma.intevation.de/gemma/pkg/pgxutils" +) + +// Daymar is an import job to import +// fairway marks of type DAYMAR in form of point geometries +// and attribute data from a WFS service. +type Daymar struct { + FairwayMarks +} + +// Description gives a short info about relevant facts of this import. +func (daymar *Daymar) Description() (string, error) { + return daymar.URL + "|" + daymar.FeatureType, nil +} + +// DAYMARJobKind is the import queue type identifier. +const DAYMARJobKind JobKind = "fm_daymar" + +type daymarJobCreator struct{} + +func init() { + RegisterJobCreator(DAYMARJobKind, daymarJobCreator{}) +} + +func (daymarJobCreator) Description() string { return "fairway marks daymar" } + +func (daymarJobCreator) AutoAccept() bool { return true } + +func (daymarJobCreator) Create() Job { return new(Daymar) } + +func (daymarJobCreator) Depends() [2][]string { + return [2][]string{ + {"fairway_marks_daymar"}, + {}, + } +} + +// StageDone is a NOP for fairway marks imports. +func (daymarJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +// CleanUp for fairway marks imports is a NOP. +func (*Daymar) CleanUp() error { return nil } + +type daymarProperties struct { + fairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Condtn *int `json:"hydro_condtn"` + Dirimp *string `json:"ienc_dirimp,omitempty"` + Topshp *int `json:"hydro_topshp"` + Orient *float64 `json:"hydro_orient,omitempty"` +} + +type daymarFeaturetype struct { + geom pointSlice + props *daymarProperties +} + +const ( + insertDaymarDirimpSQL = ` +INSERT INTO waterway.fairway_marks_daymar_dirimps (fm_daymar_id, dirimp) + VALUES ($1, $2) +` +) + +// Do executes the actual import. +func (fm *Daymar) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + err := getFMFeatures( + ctx, + conn, + feedback, + fm.FairwayMarks, + func() interface{} { return new(daymarProperties) }, + func(p pointSlice, props interface{}) interface{} { + return &daymarFeaturetype{p, props.(*daymarProperties)} + }, + func( + tx *sql.Tx, epsg int, fms []interface{}, + ) (outsideOrDup int, features int, err error) { + + feedback.Info("Store fairway marks of type DAYMAR/daymar") + + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("daymar", + "colour", "colpat", "condtn", "topshp", "orient"), + ) + if err != nil { + return + } + defer insertStmt.Close() + + insertDaymarDirimpStmt, err := tx.PrepareContext( + ctx, insertDaymarDirimpSQL) + if err != nil { + return + } + defer insertDaymarDirimpStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + + for _, fm := range fms { + + f := fm.(*daymarFeaturetype) + + var fmid int64 + err := savepoint(func() error { + err := insertStmt.QueryRowContext( + ctx, + f.geom.asWKB(), + epsg, + f.props.Datsta, + f.props.Datend, + f.props.Persta, + f.props.Perend, + f.props.Objnam, + f.props.Nobjnm, + f.props.Inform, + f.props.Ninfom, + f.props.Scamin, + f.props.Picrep, + f.props.Txtdsc, + f.props.Sordat, + f.props.Sorind, + f.props.Colour, + f.props.Colpat, + f.props.Condtn, + f.props.Topshp, + f.props.Orient, + ).Scan(&fmid) + return err + }) + switch { + case err == sql.ErrNoRows: + outsideOrDup++ + // ignore -> filtered by responsibility area or a duplicate + // TODO: handle eventual changes to dirimp + case err != nil: + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + default: + features++ + + if f.props.Dirimp != nil && *f.props.Dirimp != "" { + dirimps := strings.Split(*f.props.Dirimp, ",") + for _, dirimp := range dirimps { + if err := savepoint(func() error { + _, err := insertDaymarDirimpStmt.ExecContext( + ctx, fmid, dirimp) + return err + }); err != nil { + feedback.Warn( + pgxutils.ReadableError{Err: err}.Error()) + feedback.Info( + "Tried to import '%s' as dirimp value", + dirimp) + } + } + } + } + } + return + }) + + return nil, err +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm_lights.go Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,181 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> + +package imports + +import ( + "context" + "database/sql" + + "gemma.intevation.de/gemma/pkg/pgxutils" +) + +// Lights is an import job to import +// fairway marks of type LIGHTS in form of point geometries +// and attribute data from a WFS service. +type Lights struct { + FairwayMarks +} + +// Description gives a short info about relevant facts of this import. +func (lights *Lights) Description() (string, error) { + return lights.URL + "|" + lights.FeatureType, nil +} + +// LIGHTSJobKind is the import queue type identifier. +const LIGHTSJobKind JobKind = "fm_lights" + +type lightsJobCreator struct{} + +func init() { + RegisterJobCreator(LIGHTSJobKind, lightsJobCreator{}) +} + +func (lightsJobCreator) Description() string { return "fairway marks lights" } + +func (lightsJobCreator) AutoAccept() bool { return true } + +func (lightsJobCreator) Create() Job { return new(Lights) } + +func (lightsJobCreator) Depends() [2][]string { + return [2][]string{ + {"fairway_marks_lights"}, + {}, + } +} + +// StageDone is a NOP for fairway marks imports. +func (lightsJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +// CleanUp for fairway marks imports is a NOP. +func (*Lights) CleanUp() error { return nil } + +type lightsProperties struct { + fairwayMarksProperties + Colour *string `json:"hydro_colour"` + Condtn *int `json:"hydro_condtn"` + Orient *float64 `json:"hydro_orient"` + Catlit *string `json:"hydro_catlit"` + Exclit *int `json:"hydro_exclit"` + Litchr *int `json:"hydro_litchr"` + Litvis *string `json:"hydro_litvis"` + Mltylt *int `json:"hydro_mltylt"` + Sectr1 *float64 `json:"hydro_sectr1"` + Sectr2 *float64 `json:"hydro_sectr2"` + Siggrp *string `json:"hydro_siggrp"` + Sigper *float64 `json:"hydro_sigper"` + Sigseq *string `json:"hydro_sigseq"` + Status *string `json:"hydro_status"` +} + +type lightsFeaturetype struct { + geom pointSlice + props *lightsProperties +} + +// Do executes the actual import. +func (fm *Lights) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + err := getFMFeatures( + ctx, + conn, + feedback, + fm.FairwayMarks, + func() interface{} { return new(lightsProperties) }, + func(p pointSlice, props interface{}) interface{} { + return &lightsFeaturetype{p, props.(*lightsProperties)} + }, + func( + tx *sql.Tx, epsg int, fms []interface{}, + ) (outsideOrDup int, features int, err error) { + + feedback.Info("Store fairway marks of type LIGHTS") + + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("lights", + "colour", "condtn", "orient", + "catlit", "exclit", "litchr", + "litvis", "mltylt", "sectr1", + "sectr2", "siggrp", "sigper", + "sigseq", "status"), + ) + if err != nil { + return + } + defer insertStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + + for _, fm := range fms { + + f := fm.(*lightsFeaturetype) + + var fmid int64 + err := savepoint(func() error { + err := insertStmt.QueryRowContext( + ctx, + f.geom.asWKB(), + epsg, + f.props.Datsta, + f.props.Datend, + f.props.Persta, + f.props.Perend, + f.props.Objnam, + f.props.Nobjnm, + f.props.Inform, + f.props.Ninfom, + f.props.Scamin, + f.props.Picrep, + f.props.Txtdsc, + f.props.Sordat, + f.props.Sorind, + f.props.Colour, + f.props.Condtn, + f.props.Orient, + f.props.Catlit, + f.props.Exclit, + f.props.Litchr, + f.props.Litvis, + f.props.Mltylt, + f.props.Sectr1, + f.props.Sectr2, + f.props.Siggrp, + f.props.Sigper, + f.props.Sigseq, + f.props.Status, + ).Scan(&fmid) + return err + }) + switch { + case err == sql.ErrNoRows: + outsideOrDup++ + // ignore -> filtered by responsibility area or a duplicate + case err != nil: + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + default: + features++ + } + } + return + }) + + return nil, err +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm_notmrk.go Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,210 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> + +package imports + +import ( + "context" + "database/sql" + "strings" + + "gemma.intevation.de/gemma/pkg/pgxutils" +) + +// Notmrk is an import job to import +// fairway marks of type NOTMRK in form of point geometries +// and attribute data from a WFS service. +type Notmrk struct { + FairwayMarks +} + +// Description gives a short info about relevant facts of this import. +func (notmrk *Notmrk) Description() (string, error) { + return notmrk.URL + "|" + notmrk.FeatureType, nil +} + +// NOTMRKJobKind is the import queue type identifier. +const NOTMRKJobKind JobKind = "fm_notmrk" + +type notmrkJobCreator struct{} + +func init() { + RegisterJobCreator(NOTMRKJobKind, notmrkJobCreator{}) +} + +func (notmrkJobCreator) Description() string { return "fairway marks notmrk" } + +func (notmrkJobCreator) AutoAccept() bool { return true } + +func (notmrkJobCreator) Create() Job { return new(Notmrk) } + +func (notmrkJobCreator) Depends() [2][]string { + return [2][]string{ + {"fairway_marks_notmrk"}, + {}, + } +} + +// StageDone is a NOP for fairway marks imports. +func (notmrkJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +// CleanUp for fairway marks imports is a NOP. +func (*Notmrk) CleanUp() error { return nil } + +type notmrkProperties struct { + fairwayMarksProperties + Condtn *int `json:"hydro_condtn"` + Marsys *int `json:"hydro_bcnshp"` + Dirimp *string `json:"ienc_dirimp"` + Orient *float64 `json:"hydro_orient"` + Status *string `json:"hydro_status"` + Addmrk *string `json:"ienc_addmrk"` + Catnmk *int `json:"ienc_catnmk"` + Disipd *float64 `json:"ienc_disipd"` + Disipu *float64 `json:"ienc_disipu"` + Disbk1 *float64 `json:"ienc_disbk1"` + Disbk2 *float64 `json:"ienc_disbk2"` + Fnctnm *int `json:"ienc_fnctnm"` + Bnkwtw *int `json:"ienc_bnkwtw"` +} + +type notmrkFeaturetype struct { + geom pointSlice + props *notmrkProperties +} + +const ( + insertNotmrkDirimpSQL = ` +INSERT INTO waterway.fairway_marks_notmrk_dirimps (fm_notmrk_id, dirimp) + VALUES ($1, $2) +` +) + +// Do executes the actual import. +func (fm *Notmrk) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + err := getFMFeatures( + ctx, + conn, + feedback, + fm.FairwayMarks, + func() interface{} { return new(notmrkProperties) }, + func(p pointSlice, props interface{}) interface{} { + return ¬mrkFeaturetype{p, props.(*notmrkProperties)} + }, + func( + tx *sql.Tx, epsg int, fms []interface{}, + ) (outsideOrDup int, features int, err error) { + + feedback.Info("Store fairway marks of type NOTMRK") + + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("notmrk", + "condtn", "marsys", "orient", + "status", "addmrk", "catnmk", + "disipd", "disipu", "disbk1", + "disbk2", "fnctnm", "bnkwtw"), + ) + if err != nil { + return + } + defer insertStmt.Close() + + insertNotmrkDirimpStmt, err := tx.PrepareContext( + ctx, insertNotmrkDirimpSQL) + if err != nil { + return + } + defer insertNotmrkDirimpStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + + for _, fm := range fms { + + f := fm.(*notmrkFeaturetype) + + var fmid int64 + err := savepoint(func() error { + err := insertStmt.QueryRowContext( + ctx, + f.geom.asWKB(), + epsg, + f.props.Datsta, + f.props.Datend, + f.props.Persta, + f.props.Perend, + f.props.Objnam, + f.props.Nobjnm, + f.props.Inform, + f.props.Ninfom, + f.props.Scamin, + f.props.Picrep, + f.props.Txtdsc, + f.props.Sordat, + f.props.Sorind, + f.props.Condtn, + f.props.Marsys, + f.props.Orient, + f.props.Status, + f.props.Addmrk, + f.props.Catnmk, + f.props.Disipd, + f.props.Disipu, + f.props.Disbk1, + f.props.Disbk2, + f.props.Fnctnm, + f.props.Bnkwtw, + ).Scan(&fmid) + return err + }) + switch { + case err == sql.ErrNoRows: + outsideOrDup++ + // ignore -> filtered by responsibility area or a duplicate + // TODO: handle eventual changes to dirimp + case err != nil: + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + default: + features++ + + if f.props.Dirimp != nil && *f.props.Dirimp != "" { + dirimps := strings.Split(*f.props.Dirimp, ",") + for _, dirimp := range dirimps { + if err := savepoint(func() error { + _, err := insertNotmrkDirimpStmt.ExecContext( + ctx, fmid, dirimp) + return err + }); err != nil { + feedback.Warn( + pgxutils.ReadableError{Err: err}.Error()) + feedback.Info( + "Tried to import '%s' as dirimp value", + dirimp) + } + } + } + } + } + return + }) + + return nil, err +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm_rtpbcn.go Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,157 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> + +package imports + +import ( + "context" + "database/sql" + + "gemma.intevation.de/gemma/pkg/pgxutils" +) + +// Rtpbcn is an import job to import +// fairway marks of type RTPBCN in form of point geometries +// and attribute data from a WFS service. +type Rtpbcn struct { + FairwayMarks +} + +// Description gives a short info about relevant facts of this import. +func (rtpbcn *Rtpbcn) Description() (string, error) { + return rtpbcn.URL + "|" + rtpbcn.FeatureType, nil +} + +// RTPBCNJobKind is the import queue type identifier. +const RTPBCNJobKind JobKind = "fm_rtpbcn" + +type rtpbcnJobCreator struct{} + +func init() { + RegisterJobCreator(RTPBCNJobKind, rtpbcnJobCreator{}) +} + +func (rtpbcnJobCreator) Description() string { return "fairway marks rtpbcn" } + +func (rtpbcnJobCreator) AutoAccept() bool { return true } + +func (rtpbcnJobCreator) Create() Job { return new(Rtpbcn) } + +func (rtpbcnJobCreator) Depends() [2][]string { + return [2][]string{ + {"fairway_marks_rtpbcn"}, + {}, + } +} + +// StageDone is a NOP for fairway marks imports. +func (rtpbcnJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +// CleanUp for fairway marks imports is a NOP. +func (*Rtpbcn) CleanUp() error { return nil } + +type rtpbcnProperties struct { + fairwayMarksProperties + Condtn *int `json:"hydro_condtn"` + Siggrp *string `json:"hydro_siggrp"` + Catrtb *int `json:"hydro_catrtb"` + Radwal *string `json:"hydro_radwal"` +} + +type rtpbcnFeaturetype struct { + geom pointSlice + props *rtpbcnProperties +} + +// Do executes the actual import. +func (fm *Rtpbcn) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + err := getFMFeatures( + ctx, + conn, + feedback, + fm.FairwayMarks, + func() interface{} { return new(rtpbcnProperties) }, + func(p pointSlice, props interface{}) interface{} { + return &rtpbcnFeaturetype{p, props.(*rtpbcnProperties)} + }, + func( + tx *sql.Tx, epsg int, fms []interface{}, + ) (outsideOrDup int, features int, err error) { + + feedback.Info("Store fairway marks of type RTPBCN") + + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("rtpbcn", + "condtn", "siggrp", "catrtb", "radwal"), + ) + if err != nil { + return + } + defer insertStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + + for _, fm := range fms { + + f := fm.(*rtpbcnFeaturetype) + + var fmid int64 + err := savepoint(func() error { + err := insertStmt.QueryRowContext( + ctx, + f.geom.asWKB(), + epsg, + f.props.Datsta, + f.props.Datend, + f.props.Persta, + f.props.Perend, + f.props.Objnam, + f.props.Nobjnm, + f.props.Inform, + f.props.Ninfom, + f.props.Scamin, + f.props.Picrep, + f.props.Txtdsc, + f.props.Sordat, + f.props.Sorind, + f.props.Condtn, + f.props.Siggrp, + f.props.Catrtb, + f.props.Radwal, + ).Scan(&fmid) + return err + }) + switch { + case err == sql.ErrNoRows: + outsideOrDup++ + // ignore -> filtered by responsibility area or a duplicate + case err != nil: + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + default: + features++ + } + } + return + }) + + return nil, err +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/imports/fm_topmar.go Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,157 @@ +// This is Free Software under GNU Affero General Public License v >= 3.0 +// without warranty, see README.md and license for details. +// +// SPDX-License-Identifier: AGPL-3.0-or-later +// License-Filename: LICENSES/AGPL-3.0.txt +// +// Copyright (C) 2020 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Tom Gottfried <tom.gottfried@intevation.de> + +package imports + +import ( + "context" + "database/sql" + + "gemma.intevation.de/gemma/pkg/pgxutils" +) + +// Topmar is an import job to import +// fairway marks of type TOPMAR in form of point geometries +// and attribute data from a WFS service. +type Topmar struct { + FairwayMarks +} + +// Description gives a short info about relevant facts of this import. +func (topmar *Topmar) Description() (string, error) { + return topmar.URL + "|" + topmar.FeatureType, nil +} + +// TOPMARJobKind is the import queue type identifier. +const TOPMARJobKind JobKind = "fm_topmar" + +type topmarJobCreator struct{} + +func init() { + RegisterJobCreator(TOPMARJobKind, topmarJobCreator{}) +} + +func (topmarJobCreator) Description() string { return "fairway marks topmar" } + +func (topmarJobCreator) AutoAccept() bool { return true } + +func (topmarJobCreator) Create() Job { return new(Topmar) } + +func (topmarJobCreator) Depends() [2][]string { + return [2][]string{ + {"fairway_marks_topmar"}, + {}, + } +} + +// StageDone is a NOP for fairway marks imports. +func (topmarJobCreator) StageDone(context.Context, *sql.Tx, int64) error { + return nil +} + +// CleanUp for fairway marks imports is a NOP. +func (*Topmar) CleanUp() error { return nil } + +type topmarProperties struct { + fairwayMarksProperties + Colour *string `json:"hydro_colour"` + Colpat *string `json:"hydro_colpat"` + Condtn *int `json:"hydro_condtn"` + Topshp *int `json:"hydro_topshp"` +} + +type topmarFeaturetype struct { + geom pointSlice + props *topmarProperties +} + +// Do executes the actual import. +func (fm *Topmar) Do( + ctx context.Context, + importID int64, + conn *sql.Conn, + feedback Feedback, +) (interface{}, error) { + + err := getFMFeatures( + ctx, + conn, + feedback, + fm.FairwayMarks, + func() interface{} { return new(topmarProperties) }, + func(p pointSlice, props interface{}) interface{} { + return &topmarFeaturetype{p, props.(*topmarProperties)} + }, + func( + tx *sql.Tx, epsg int, fms []interface{}, + ) (outsideOrDup int, features int, err error) { + + feedback.Info("Store fairway marks of type TOPMAR") + + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("topmar", + "colour", "colpat", "condtn", "topshp"), + ) + if err != nil { + return + } + defer insertStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + + for _, fm := range fms { + + f := fm.(*topmarFeaturetype) + + var fmid int64 + err := savepoint(func() error { + err := insertStmt.QueryRowContext( + ctx, + f.geom.asWKB(), + epsg, + f.props.Datsta, + f.props.Datend, + f.props.Persta, + f.props.Perend, + f.props.Objnam, + f.props.Nobjnm, + f.props.Inform, + f.props.Ninfom, + f.props.Scamin, + f.props.Picrep, + f.props.Txtdsc, + f.props.Sordat, + f.props.Sorind, + f.props.Colour, + f.props.Colpat, + f.props.Condtn, + f.props.Topshp, + ).Scan(&fmid) + return err + }) + switch { + case err == sql.ErrNoRows: + outsideOrDup++ + // ignore -> filtered by responsibility area or a duplicate + case err != nil: + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + default: + features++ + } + } + return + }) + + return nil, err +}
--- a/pkg/imports/modelconvert.go Tue Feb 18 09:10:05 2020 +0100 +++ b/pkg/imports/modelconvert.go Tue Feb 18 09:10:27 2020 +0100 @@ -30,6 +30,13 @@ BCNLATJobKind: func() interface{} { return new(models.FairwayMarksImport) }, BOYCARJobKind: func() interface{} { return new(models.FairwayMarksImport) }, BOYLATJobKind: func() interface{} { return new(models.FairwayMarksImport) }, + BOYSAWJobKind: func() interface{} { return new(models.FairwayMarksImport) }, + BOYSPPJobKind: func() interface{} { return new(models.FairwayMarksImport) }, + DAYMARJobKind: func() interface{} { return new(models.FairwayMarksImport) }, + LIGHTSJobKind: func() interface{} { return new(models.FairwayMarksImport) }, + RTPBCNJobKind: func() interface{} { return new(models.FairwayMarksImport) }, + TOPMARJobKind: func() interface{} { return new(models.FairwayMarksImport) }, + NOTMRKJobKind: func() interface{} { return new(models.FairwayMarksImport) }, STJobKind: func() interface{} { return new(models.StretchImport) }, SECJobKind: func() interface{} { return new(models.SectionImport) }, DSECJobKind: func() interface{} { return new(models.SectionDelete) }, @@ -172,6 +179,83 @@ } }, + BOYSAWJobKind: func(input interface{}) interface{} { + fmi := input.(*models.FairwayMarksImport) + return &FairwayMarks{ + URL: fmi.URL, + FeatureType: fmi.FeatureType, + SortBy: nilString(fmi.SortBy), + User: nilString(fmi.User), + Password: nilString(fmi.Password), + } + }, + + BOYSPPJobKind: func(input interface{}) interface{} { + fmi := input.(*models.FairwayMarksImport) + return &FairwayMarks{ + URL: fmi.URL, + FeatureType: fmi.FeatureType, + SortBy: nilString(fmi.SortBy), + User: nilString(fmi.User), + Password: nilString(fmi.Password), + } + }, + + DAYMARJobKind: func(input interface{}) interface{} { + fmi := input.(*models.FairwayMarksImport) + return &FairwayMarks{ + URL: fmi.URL, + FeatureType: fmi.FeatureType, + SortBy: nilString(fmi.SortBy), + User: nilString(fmi.User), + Password: nilString(fmi.Password), + } + }, + + LIGHTSJobKind: func(input interface{}) interface{} { + fmi := input.(*models.FairwayMarksImport) + return &FairwayMarks{ + URL: fmi.URL, + FeatureType: fmi.FeatureType, + SortBy: nilString(fmi.SortBy), + User: nilString(fmi.User), + Password: nilString(fmi.Password), + } + }, + + RTPBCNJobKind: func(input interface{}) interface{} { + fmi := input.(*models.FairwayMarksImport) + return &FairwayMarks{ + URL: fmi.URL, + FeatureType: fmi.FeatureType, + SortBy: nilString(fmi.SortBy), + User: nilString(fmi.User), + Password: nilString(fmi.Password), + } + }, + + TOPMARJobKind: func(input interface{}) interface{} { + fmi := input.(*models.FairwayMarksImport) + return &FairwayMarks{ + URL: fmi.URL, + FeatureType: fmi.FeatureType, + SortBy: nilString(fmi.SortBy), + User: nilString(fmi.User), + Password: nilString(fmi.Password), + } + }, + + NOTMRKJobKind: func(input interface{}) interface{} { + fmi := input.(*models.FairwayMarksImport) + return &FairwayMarks{ + URL: fmi.URL, + FeatureType: fmi.FeatureType, + SortBy: nilString(fmi.SortBy), + User: nilString(fmi.User), + Password: nilString(fmi.Password), + } + }, + STJobKind: func(input interface{}) interface{} { sti := input.(*models.StretchImport) return &Stretch{
--- a/pkg/pgxutils/errors.go Tue Feb 18 09:10:05 2020 +0100 +++ b/pkg/pgxutils/errors.go Tue Feb 18 09:10:27 2020 +0100 @@ -98,6 +98,18 @@ return } } + switch err.TableName { + case "fairway_marks_bcnlat_dirimps", + "fairway_marks_daymar_dirimps", + "fairway_marks_notmrk_dirimps": + switch err.ConstraintName { + case "fairway_marks_bcnlat_dirimps_dirimp_fkey", + "fairway_marks_daymar_dirimps_dirimp_fkey", + "fairway_marks_notmrk_dirimps_dirimp_fkey": + m = "Invalid value for dirimp" + return + } + } } case uniqueViolation: switch err.SchemaName {
--- a/schema/gemma.sql Tue Feb 18 09:10:05 2020 +0100 +++ b/schema/gemma.sql Tue Feb 18 09:10:27 2020 +0100 @@ -288,8 +288,7 @@ -- TODO: Do we need name and/or definition from IENC feature catalogue? -- (see page 381 of edition 2.3) ); -INSERT INTO dirimps VALUES (1), (2), (3), (4); --- dirimp_5 left out because it cannot be used for waterway area +INSERT INTO dirimps VALUES (1), (2), (3), (4), (5); CREATE TABLE depth_references ( depth_reference varchar(4) PRIMARY KEY @@ -941,6 +940,175 @@ 0, colour, colpat, conrad, marsys, boyshp, catlam ) AS fairway_marks_boylat) )) + + -- Additional attributes for IENC feature BOYSAW + CREATE TABLE fairway_marks_boysaw ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int + ) 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_boysaw_distinct_rows + ON fairway_marks_boysaw + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp + ) AS fairway_marks_boysaw) + )) + + -- Additional attributes for IENC feature BOYSPP + CREATE TABLE fairway_marks_boyspp ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catspm varchar + ) 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_boyspp_distinct_rows + ON fairway_marks_boyspp + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, conrad, marsys, boyshp, catspm + ) AS fairway_marks_boyspp) + )) + + -- Additional attributes for IENC features DAYMAR/daymar + CREATE TABLE fairway_marks_daymar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + topshp int, + orient double precision + ) 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_daymar_distinct_rows + ON fairway_marks_daymar + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp, orient + ) AS fairway_marks_daymar) + )) + + CREATE TABLE fairway_marks_daymar_dirimps ( + fm_daymar_id int REFERENCES fairway_marks_daymar, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_daymar_id, dirimp) + ) + + -- Additional attributes for IENC feature LIGHTS + CREATE TABLE fairway_marks_lights ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + condtn int, + orient double precision, + catlit varchar, + exclit int, + litchr int, + litvis varchar, + mltylt int, + sectr1 double precision, + sectr2 double precision, + siggrp varchar, + sigper double precision, + sigseq varchar, + status varchar + ) 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_lights_distinct_rows + ON fairway_marks_lights + ((CAST((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 fairway_marks_lights) + )) + + -- Additional attributes for IENC feature RTPBCN + CREATE TABLE fairway_marks_rtpbcn ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + condtn int, + siggrp varchar, + catrtb int, + radwal varchar + ) 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_rtpbcn_distinct_rows + ON fairway_marks_rtpbcn + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, condtn, siggrp, catrtb, radwal + ) AS fairway_marks_rtpbcn) + )) + + -- Additional attributes for IENC feature TOPMAR + CREATE TABLE fairway_marks_topmar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + topshp int + ) 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_topmar_distinct_rows + ON fairway_marks_topmar + ((CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, colour, colpat, condtn, topshp + ) AS fairway_marks_topmar) + )) + + -- Additional attributes for IENC feature NOTMRK + CREATE TABLE fairway_marks_notmrk ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + condtn int, + marsys int, + orient double precision, + status varchar, + addmrk varchar, + catnmk int, + disipd double precision, + disipu double precision, + disbk1 double precision, + disbk2 double precision, + fnctnm int, + bnkwtw int + ) 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_notmrk_distinct_rows + ON fairway_marks_notmrk + ((CAST((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 fairway_marks_notmrk) + )) + + CREATE TABLE fairway_marks_notmrk_dirimps ( + fm_notmrk_id int REFERENCES fairway_marks_notmrk, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_notmrk_id, dirimp) + ) ;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1403/01.add_dirimp_5.sql Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,1 @@ +INSERT INTO dirimps VALUES (5);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1403/02.add_fairway_mark_types.sql Tue Feb 18 09:10:27 2020 +0100 @@ -0,0 +1,151 @@ +CREATE TABLE waterway.fairway_marks_boysaw ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows + ON waterway.fairway_marks_boysaw + ((CAST((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 TABLE waterway.fairway_marks_boyspp ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + conrad int, + marsys int, + boyshp int, + catspm varchar +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows + ON waterway.fairway_marks_boyspp + ((CAST((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 TABLE waterway.fairway_marks_daymar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + topshp int, + orient double precision +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_daymar_distinct_rows + ON waterway.fairway_marks_daymar + ((CAST((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 TABLE waterway.fairway_marks_daymar_dirimps ( + fm_daymar_id int REFERENCES waterway.fairway_marks_daymar, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_daymar_id, dirimp) +); + +CREATE TABLE waterway.fairway_marks_lights ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + condtn int, + orient double precision, + catlit varchar, + exclit int, + litchr int, + litvis varchar, + mltylt int, + sectr1 double precision, + sectr2 double precision, + siggrp varchar, + sigper double precision, + sigseq varchar, + status varchar +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows + ON waterway.fairway_marks_lights + ((CAST((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 TABLE waterway.fairway_marks_rtpbcn ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + condtn int, + siggrp varchar, + catrtb int, + radwal varchar +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows + ON waterway.fairway_marks_rtpbcn + ((CAST((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 TABLE waterway.fairway_marks_topmar ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + colour varchar, + colpat varchar, + condtn int, + topshp int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows + ON waterway.fairway_marks_topmar + ((CAST((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 TABLE waterway.fairway_marks_notmrk ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + condtn int, + marsys int, + orient double precision, + status varchar, + addmrk varchar, + catnmk int, + disipd double precision, + disipu double precision, + disbk1 double precision, + disbk2 double precision, + fnctnm int, + bnkwtw int +) INHERITS (waterway.fairway_marks); +CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows + ON waterway.fairway_marks_notmrk + ((CAST((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) + )); + +CREATE TABLE waterway.fairway_marks_notmrk_dirimps ( + fm_notmrk_id int REFERENCES waterway.fairway_marks_notmrk, + dirimp smallint REFERENCES dirimps, + PRIMARY KEY (fm_notmrk_id, dirimp) +); + +GRANT SELECT on ALL tables in schema waterway TO waterway_user ; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;