Mercurial > gemma
changeset 654:baa31814717c
Cross sections: Commit data model for fast cross section calculation.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 14 Sep 2018 16:21:44 +0200 |
parents | 7aeacd7f150b |
children | 014ee3beb329 |
files | pkg/controllers/cross.go schema/gemma.sql |
diffstat | 2 files changed, 113 insertions(+), 12 deletions(-) [+] |
line wrap: on
line diff
--- a/pkg/controllers/cross.go Fri Sep 14 12:01:45 2018 +0200 +++ b/pkg/controllers/cross.go Fri Sep 14 16:21:44 2018 +0200 @@ -2,22 +2,29 @@ import ( "database/sql" + "log" "net/http" + "time" "gemma.intevation.de/gemma/pkg/models" ) -// TODO: This is hardcoded on SLT's model of a concrete sounding result. const crossSQL = ` -SELECT ST_AsBinary((ST_Dump(ST_Multi(ST_3DIntersection( +WITH line AS ( +SELECT ST_3DIntersection( ST_Translate( ST_Extrude( ST_GeomFromWKB($1, 4326), - 0, 0, 100), - 0, 0, -150), - geom)))).geom) -FROM b442017 -WHERE ST_Intersects(geom, ST_GeomFromWKB($1, 4326)) + 0, 0, 1000), + 0, 0, -500), + geom) AS geom +FROM waterway.meshes m JOIN waterway.sounding_results sr ON m.sounding_result_id = sr.id +WHERE ST_Intersects(geom, ST_GeomFromWKB($1, 4326)) AND + sr.bottleneck_id = $2 AND sr.date_info = $3 +) +SELECT ST_AsBinary((ST_Dump(ST_Intersection(line.geom, sr.area::geometry))).geom) + FROM line, waterway.sounding_results sr + WHERE sr.bottleneck_id = $2 AND sr.date_info = $3 ` func crossSection( @@ -28,14 +35,16 @@ csi := input.(*models.CrossSectionInput) - // TODO: Use these properties in query - _ = csi.Properties.Bottleneck - _ = csi.Properties.Date - var rows *sql.Rows + start := time.Now() + rows, err = db.QueryContext( - req.Context(), crossSQL, csi.Geometry.Coordinates.AsWKB()) + req.Context(), + crossSQL, + csi.Geometry.Coordinates.AsWKB(), + csi.Properties.Bottleneck, + csi.Properties.Date.Time) if err != nil { return } @@ -55,8 +64,20 @@ return } + log.Printf("query took %v\n", time.Since(start)) + joined := segments.Join() + /* + if err2 := dumpProfile( + csi.Geometry.Coordinates[0], + csi.Geometry.Coordinates[len(csi.Geometry.Coordinates)-1], + joined, + ); err2 != nil { + log.Printf("error: %v\n", err2) + } + */ + jr = JSONResult{ Result: &models.CrossSectionOutput{ Type: "Feature", @@ -70,3 +91,75 @@ return } + +/* +func dumpProfile( + start models.GeoJSONCoordinate, + stop models.GeoJSONCoordinate, + segments models.GeoJSONMultiLineCoordinatesZ, +) error { + + w, err := os.Create("/tmp/cross.txt") + if err != nil { + return err + } + defer w.Close() + + begin := models.GeoJSONCoordinateZ{ + Lat: start.Lat, + Lon: start.Lon, + } + end := models.GeoJSONCoordinateZ{ + Lat: stop.Lat, + Lon: stop.Lon, + } + + last := begin + + var total float64 + + minz := 10000.0 + + for _, line := range segments { + for _, coord := range line { + if coord.Z < minz { + minz = coord.Z + } + total += last.Distance(coord) + last = coord + } + } + + log.Printf("length: %.3f minz: %f\n", total, minz) + + var pos float64 + + for i, line := range segments { + for j, coord := range line { + if i == 0 && j == 0 { + if !begin.Equals(coord) { + pos = begin.Distance(coord) + fmt.Fprintf(w, "%.3f %f\n", 0.0, 200.0) + fmt.Fprintf(w, "%.3f %f\n", pos-0.0001, 200.0) + fmt.Fprintf(w, "%.3f %f\n", pos, coord.Z-minz) + continue + } + } else if j == 0 { + fmt.Fprintf(w, "%.3f %f\n", pos+0.0001, 200.0) + fmt.Fprintf(w, "%.3f %f\n", pos+last.Distance(coord)-0.0001, 200.0) + continue + } + pos += last.Distance(coord) + fmt.Fprintf(w, "%.3f %f\n", pos, coord.Z-minz) + last = coord + } + } + + if !last.Equals(end) { + fmt.Fprintf(w, "%.3f %f\n", pos+0.0001, 200.0) + fmt.Fprintf(w, "%.3f %f\n", pos+last.Distance(end), 200.0) + } + + return nil +} +*/
--- a/schema/gemma.sql Fri Sep 14 12:01:45 2018 +0200 +++ b/schema/gemma.sql Fri Sep 14 16:21:44 2018 +0200 @@ -370,6 +370,7 @@ ) CREATE TABLE sounding_results ( + id int serial NOT NULL UNIQUE, bottleneck_id varchar NOT NULL REFERENCES bottlenecks, date_info date NOT NULL, PRIMARY KEY (bottleneck_id, date_info), @@ -383,6 +384,13 @@ staging_done boolean NOT NULL DEFAULT false ) + CREATE TABLE meshes ( + sounding_result_id int NOT NULL REFERENCES sounding_results(id), + geom geometry(polygonz) NOT NULL + ) + + CREATE INDEX meshes_gix ON meshes USING gist(geom); + -- -- Fairway availability --