Mercurial > gemma
changeset 2091:3f32b18bbd4c
Waterway profile import: Store temp geoms in WGS84, do buffering with four decimal places and fetch the lines closest to the dmv points.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 01 Feb 2019 16:32:52 +0100 |
parents | 4882f01c8592 |
children | f70f67eaa7aa |
files | pkg/imports/wp.go |
diffstat | 1 files changed, 43 insertions(+), 67 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/imports/wp.go Fri Feb 01 13:45:02 2019 +0100 +++ b/pkg/imports/wp.go Fri Feb 01 16:32:52 2019 +0100 @@ -77,7 +77,7 @@ const ( createGeomTempTableSQL = ` CREATE TEMP TABLE wp_geoms ( - geom geometry(linestring, %d) + geom geometry(linestring, 4326) ) ON COMMIT DROP` createTempIndexSQL = ` @@ -87,12 +87,14 @@ insertGeomTmpTableSQL = ` INSERT INTO wp_geoms (geom) VALUES ( - ST_Transform(ST_GeomFromWKB($1, $2::int), %d) + ST_Transform(ST_GeomFromWKB($1, $2::int), 4326) )` insertWaterwayProfileSQL = ` WITH point AS ( - SELECT ST_Buffer(ST_Transform(geom::geometry, $14::int), 0.001) geom + SELECT + ST_Buffer(geom::geometry, 0.0001) AS geom, + geom::geometry AS point FROM waterway.distance_marks_virtual WHERE location_code = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) @@ -110,10 +112,10 @@ source_organization ) VALUES ( ($1, $2, $3, $4, $5), - ( SELECT ST_Transform(geom, 4326)::geography - FROM wp_geoms - WHERE geom && ( SELECT geom from point ) AND - ST_Intersects(geom, ( SELECT geom FROM point )) + ( SELECT wp_geoms.geom::geography + FROM wp_geoms, point + WHERE wp_geoms.geom && point.geom + ORDER BY ST_Distance(point.point, wp_geoms.geom) LIMIT 1 ), $6, @@ -147,18 +149,6 @@ return os.RemoveAll(wp.Dir) } -func parseFloat64(s string) (sql.NullFloat64, error) { - if s == "" { - return sql.NullFloat64{}, nil - } - s = strings.Replace(s, ",", ".", -1) - v, err := strconv.ParseFloat(s, 64) - if err != nil { - return sql.NullFloat64{}, err - } - return sql.NullFloat64{Float64: v, Valid: true}, nil -} - func (wp *WaterwayProfiles) Do( ctx context.Context, importID int64, @@ -173,14 +163,13 @@ } defer tx.Rollback() - epsg, err := wp.downloadGeometries( - ctx, importID, tx, start, feedback) - if err != nil { + if err := wp.downloadGeometries( + ctx, importID, tx, start, feedback); err != nil { return nil, fmt.Errorf("error downloading geometries: %v", err) } summary, err := wp.processCSV( - ctx, importID, tx, start, epsg, feedback) + ctx, importID, tx, start, feedback) if err != nil { return nil, fmt.Errorf("error processing CVS: %v", err) } @@ -202,19 +191,19 @@ tx *sql.Tx, start time.Time, feedback Feedback, -) (int, error) { +) error { feedback.Info("Start downloading geometries from WFS.") feedback.Info("Loading capabilities from %s", wp.URL) caps, err := wfs.GetCapabilities(wp.URL) if err != nil { feedback.Error("Loading capabilities failed: %v", err) - return 0, err + return err } ft := caps.FindFeatureType(wp.FeatureType) if ft == nil { - return 0, fmt.Errorf("Unknown feature type '%s'", wp.FeatureType) + return fmt.Errorf("Unknown feature type '%s'", wp.FeatureType) } feedback.Info("Found feature type '%s", wp.FeatureType) @@ -222,7 +211,7 @@ epsg, err := wfs.CRSToEPSG(ft.DefaultCRS) if err != nil { feedback.Error("Unsupported CRS name '%s'", ft.DefaultCRS) - return 0, err + return err } if wp.SortBy != "" { @@ -233,44 +222,25 @@ caps, wp.FeatureType, "application/json", wp.SortBy) if err != nil { feedback.Error("Cannot create GetFeature URLs. %v", err) - return 0, err + return err + } + + if _, err := tx.ExecContext(ctx, createGeomTempTableSQL); err != nil { + return err } + insertStmt, err := tx.PrepareContext(ctx, insertGeomTmpTableSQL) + if err != nil { + return err + } + defer insertStmt.Close() + var ( unsupported = stringCounter{} missingProperties int features int ) - var insertStmt *sql.Stmt - defer func() { - if insertStmt != nil { - insertStmt.Close() - } - }() - - var usedEPGS int - - setup := func(epsg int) error { - if insertStmt != nil { - return nil - } - feedback.Info("Using EPSG: %d", epsg) - usedEPGS = epsg - - tblSQL := fmt.Sprintf(createGeomTempTableSQL, epsg) - if _, err := tx.ExecContext(ctx, tblSQL); err != nil { - return err - } - if _, err := tx.ExecContext(ctx, createTempIndexSQL); err != nil { - return err - } - insertSQL := fmt.Sprintf(insertGeomTmpTableSQL, epsg) - var err error - insertStmt, err = tx.PrepareContext(ctx, insertSQL) - return err - } - if err := wfs.DownloadURLs(urls, func(r io.Reader) error { rfc, err := wfs.ParseRawFeatureCollection(r) if err != nil { @@ -289,10 +259,6 @@ return nil } - if err := setup(epsg); err != nil { - return err - } - for _, feature := range rfc.Features { if feature.Geometry.Coordinates == nil { missingProperties++ @@ -320,7 +286,7 @@ return nil }); err != nil { feedback.Error("Downloading features failed: %v", err) - return 0, err + return err } if missingProperties > 0 { @@ -332,12 +298,24 @@ } if features == 0 { - return 0, errors.New("No features found") + return errors.New("No features found") } if _, err := tx.ExecContext(ctx, analyzeTempTableSQL); err != nil { - return 0, err + return err } - return usedEPGS, nil + return nil +} + +func parseFloat64(s string) (sql.NullFloat64, error) { + if s == "" { + return sql.NullFloat64{}, nil + } + s = strings.Replace(s, ",", ".", -1) + v, err := strconv.ParseFloat(s, 64) + if err != nil { + return sql.NullFloat64{}, err + } + return sql.NullFloat64{Float64: v, Valid: true}, nil } func (wp *WaterwayProfiles) processCSV( @@ -345,7 +323,6 @@ importID int64, tx *sql.Tx, start time.Time, - epsg int, feedback Feedback, ) (interface{}, error) { feedback.Info("Start processing CSV file.") @@ -546,7 +523,6 @@ fe100, dateInfo, source, - epsg, ).Scan(&id); err != nil { return nil, err }