Mercurial > gemma

comparison schema/isrs_functions.sql @ 5520:05db984d3db1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve performance of bottleneck area calculation Avoid buffer calculations by replacing them with simple distance comparisons and calculate the boundary of the result geometry only once per iteration. In some edge cases with very large numbers of iterations, this reduced the runtime of a bottleneck import by a factor of more than twenty.
author Tom Gottfried <tom@intevation.de>
date Thu, 21 Oct 2021 19:50:39 +0200
parents 68358e4603c8
children 3cfbc5769e8b
comparison
equal deleted inserted replaced
5519:e8e1a24d2f4c 5520:05db984d3db1
88 axis_snapped AS ( 88 axis_snapped AS (
89 -- Iteratively connect non-contiguous axis chunks 89 -- Iteratively connect non-contiguous axis chunks
90 -- to find the contiguous axis on which given distance marks lie 90 -- to find the contiguous axis on which given distance marks lie
91 (SELECT ARRAY[id] AS ids, wtwaxs 91 (SELECT ARRAY[id] AS ids, wtwaxs
92 FROM axis, points 92 FROM axis, points
93 WHERE ST_Intersects( 93 WHERE ST_DWithin(axis.wtwaxs, points.geom, 0.0001)
94 ST_Buffer(axis.wtwaxs, 0.0001), points.geom)
95 FETCH FIRST ROW ONLY) 94 FETCH FIRST ROW ONLY)
96 UNION 95 UNION
97 -- Connect endpoint of next linestring with closest 96 -- Connect endpoint of next linestring with closest
98 -- endpoint of merged linestring until a contiguous 97 -- endpoint of merged linestring until a contiguous
99 -- linestring connecting both distance marks is build up 98 -- linestring connecting both distance marks is build up
101 ST_LineMerge(ST_Collect(ARRAY( 100 ST_LineMerge(ST_Collect(ARRAY(
102 -- Linestring build up so far 101 -- Linestring build up so far
103 SELECT refgeom 102 SELECT refgeom
104 UNION 103 UNION
105 -- Fill eventual gap 104 -- Fill eventual gap
106 SELECT ST_ShortestLine( 105 SELECT ST_ShortestLine(refbdr, bdr)
107 ST_Boundary(refgeom), bdr)
108 UNION 106 UNION
109 -- Linestring to be added 107 -- Linestring to be added
110 SELECT geom))) 108 SELECT geom)))
111 FROM axis_snapped AS axis_snapped (refids, refgeom), 109 FROM axis_snapped AS axis_snapped (refids, refgeom),
112 axis AS axis (id, geom, bdr), 110 axis AS axis (id, geom, bdr),
113 (SELECT ST_Collect(points.geom) AS pts 111 ST_Boundary(refgeom) AS refbdr (refbdr)
114 FROM points) AS points
115 WHERE id <> ALL(refids) 112 WHERE id <> ALL(refids)
116 AND ST_DWithin( 113 AND ST_DWithin(refbdr, bdr, tolerance)
117 ST_Boundary(refgeom), bdr, tolerance) 114 -- Stop if refgeom goes through both distance marks
118 AND NOT ST_Covers(ST_Buffer(refgeom, 0.0001), points.pts) 115 AND NOT 0.0001 >= ALL(SELECT refgeom <-> geom FROM points)
119 ORDER BY ST_Boundary(refgeom) <-> bdr 116 ORDER BY refbdr <-> bdr
120 FETCH FIRST ROW ONLY)), 117 FETCH FIRST ROW ONLY)),
121 axis_segment AS ( 118 axis_segment AS (
122 -- Fetch end result from snapping 119 -- Fetch end result from snapping
123 SELECT wtwaxs AS line 120 SELECT wtwaxs AS line
124 FROM axis_snapped, 121 FROM axis_snapped
125 (SELECT ST_Collect(points.geom) AS pts
126 FROM points) AS points
127 -- Return end result only if both distance marks were connected 122 -- Return end result only if both distance marks were connected
128 WHERE ST_Covers(ST_Buffer(wtwaxs, 0.0001), points.pts)) 123 WHERE 0.0001 >= ALL(SELECT wtwaxs <-> geom FROM points))
129 -- Use linear referencing to clip axis between distance marks. 124 -- Use linear referencing to clip axis between distance marks.
130 -- Simplification is used to work-around the problem, that 125 -- Simplification is used to work-around the problem, that
131 -- ST_LineSubstring might generate very small line segments at an 126 -- ST_LineSubstring might generate very small line segments at an
132 -- end of the resulting linestring, that significantly differ from 127 -- end of the resulting linestring, that significantly differ from
133 -- the direction of the input linestring due to finite precision 128 -- the direction of the input linestring due to finite precision