Finding Points on SQL Server Spatial LineStrings using STFindPointByLength

STFindPointByLength — Returns (possibly offset) point geometry at supplied distance along linestring.

Function Specification.

Function [lrs].[STFindPointByLength] (
                  @p_linestring   geometry,
                  @p_length       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 length (0 to @p_linestring.STLength()), this function returns a geometry point at the position described by that length.

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 a generated point is on the side of the centre of a CircularString ie offset > radius:
0 returns the offset point regardless.
1 causes NULL to be returned;
2 returns centre point;

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.

Parameters.

    @p_linestring (geometry) - Linestring geometry.
    @p_length        (float) - Length defining position of point to be located. Valid values between 0.0 and @p_linestring.STLength()
    @p_offset        (float) - Offset (distance) value left (negative) or right (positive) in SRID units.
    @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 distance from start, offset to left or right.

Example.

with data as (
  select geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0) as linestring
)
select g.intValue as length,
       offset.IntValue as offset,
       [lrs].[STFindPointByLength](a.linestring,g.IntValue,offset.IntValue,0,3,2).AsTextZM() as fPoint
  from data as a
       cross apply
       [dbo].[generate_series](0,a.lineString.STLength(),a.linestring.STLength() / 4.0 ) as g
       cross apply
       [dbo].[generate_series](-1,1,1) as offset
order by offset, length
GO

length offset fPoint
0      -1     POINT (2.571 5.421)
5      -1     POINT (-1.608 5.78)
10     -1     POINT (-0.468 3.321)
15     -1     POINT (-0.133 2.055)
20     -1     POINT (2.01 6.572)
0      0      POINT (3 6.3246)
5      0      POINT (-1.876 6.744)
10     0      POINT (-1.372 2.892)
15     0      POINT (0.771 1.626)
20     0      POINT (2.914 6.143)
0      1      POINT (3.429 7.228)
5      1      POINT (-2.144 7.707)
10     1      POINT (-2.276 2.463)
15     1      POINT (1.675 1.197)
20     1      POINT (3.818 5.714)

Here is a visual example based on generating points every 500 meters (on a complex linestring) and buffering each point by 100 meters.

with data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
CIRCULARSTRING (2172207.1209 256989.8612, 2172337.527376 257437.964868, 2172663.831673 257771.623755),
(2172663.831673 257771.623755, 2173053.905662 258011.323979),
CIRCULARSTRING (2173053.905662 258011.323979,2173287.011364 258189.963583, 2173478.707188 258412.456477, 2173748.779276 258973.841139, 2173828.656546 259591.669023),
(2173828.656546 259591.669023, 2173758.293992 261836.179864),
CIRCULARSTRING (2173758.293992 261836.179864, 2173725.783967 262165.54628, 2173649.531592 262487.609494),
(2173649.531592 262487.609494, 2173560.781426 262776.413808),
CIRCULARSTRING (2173560.781426 262776.413808, 2173487.327886 263024.518797, 2173420.504432 263274.491064),
(2173420.504432 263274.491064, 2173069.137254 264662.682722, 2172750.151745 265922.939936))',2274) as geom
)
select -1 as dist, a.geom from data as a union all
select g.IntValue,
       [lrs].[STFindPointByLength] ( a.geom,g.IntValue,0.0,1,3,3).STBuffer(100) as fl
  from data as a
       cross apply
       dbo.Generate_Series(0,a.geom.STLength(),500) as g;
go

Here is an example showing how circular string offsets are handled using @p_radius_check of 2 (CircularString has a radius of 12.5m).

with data as (
select geometry::STGeomFromText('CIRCULARSTRING (0 0,10 5, 20 0, 30 -5, 40 0)',2274) as geom
)
select cogo.STFindCircleFromArc(b.geom).Z, b.geom from data as b union all
select g.IntValue,
        [lrs].[STFindPointByLength] (a.geom,g.IntValue,13.0,2,3,3).STBuffer(0.3) as fl
   from data as a
        cross apply
        dbo.Generate_Series(0,a.geom.STLength(),5) as g;
go