Mercurial > gemma
changeset 4938:9f9d72a1d398 fairway-marks-import
Save some typos and wrong parameter counts when adding new fairway mark types
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 17 Feb 2020 14:47:54 +0100 |
parents | 40da1b8aba01 |
children | 39b67b910204 c4d84be3a476 |
files | pkg/imports/fm.go pkg/imports/fm_bcnlat.go pkg/imports/fm_boycar.go pkg/imports/fm_boylat.go |
diffstat | 4 files changed, 74 insertions(+), 134 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/imports/fm.go Mon Feb 17 12:24:00 2020 +0100 +++ b/pkg/imports/fm.go Mon Feb 17 14:47:54 2020 +0100 @@ -19,6 +19,7 @@ "encoding/json" "fmt" "io" + "strings" "time" "gemma.intevation.de/gemma/pkg/wfs" @@ -58,6 +59,62 @@ Sorind *string `json:"hydro_sorind"` } +const ( + // Format string to be completed with type and additional attributes + insertFairwayMarkSQL = ` +WITH a AS ( + SELECT users.current_user_area_utm() AS a +) +INSERT INTO waterway.fairway_marks_%s ( + geom, + datsta, + datend, + persta, + perend, + objnam, + nobjnm, + inform, + ninfom, + scamin, + picrep, + txtdsc, + sordat, + sorind, + %s +) +SELECT newfm, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, + %s + FROM ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326) AS newfm (newfm) + WHERE pg_has_role('sys_admin', 'MEMBER') + OR ST_Intersects((select a from a), + ST_Transform(newfm, (select ST_SRID(a) from a))) +ON CONFLICT ( + CAST((geom, + datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, + scamin, picrep, txtdsc, sordat, sorind, + 0, %[2]s + ) AS waterway.fairway_marks_%[1]s) + ) + DO NOTHING +RETURNING id +` +) + +// Create INSERT statement for specific fairway marks type +func getFMInsertSQL(fmType string, attributes []string) string { + attNums := "$16" + for i := 1; i < len(attributes); i++ { + attNums += fmt.Sprintf(",$%d", 16+i) + } + + return fmt.Sprintf( + insertFairwayMarkSQL, + fmType, + strings.Join(attributes, ","), + attNums, + ) +} + // Common operation of FM imports to get features from WFS service func getFMFeatures( ctx context.Context,
--- a/pkg/imports/fm_bcnlat.go Mon Feb 17 12:24:00 2020 +0100 +++ b/pkg/imports/fm_bcnlat.go Mon Feb 17 14:47:54 2020 +0100 @@ -80,47 +80,6 @@ } const ( - insertBCNLATSQL = ` -with a as ( - select users.current_user_area_utm() AS a -) -INSERT INTO waterway.fairway_marks_bcnlat ( - geom, - datsta, - datend, - persta, - perend, - objnam, - nobjnm, - inform, - ninfom, - scamin, - picrep, - txtdsc, - sordat, - sorind, - colour, - colpat, - condtn, - bcnshp, - catlam -) -SELECT newfm, $3, $4, $5, $6, $7, $8, $9, - $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 - FROM ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326) AS newfm (newfm) - WHERE pg_has_role('sys_admin', 'MEMBER') - OR ST_Intersects((select a from a), - ST_Transform(newfm, (select ST_SRID(a) from a))) -ON CONFLICT ( - CAST((geom, - datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, - scamin, picrep, txtdsc, sordat, sorind, - 0, colour, colpat, condtn, bcnshp, catlam - ) AS waterway.fairway_marks_bcnlat) - ) - DO NOTHING -RETURNING id -` insertDirimpSQL = ` INSERT INTO waterway.fairway_marks_bcnlat_dirimps (fm_bcnlat_id, dirimp) VALUES ($1, $2) @@ -150,7 +109,11 @@ feedback.Info("Store fairway marks of type BCNLAT/bcnlat") - insertStmt, err := tx.PrepareContext(ctx, insertBCNLATSQL) + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("bcnlat", []string{ + "colour", "colpat", "condtn", "bcnshp", "catlam"}), + ) if err != nil { return }
--- a/pkg/imports/fm_boycar.go Mon Feb 17 12:24:00 2020 +0100 +++ b/pkg/imports/fm_boycar.go Mon Feb 17 14:47:54 2020 +0100 @@ -77,51 +77,6 @@ props *boycarProperties } -const ( - insertBOYCARSQL = ` -with a as ( - select users.current_user_area_utm() AS a -) -INSERT INTO waterway.fairway_marks_boycar ( - geom, - datsta, - datend, - persta, - perend, - objnam, - nobjnm, - inform, - ninfom, - scamin, - picrep, - txtdsc, - sordat, - sorind, - colour, - colpat, - conrad, - marsys, - boyshp, - catcam -) -SELECT newfm, $3, $4, $5, $6, $7, $8, $9, - $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 - FROM ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326) AS newfm (newfm) - WHERE pg_has_role('sys_admin', 'MEMBER') - OR ST_Intersects((select a from a), - ST_Transform(newfm, (select ST_SRID(a) from a))) -ON CONFLICT ( - CAST((geom, - datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, - scamin, picrep, txtdsc, sordat, sorind, - 0, colour, colpat, conrad, marsys, boyshp, catcam - ) AS waterway.fairway_marks_boycar) - ) - DO NOTHING -RETURNING id -` -) - // Do executes the actual import. func (fm *Boycar) Do( ctx context.Context, @@ -145,7 +100,12 @@ feedback.Info("Store fairway marks of type BOYCAR") - insertStmt, err := tx.PrepareContext(ctx, insertBOYCARSQL) + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("boycar", []string{ + "colour", "colpat", "conrad", + "marsys", "boyshp", "catcam"}), + ) if err != nil { return }
--- a/pkg/imports/fm_boylat.go Mon Feb 17 12:24:00 2020 +0100 +++ b/pkg/imports/fm_boylat.go Mon Feb 17 14:47:54 2020 +0100 @@ -79,51 +79,6 @@ props *boylatProperties } -const ( - insertBOYLATSQL = ` -with a as ( - select users.current_user_area_utm() AS a -) -INSERT INTO waterway.fairway_marks_boylat ( - geom, - datsta, - datend, - persta, - perend, - objnam, - nobjnm, - inform, - ninfom, - scamin, - picrep, - txtdsc, - sordat, - sorind, - colour, - colpat, - conrad, - marsys, - boyshp, - catlam -) -SELECT newfm, $3, $4, $5, $6, $7, $8, $9, - $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21 - FROM ST_Transform(ST_GeomFromWKB($1, $2::integer), 4326) AS newfm (newfm) - WHERE pg_has_role('sys_admin', 'MEMBER') - OR ST_Intersects((select a from a), - ST_Transform(newfm, (select ST_SRID(a) from a))) -ON CONFLICT ( - CAST((geom, - datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, - scamin, picrep, txtdsc, sordat, sorind, - 0, colour, colpat, conrad, marsys, boyshp, catlam - ) AS waterway.fairway_marks_boylat) - ) - DO NOTHING -RETURNING id -` -) - // Do executes the actual import. func (fm *Boylat) Do( ctx context.Context, @@ -147,7 +102,12 @@ feedback.Info("Store fairway marks of type BOYLAT") - insertStmt, err := tx.PrepareContext(ctx, insertBOYLATSQL) + insertStmt, err := tx.PrepareContext( + ctx, + getFMInsertSQL("boylat", []string{ + "colour", "colpat", "conrad", + "marsys", "boyshp", "catlam"}), + ) if err != nil { return }