Mercurial > gemma
changeset 4567:9c9786f9466f iso-areas
Merged default into iso-areas branch.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Fri, 04 Oct 2019 17:41:34 +0200 |
| parents | 2c49a293f275 (diff) 7465b6244d5e (current diff) |
| children | 459d682e39b5 |
| files | |
| diffstat | 11 files changed, 770 insertions(+), 17 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/cmd/isoareas/main.go Fri Oct 04 17:41:34 2019 +0200 @@ -0,0 +1,310 @@ +// 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) 2019 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Sascha L. Teichmann <sascha.teichmann@intevation.de> + +package main + +import ( + "bufio" + "flag" + "fmt" + "io" + "log" + "math" + "os" + "runtime" + "strconv" + "strings" + "sync" + "time" + + svg "github.com/ajstarks/svgo" + "github.com/fogleman/contourmap" + + "gemma.intevation.de/gemma/pkg/common" + "gemma.intevation.de/gemma/pkg/models" + "gemma.intevation.de/gemma/pkg/octree" +) + +const classBreaks = `1:#ff00dd,1.5,1.7,1.9,2.1,2.3,2.5:#f25f20,2.7,2.9,3.1:#f7e40e,3.3,3.5,4:#8ad51a,4.5,5,5.5,6,6.5,7:#1414ff` + +func loadXYZ(r io.Reader) (octree.MultiPointZ, error) { + + scanner := bufio.NewScanner(r) + + points := make(octree.MultiPointZ, 0, 2000000) + + var x, y, z float64 + var err error + + for scanner.Scan() { + line := strings.TrimSpace(scanner.Text()) + if len(line) == 0 || strings.HasPrefix(line, "#") { + continue + } + parts := strings.SplitN(line, " ", 3) + if len(parts) != 3 { + continue + } + + if x, err = strconv.ParseFloat(parts[0], 64); err != nil { + return nil, err + } + if y, err = strconv.ParseFloat(parts[1], 64); err != nil { + return nil, err + } + if z, err = strconv.ParseFloat(parts[2], 64); err != nil { + return nil, err + } + points = append(points, octree.Vertex{X: x, Y: y, Z: z}) + } + + return points, nil +} + +func check(err error) { + if err != nil { + log.Fatalf("error: %v\n", err) + } +} + +func dumpContoursSVG( + w io.Writer, + heights []float64, + colors models.ColorValues, + contours [][]contourmap.Contour, +) (err error) { + + var ( + minX = math.MaxFloat64 + minY = math.MaxFloat64 + maxX = -math.MaxFloat64 + maxY = -math.MaxFloat64 + ) + + for _, c := range contours { + for _, r := range c { + for _, p := range r { + if p.X < minX { + minX = p.X + } + if p.Y < minY { + minY = p.Y + } + if p.X > maxX { + maxX = p.X + } + if p.Y > maxY { + maxY = p.Y + } + } + } + } + ratio := (maxX - minX) / (maxY - minY) + + log.Printf("ratio: %.2f\n", ratio) + + const width = 50000 + height := int(math.Ceil(width * ratio)) + + px := common.Linear(minX, 0, maxX, width) + py := common.Linear(minY, float64(height), maxY, 0) + + out := bufio.NewWriter(w) + defer func() { err = out.Flush() }() + + canvas := svg.New(out) + canvas.Start(width, height) + + for j, c := range contours { + + h := heights[j] + col := colors.Clip(h) + + style := fmt.Sprintf("fill:#%02x%02x%02x", col.R, col.G, col.B) + + for _, r := range c { + x := make([]int, len(r)) + y := make([]int, len(r)) + for i, p := range r { + x[i] = int(math.Round(px(p.X))) + y[i] = int(math.Round(py(p.Y))) + } + + canvas.Polygon(x, y, style) + } + } + + canvas.End() + return +} + +func main() { + + cellSize := float64(0.5) + + flag.Float64Var(&cellSize, "cellsize", 0.5, "width/height of raster cell [m]") + + flag.Parse() + + colors, err := models.ParseColorValues(classBreaks) + check(err) + + heights := colors.Heights() + + log.Printf("num classes: %d\n", len(heights)) + start := time.Now() + xyz, err := loadXYZ(os.Stdin) + check(err) + log.Printf("loading took %v\n", time.Since(start)) + + log.Printf("num vertices: %d\n", len(xyz)) + + start = time.Now() + tri, err := octree.Triangulate(xyz) + check(err) + log.Printf("triangulation took %v\n", time.Since(start)) + tooLongEdge := tri.EstimateTooLong() + log.Printf("Eliminate triangles with edges longer than %.2f meters.", tooLongEdge) + + start = time.Now() + polygon, removed := tri.ConcaveHull(tooLongEdge) + + polygonArea := polygon.Area() + if polygonArea < 0.0 { // counter clockwise + polygonArea = -polygonArea + polygon.Reverse() + } + + var clippingPolygon octree.Polygon + clippingPolygon.FromLineStringZ(polygon) + clippingPolygon.Indexify() + + tin := tri.Tin() + // tin.EPSG = epsg + + var str octree.STRTree + str.Build(tin) + + removed = str.Clip(&clippingPolygon) + + builder := octree.NewBuilder(tin) + builder.Build(removed) + + tree := builder.Tree() + + min, max := tin.Min, tin.Max + + log.Printf("(%.2f, %.2f, %.2f) - (%.2f, %.2f, %.2f)\n", + min.X, min.Y, min.Z, + max.X, max.Y, max.Z) + + heights = octree.ExtrapolateClassBreaks(heights, min.Z, max.Z) + + width := max.X - min.X + height := max.Y - min.Y + + log.Printf("width/height: %.2f / %.2f\n", width, height) + + xcells := int(math.Ceil(width / cellSize)) + ycells := int(math.Ceil(height / cellSize)) + + log.Printf("raster size: (%d, %d)\n", xcells, ycells) + + // Add border for closing + raster := make([]float64, (xcells+2)*(ycells+2)) + + const closed = -math.MaxFloat64 + for i := range raster { + raster[i] = closed + } + + start = time.Now() + + var wg sync.WaitGroup + + rows := make(chan int) + + rasterRow := func() { + defer wg.Done() + for i := range rows { + pos := (i+1)*(xcells+2) + 1 + row := raster[pos : pos+xcells] + //log.Printf("len: %d\n", len(row)) + py := min.Y + float64(i)*cellSize + cellSize/2 + px := min.X + cellSize/2 + for j := range row { + v, ok := tree.Value(px, py) + if ok { + row[j] = v + } + px += cellSize + } + } + } + + start = time.Now() + + for n := runtime.NumCPU(); n >= 1; n-- { + wg.Add(1) + go rasterRow() + } + + for i := 0; i < ycells; i++ { + rows <- i + } + close(rows) + + wg.Wait() + + log.Printf("Rasterizing took %v.\n", time.Since(start)) + + start = time.Now() + cm := contourmap.FromFloat64s(xcells+2, ycells+2, raster) + + start = time.Now() + + contours := make([][]contourmap.Contour, len(heights)) + + cnts := make(chan int) + + doContours := func() { + defer wg.Done() + for i := range cnts { + contours[i] = cm.Contours(heights[i]) + } + } + + for n := runtime.NumCPU(); n >= 1; n-- { + wg.Add(1) + go doContours() + } + + for i := range heights { + cnts <- i + } + close(cnts) + + wg.Wait() + + log.Printf("Calculating contours took %v.\n", time.Since(start)) + check(dumpContoursSVG(os.Stdout, heights, colors, contours)) + + /* + + start = time.Now() + polygons := intersectTriangles(tri, heights, min, max) + log.Printf("intersecting triangles took %v.\n", time.Since(start)) + + check(dumpPolygonsSVG(os.Stdout, min, max, polygons)) + */ +}
--- a/go.mod Fri Oct 04 17:37:51 2019 +0200 +++ b/go.mod Fri Oct 04 17:41:34 2019 +0200 @@ -3,8 +3,10 @@ go 1.13 require ( + github.com/ajstarks/svgo v0.0.0-20180226025133-644b8db467af github.com/cockroachdb/apd v1.1.0 // indirect github.com/etcd-io/bbolt v1.3.3 + github.com/fogleman/contourmap v0.0.0-20190814184649-9f61d36c4199 github.com/gofrs/uuid v3.2.0+incompatible // indirect github.com/golang/snappy v0.0.1 github.com/gorilla/mux v1.7.3
--- a/go.sum Fri Oct 04 17:37:51 2019 +0200 +++ b/go.sum Fri Oct 04 17:41:34 2019 +0200 @@ -2,6 +2,7 @@ github.com/BurntSushi/toml v0.3.1 h1:WXkYYl6Yr3qBf1K79EBnL4mak0OimBfB0XUf9Vl28OQ= github.com/BurntSushi/toml v0.3.1/go.mod h1:xHWCNGjB5oqiDr8zfno3MHue2Ht5sIBksp03qcyfWMU= github.com/OneOfOne/xxhash v1.2.2/go.mod h1:HSdplMjZKSmBqAxg5vPj2TmRDmfkzw+cTzAElWljhcU= +github.com/ajstarks/svgo v0.0.0-20180226025133-644b8db467af h1:wVe6/Ea46ZMeNkQjjBW6xcqyQA/j5e0D6GytH95g0gQ= github.com/ajstarks/svgo v0.0.0-20180226025133-644b8db467af/go.mod h1:K08gAheRH3/J6wwsYMMT4xOr94bZjxIelGM0+d/wbFw= github.com/alecthomas/template v0.0.0-20160405071501-a0175ee3bccc/go.mod h1:LOuyumcjzFXgccqObfd/Ljyb9UuFJ6TxHnclSeseNhc= github.com/alecthomas/units v0.0.0-20151022065526-2efee857e7cf/go.mod h1:ybxpYRFXyAe+OPACYpWeL0wqObRcbAqCMya13uyzqw0= @@ -25,6 +26,8 @@ github.com/dgryski/go-sip13 v0.0.0-20181026042036-e10d5fee7954/go.mod h1:vAd38F8PWV+bWy6jNmig1y/TA+kYO4g3RSRF0IAv0no= github.com/etcd-io/bbolt v1.3.3 h1:gSJmxrs37LgTqR/oyJBWok6k6SvXEUerFTbltIhXkBM= github.com/etcd-io/bbolt v1.3.3/go.mod h1:ZF2nL25h33cCyBtcyWeZ2/I3HQOfTP+0PIEvHjkjCrw= +github.com/fogleman/contourmap v0.0.0-20190814184649-9f61d36c4199 h1:kufr0u0RIG5ACpjFsPRbbuHa0FhMWsS3tnSFZ2hf07s= +github.com/fogleman/contourmap v0.0.0-20190814184649-9f61d36c4199/go.mod h1:mqaaaP4j7nTF8T/hx5OCljA7BYWHmrH2uh+Q023OchE= github.com/fogleman/gg v1.2.1-0.20190220221249-0403632d5b90/go.mod h1:R/bRT+9gY/C5z7JzPU0zXsXHKM4/ayA+zqcVNZzPa1k= github.com/fsnotify/fsnotify v1.4.7 h1:IXs+QLmnXW2CcXuY+8Mzv/fWEsPGWxqefPtCP5CnV9I= github.com/fsnotify/fsnotify v1.4.7/go.mod h1:jwhsz4b93w/PPRr/qN1Yymfu8t87LnFCMoQvtojpjFo=
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/pkg/common/linear.go Fri Oct 04 17:41:34 2019 +0200 @@ -0,0 +1,36 @@ +// 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) 2019 by via donau +// – Österreichische Wasserstraßen-Gesellschaft mbH +// Software engineering by Intevation GmbH +// +// Author(s): +// * Sascha L. Teichmann <sascha.teichmann@intevation.de> +package common + +// Linear constructs a function which maps x1 to y1 and x2 to y2. +// All other values are interpolated linearly. +func Linear(x1, y1, x2, y2 float64) func(float64) float64 { + // f(x1) = y1 + // f(x2) = y2 + // y1 = x1*a + b <=> b = y1 - x1*a + // y2 = x2*a + b + + // y1 - y2 = a*(x1 - x2) + // a = (y1-y2)/(x1 - x2) for x1 != x2 + + if x1 == x2 { + return func(float64) float64 { + return 0.5 * (y1 + y2) + } + } + a := (y1 - y2) / (x1 - x2) + b := y1 - x1*a + return func(x float64) float64 { + return x*a + b + } +}
--- a/pkg/imports/sr.go Fri Oct 04 17:37:51 2019 +0200 +++ b/pkg/imports/sr.go Fri Oct 04 17:41:34 2019 +0200 @@ -28,15 +28,19 @@ "os" "path" "path/filepath" + "runtime" "strconv" "strings" + "sync" "time" + "github.com/fogleman/contourmap" shp "github.com/jonas-p/go-shp" "gemma.intevation.de/gemma/pkg/common" "gemma.intevation.de/gemma/pkg/models" "gemma.intevation.de/gemma/pkg/octree" + "gemma.intevation.de/gemma/pkg/wkb" ) // SoundingResult is a Job to import sounding reults @@ -70,9 +74,17 @@ ) const ( + // pointsPerSquareMeter is the average number of points + // when generating a artifical height model for single beam scans. pointsPerSquareMeter = 2 ) +const ( + // isoCellSize is the side length of a raster cell when tracing + // iso areas. + isoCellSize = 0.5 +) + // SRJobKind is the unique name of this import job type. const SRJobKind JobKind = "sr" @@ -88,7 +100,10 @@ func (srJobCreator) Depends() [2][]string { return [2][]string{ - {"sounding_results", "sounding_results_contour_lines"}, + {"sounding_results", + "sounding_results_contour_lines", + "sounding_results_iso_areas", + }, {"bottlenecks"}, } } @@ -178,6 +193,32 @@ WHERE id = $1 ` + insertIsoAreasSQL = ` +INSERT INTO waterway.sounding_results_iso_areas ( + sounding_result_id, + height, + areas +) +SELECT + $1, + $2, + ST_Transform( + ST_Multi( + ST_CollectionExtract( + ST_SimplifyPreserveTopology( + ST_Multi(ST_Collectionextract( + ST_MakeValid(ST_GeomFromWKB($4, $3::integer)), 3)), + $5 + ), + 3 + ) + ), + 4326 + ) +FROM waterway.sounding_results sr +WHERE id = $1 +` + selectGaugeLDCSQL = ` SELECT grwl.value, @@ -619,15 +660,10 @@ return nil, err } feedback.Info("Storing octree index took %s.", time.Since(start)) - feedback.Info("Generate contour lines") - - start = time.Now() - err = generateContours(ctx, tx, feedback, builder.Tree(), id) + err = generateIsos(ctx, tx, feedback, builder.Tree(), id) if err != nil { return nil, err } - feedback.Info("Generating contour lines took %s.", - time.Since(start)) // Store for potential later removal. if err = track(ctx, tx, importID, "waterway.sounding_results", id); err != nil { @@ -827,23 +863,19 @@ return shapeToPolygon(s) } -func generateContours( +func generateIsos( ctx context.Context, tx *sql.Tx, feedback Feedback, tree *octree.Tree, id int64, ) error { - stmt, err := tx.PrepareContext(ctx, insertContourSQL) - if err != nil { - return err - } - defer stmt.Close() heights, err := octree.LoadClassBreaks( ctx, tx, "morphology_classbreaks", ) + if err != nil { feedback.Warn("Loading class breaks failed: %v", err) feedback.Info("Using default class breaks") @@ -871,6 +903,212 @@ heights = common.DedupFloat64s(heights) + if err := generateContours(ctx, tx, feedback, tree, heights, id); err != nil { + return err + } + + return generateIsoAreas(ctx, tx, feedback, tree, heights, id) +} + +func generateIsoAreas( + ctx context.Context, + tx *sql.Tx, + feedback Feedback, + tree *octree.Tree, + heights []float64, + id int64, +) error { + feedback.Info("Generate iso areas") + total := time.Now() + defer func() { + feedback.Info("Generating iso areas took %s.", + time.Since(total)) + }() + + min, max := tree.Min, tree.Max + + width := max.X - min.X + height := max.Y - min.Y + + feedback.Info("Width/Height: %.2f / %.2f", width, height) + + xcells := int(math.Ceil(width / isoCellSize)) + ycells := int(math.Ceil(height / isoCellSize)) + + feedback.Info("Raster size: (%d, %d)", xcells, ycells) + + // Add border for closing + raster := make([]float64, (xcells+2)*(ycells+2)) + + // prefill for no data + const nodata = -math.MaxFloat64 + for i := range raster { + raster[i] = nodata + } + + // rasterize the height model + + var wg sync.WaitGroup + + rows := make(chan int) + + rasterRow := func() { + defer wg.Done() + for i := range rows { + pos := (i+1)*(xcells+2) + 1 + row := raster[pos : pos+xcells] + py := min.Y + float64(i)*isoCellSize + isoCellSize/2 + px := min.X + isoCellSize/2 + for j := range row { + v, ok := tree.Value(px, py) + if ok { + row[j] = v + } + px += isoCellSize + } + } + } + + start := time.Now() + + for n := runtime.NumCPU(); n >= 1; n-- { + wg.Add(1) + go rasterRow() + } + + for i := 0; i < ycells; i++ { + rows <- i + } + close(rows) + + wg.Wait() + + feedback.Info("Rasterizing took %v.", time.Since(start)) + + feedback.Info("Trace iso areas.") + + start = time.Now() + + tracer := contourmap.FromFloat64s(xcells+2, ycells+2, raster) + + areas := make([]wkb.MultiPolygonGeom, len(heights)) + + // TODO: Check if this correct! + reprojX := common.Linear(1, min.X, float64(xcells+1), max.X) + reprojY := common.Linear(1, min.Y, float64(ycells+1), max.Y) + + cnts := make(chan int) + + doContours := func() { + defer wg.Done() + for hIdx := range cnts { + c := tracer.Contours(heights[hIdx]) + + if len(c) == 0 { + continue + } + + // We need to bring it back to the + // none raster coordinate system. + a := make(wkb.MultiPolygonGeom, len(c)) + + for i, pl := range c { + shell := make(wkb.LinearRingGeom, len(pl)) + for j, pt := range pl { + shell[j] = wkb.PointGeom{ + X: reprojX(pt.X), + Y: reprojY(pt.Y), + } + } + a[i] = wkb.PolygonGeom{shell} + } + + areas[hIdx] = a + } + } + + for n := runtime.NumCPU(); n >= 1; n-- { + wg.Add(1) + go doContours() + } + + for i := range heights { + cnts <- i + } + close(cnts) + + wg.Wait() + + feedback.Info("Tracing areas took %v.", time.Since(start)) + + // Raster and tracer are not needed any more. + raster, tracer = nil, nil + + return storeAreas( + ctx, tx, feedback, + areas, tree.EPSG, heights, id) +} + +func storeAreas( + ctx context.Context, + tx *sql.Tx, + feedback Feedback, + areas []wkb.MultiPolygonGeom, + epsg uint32, + heights []float64, + id int64, +) error { + feedback.Info("Store iso areas.") + total := time.Now() + defer func() { + feedback.Info("Storing iso areas took %v.", + time.Since(total)) + }() + + stmt, err := tx.PrepareContext(ctx, insertIsoAreasSQL) + if err != nil { + return err + } + defer stmt.Close() + + for i, a := range areas { + if len(a) == 0 { + continue + } + if _, err := stmt.ExecContext( + ctx, + id, heights[i], epsg, + a.AsWKB(), + contourTolerance, + ); err != nil { + return err + } + } + + return nil +} + +func generateContours( + ctx context.Context, + tx *sql.Tx, + feedback Feedback, + tree *octree.Tree, + heights []float64, + id int64, +) error { + feedback.Info("Generate contour lines") + start := time.Now() + defer func() { + feedback.Info("Generating contour lines took %v", + time.Since(start)) + }() + + stmt, err := tx.PrepareContext(ctx, insertContourSQL) + if err != nil { + return err + } + defer stmt.Close() + octree.DoContours(tree, heights, func(res *octree.ContourResult) { if err == nil && len(res.Lines) > 0 { _, err = stmt.ExecContext(
--- a/pkg/models/colors.go Fri Oct 04 17:37:51 2019 +0200 +++ b/pkg/models/colors.go Fri Oct 04 17:41:34 2019 +0200 @@ -71,6 +71,28 @@ return cbs } +func (cc ColorValues) Heights() []float64 { + heights := make([]float64, len(cc)) + for i := range cc { + heights[i] = cc[i].Value + } + return heights +} + +func (cc ColorValues) Clip(v float64) color.RGBA { + if len(cc) == 0 { + return color.RGBA{} + } + if v < cc[0].Value { + return cc[0].Color + } + if v > cc[len(cc)-1].Value { + return cc[len(cc)-1].Color + } + c, _ := cc.Interpolate(v) + return c +} + func (cc ColorValues) Interpolate(v float64) (color.RGBA, bool) { if len(cc) == 0 || v < cc[0].Value || v > cc[len(cc)-1].Value { return color.RGBA{}, false
--- a/pkg/octree/vertex.go Fri Oct 04 17:37:51 2019 +0200 +++ b/pkg/octree/vertex.go Fri Oct 04 17:41:34 2019 +0200 @@ -350,9 +350,9 @@ } func (t *Triangle) Contains(x, y float64) bool { - v0 := t[2].Sub(t[0]) - v1 := t[1].Sub(t[0]) - v2 := Vertex{X: x, Y: y}.Sub(t[0]) + v0 := t[2].Sub2D(t[0]) + v1 := t[1].Sub2D(t[0]) + v2 := Vertex{X: x, Y: y}.Sub2D(t[0]) dot00 := v0.Dot2(v0) dot01 := v0.Dot2(v1) @@ -483,6 +483,14 @@ math.Abs(v.Y-w.Y) < eps && math.Abs(v.Z-w.Z) < eps } +// EpsEquals returns true if v and w are equal component-wise +// in the X/Y plane with the values within a epsilon range. +func (v Vertex) EpsEquals2D(w Vertex) bool { + const eps = 1e-5 + return math.Abs(v.X-w.X) < eps && + math.Abs(v.Y-w.Y) < eps +} + // JoinOnLine joins the the elements of a given multi line string // under the assumption that the segments are all on the line segment // from (x1, y1) to (x2, y2). @@ -629,6 +637,15 @@ return buf.Bytes() } +func (ls LineStringZ) CCW() bool { + var sum float64 + for i, v1 := range ls { + v2 := ls[(i+1)%len(ls)] + sum += (v2.X - v1.X) * (v2.Y + v1.Y) + } + return sum > 0 +} + // Join joins two lines leaving the first of the second out. func (ls LineStringZ) Join(other LineStringZ) LineStringZ { nline := make(LineStringZ, len(ls)+len(other)-1)
--- a/schema/gemma.sql Fri Oct 04 17:37:51 2019 +0200 +++ b/schema/gemma.sql Fri Oct 04 17:41:34 2019 +0200 @@ -699,6 +699,16 @@ -- CHECK(ST_IsSimple(CAST(lines AS geometry))), PRIMARY KEY (sounding_result_id, height) ) + + CREATE TABLE sounding_results_iso_areas ( + sounding_result_id int NOT NULL REFERENCES sounding_results + ON DELETE CASCADE, + height numeric NOT NULL, + areas geography(MULTIPOLYGON, 4326) NOT NULL, + -- TODO: generate valid simple features and add constraint: + -- CHECK(ST_IsSimple(CAST(areas AS geometry))), + PRIMARY KEY (sounding_result_id, height) + ) -- -- Fairway availability --
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1204/01.create-iso-areas.sql Fri Oct 04 17:41:34 2019 +0200 @@ -0,0 +1,16 @@ +CREATE TABLE waterway.sounding_results_iso_areas ( + sounding_result_id int NOT NULL REFERENCES waterway.sounding_results + ON DELETE CASCADE, + height numeric NOT NULL, + areas geography(MULTIPOLYGON, 4326) NOT NULL, + -- TODO: generate valid simple features and add constraint: + -- CHECK(ST_IsSimple(CAST(areas AS geometry))), + PRIMARY KEY (sounding_result_id, height) +); + +GRANT INSERT, UPDATE, DELETE ON waterway.sounding_results_iso_areas + TO waterway_admin; + +GRANT SELECT ON waterway.sounding_results_iso_areas + TO waterway_user; +
--- a/schema/version.sql Fri Oct 04 17:37:51 2019 +0200 +++ b/schema/version.sql Fri Oct 04 17:41:34 2019 +0200 @@ -1,1 +1,1 @@ -INSERT INTO gemma_schema_version(version) VALUES (1203); +INSERT INTO gemma_schema_version(version) VALUES (1204);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/style-templates/sounding_results_area.sld-template Fri Oct 04 17:41:34 2019 +0200 @@ -0,0 +1,99 @@ +<?xml version="1.0" encoding="UTF-8"?> +<StyledLayerDescriptor + xmlns="http://www.opengis.net/sld" + xmlns:se="http://www.opengis.net/se" + xmlns:ogc="http://www.opengis.net/ogc" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://www.opengis.net/sld http://schemas.opengis.net/sld/1.1.0/StyledLayerDescriptor.xsd" + version="1.1.0"> + <NamedLayer> + <se:Name>sounding_results_areas</se:Name> + <UserStyle> + <se:Name>sounding_results_areas</se:Name> + <se:FeatureTypeStyle> + <se:Name>area_colours</se:Name> + <se:Description> + <se:Abstract> + FeatureTypeStyle defining colour classes for height attribute + </se:Abstract> + </se:Description> + {{ range . -}} + <se:Rule> + {{- if not .HasLow }} + <se:Name>≤ {{ printf "%g" .High }}</se:Name> + <ogc:Filter> + <ogc:PropertyIsLessThanOrEqualTo> + <ogc:PropertyName>height</ogc:PropertyName> + <ogc:Literal>{{ printf "%f" .High }}</ogc:Literal> + </ogc:PropertyIsLessThanOrEqualTo> + </ogc:Filter> + {{- else if not .HasHigh }} + <se:Name>> {{ printf "%g" .Low }}</se:Name> + <ogc:Filter> + <ogc:PropertyIsGreaterThanOrEqualTo> + <ogc:PropertyName>height</ogc:PropertyName> + <ogc:Literal>{{ printf "%f" .Low }}</ogc:Literal> + </ogc:PropertyIsGreaterThanOrEqualTo> + </ogc:Filter> + {{- else }} + <se:Name>≤ {{ printf "%g" .High }}</se:Name> + <ogc:Filter> + <ogc:And> + <ogc:PropertyIsGreaterThan> + <ogc:PropertyName>height</ogc:PropertyName> + <ogc:Literal>{{ printf "%f" .Low }}</ogc:Literal> + </ogc:PropertyIsGreaterThan> + <ogc:PropertyIsLessThanOrEqualTo> + <ogc:PropertyName>height</ogc:PropertyName> + <ogc:Literal>{{ printf "%f" .High }}</ogc:Literal> + </ogc:PropertyIsLessThanOrEqualTo> + </ogc:And> + </ogc:Filter> + {{- end }} + <se:PolygonSymbolizer> + <se:Fill> + <se:SvgParameter name="fill">{{ .Color }}</se:SvgParameter> + </se:Fill> + <se:Stroke> + <se:SvgParameter name="stroke">#404040</se:SvgParameter> + <se:SvgParameter name="stroke-width">0.5</se:SvgParameter> + </se:Stroke> + </se:PolygonSymbolizer> + </se:Rule> + {{ end }} + </se:FeatureTypeStyle> + <se:FeatureTypeStyle> + <se:Name>area_labels</se:Name> + <se:Description> + <se:Abstract> + FeatureTypeStyle for labels at colour areas + </se:Abstract> + </se:Description> + <se:Rule> + <!-- + <se:MaxScaleDenominator>5e3</se:MaxScaleDenominator> + --> + <se:TextSymbolizer> + <se:Label> + <ogc:PropertyName>height</ogc:PropertyName> + </se:Label> + <se:Font> + <se:SvgParameter name="font-family">Avenir</se:SvgParameter> + <se:SvgParameter name="font-family">Helvetica</se:SvgParameter> + <se:SvgParameter name="font-family">Arial</se:SvgParameter> + <se:SvgParameter name="font-family">sans-serif</se:SvgParameter> + </se:Font> + <se:LabelPlacement> + <se:LinePlacement> + <se:PerpendicularOffset>5</se:PerpendicularOffset> + </se:LinePlacement> + </se:LabelPlacement> + <se:Fill> + <se:SvgParameter name="fill">#070707</se:SvgParameter> + </se:Fill> + </se:TextSymbolizer> + </se:Rule> + </se:FeatureTypeStyle> + </UserStyle> + </NamedLayer> +</StyledLayerDescriptor>