STFindPointByRatio
STFindPointByRatio — Returns (possibly offset) point geometry at supplied length ratio along linestring.
Function Specification.
Function [lrs].[STFindPointByRatio] (
@p_linestring geometry,
@p_ratio Float,
@p_offset Float = 0.0,
@p_radius_check int = 1,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
Description.
Given a ratio (0 to 1.0), this function returns a geometry point at the position described by that ratio.
Ratio is combined with length, so @p_ratio of 1.0 is equivalent to @p_linestring.STLength() ie @p_linestring.STEndPoint().
For example, @p_ratio value of 0.5 returns point at exact midpoint of linestring (ct centroid).
If a non-zero/null value is supplied for @p_offset, the found point is offset (perpendicular to line) to the left (if @p_offset < 0) or to the right (if @p_offset > 0).
If the offset is greater than the radius inside a CircularString: 1 causes NULL to be returned; 2 returns centre point; 0 returns the offset point regardless.
The returned point has its ordinate values rounded using the supplied @p_round_xy/@p_round_zm decimal place values.
Notes.
Supports LineStrings with CircularString elements.
Wrapper over [lrs].[STFindPointByLength]
Parameters.
@p_linestring (geometry) - Linestring (including CircularString) geometry.
@p_ratio (float) - Length ratio between 0.0 and 1.0. If Null, @p_linestring is returned.
@p_offset (float) - Offset (distance) value left (negative) or right (positive) in STSrid units.
@p_radius_check (int) - If the offset is greater than the radius inside a CircularString: 1 causes NULL to be returned; 2 returns centre point; 0 returns the offset point regardless.
@p_round_xy (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_zm (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.
Result.
This function returns a point geometry at the provided length ratio from the start, with optional offset to left or right.
Example.
-- LineStrnig Test
select f.ratio,
o.IntValue as offset,
[lrs].[STFindPointByRatio] (
/* @p_linestring */ geometry::STGeomFromText('LINESTRING(-4 -4 0 1,0 0 0 5.6,10 0 0 15.61,10 10 0 25.4)',0),
/* @p_ratio */ f.ratio,
/* @p_offset */ o.IntValue,
/* @p_radius_check */ 1,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2
).AsTextZM() as fPoint
from (select /* @p_ratio */ 0.01 * CAST(t.IntValue as numeric) as ratio
from [dbo].[Generate_Series](1,100,10) as t
) as f
cross apply
[dbo].[generate_series](-1,1,1) as o
order by f.ratio
GO
ratio offset fPoint
0.01 -1 POINT (-4.526 -3.112 0 1.05)
0.01 0 POINT (-3.819 -3.819 0 1.05)
0.01 1 POINT (-3.112 -4.526 0 1.05)
0.11 -1 POINT (-2.711 -1.297 0 1.51)
0.11 0 POINT (-2.004 -2.004 0 1.51)
0.11 1 POINT (-1.297 -2.711 0 1.51)
0.21 -1 POINT (-0.897 0.517 0 1.97)
0.21 0 POINT (-0.19 -0.19 0 1.97)
0.21 1 POINT (0.517 -0.897 0 1.97)
0.31 -1 POINT (2.297 1 0 6.5)
0.31 0 POINT (2.297 0 0 6.5)
0.31 1 POINT (2.297 -1 0 6.5)
0.41 -1 POINT (4.862 1 0 7.5)
0.41 0 POINT (4.862 0 0 7.5)
0.41 1 POINT (4.862 -1 0 7.5)
0.51 -1 POINT (7.428 1 0 8.5)
0.51 0 POINT (7.428 0 0 8.5)
0.51 1 POINT (7.428 -1 0 8.5)
0.61 -1 POINT (9.994 1 0 9.5)
0.61 0 POINT (9.994 0 0 9.5)
0.61 1 POINT (9.994 -1 0 9.5)
0.71 -1 POINT (9 2.56 0 16.59)
0.71 0 POINT (10 2.56 0 16.59)
0.71 1 POINT (11 2.56 0 16.59)
0.81 -1 POINT (9 5.125 0 17.57)
0.81 0 POINT (10 5.125 0 17.57)
0.81 1 POINT (11 5.125 0 17.57)
0.91 -1 POINT (9 7.691 0 18.54)
0.91 0 POINT (10 7.691 0 18.54)
0.91 1 POINT (11 7.691 0 18.54)
select f.ratio,
o.IntValue as offset,
[lrs].[STFindPointByRatio] (
/* @p_linestring */ geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0),
/* @p_ratio */ f.ratio,
/* @p_offset */ o.IntValue,
/* @p_radius_check */ 1,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2
).AsTextZM() as fPoint
from (select /* @p_ratio */ 0.01 * CAST(t.IntValue as numeric) as ratio
from [dbo].[Generate_Series](1,100,10) as t
) as f
cross apply
[dbo].[generate_series](-1,1,1) as o
order by f.ratio
GO
ratio offset fPoint
0.01 -1 POINT (3.219 7.324)
0.01 0 POINT (2.816 6.409)
0.01 1 POINT (2.414 5.493)
0.11 -1 POINT (1.001 7.937)
0.11 0 POINT (0.876 6.945)
0.11 1 POINT (0.751 5.953)
0.21 -1 POINT (-1.299 7.894)
0.21 0 POINT (-1.137 6.907)
0.21 1 POINT (-0.974 5.92)
0.31 -1 POINT (-2.07 6.698)
0.31 0 POINT (-2.974 6.269)
0.31 1 POINT (-3.878 5.84)
0.41 -1 POINT (-1.204 4.873)
0.41 0 POINT (-2.108 4.444)
0.41 1 POINT (-3.012 4.015)
0.51 -1 POINT (-0.338 3.048)
0.51 0 POINT (-1.242 2.619)
0.51 1 POINT (-2.146 2.19)
0.61 -1 POINT (0.528 1.223)
0.61 0 POINT (-0.376 0.794)
0.61 1 POINT (-1.28 0.365)
0.71 -1 POINT (-0.415 1.46)
0.71 0 POINT (0.489 1.031)
0.71 1 POINT (1.393 0.602)
0.81 -1 POINT (0.451 3.285)
0.81 0 POINT (1.355 2.856)
0.81 1 POINT (2.259 2.427)
0.91 -1 POINT (1.317 5.111)
0.91 0 POINT (2.221 4.682)
0.91 1 POINT (3.125 4.253)