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)