STFindPointByMeasure

STFindPointByMeasure — Returns (possibly offset) point geometry at supplied measure along linestring.

Function Specification.

Function [lrs].[STFindPointByMeasure] (
               @p_linestring geometry,
               @p_measure    Float,
               @p_offset     Float = 0.0,
               @p_round_xy   int   = 3,
               @p_round_zm   int   = 2
             )
Returns geometry

Description.

Given a measure, this function returns a geometry point at that measure.

If a non-zero/null value is suppied 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).

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 with measures.
    @p_measure       (float) - Measure defining position of point to be located.
    @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 the geometry point at the provided measure optionally offset to left or right.

Example.

— Handle non-measured linestring
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 f.linestring.STEquals(f.fpoint) as equals
from (select [lrs].[STFindPointByMeasure](a.linestring,0,0,3,2) as fPoint,
a.linestring
from data as a
) f
go

equals
1

— Circular Arc / Measured Tests
with data as (
select geometry::STGeomFromText(‘LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)’,0) as linestring
union all
select geometry::STGeomFromText(‘CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)’,0) as linestring
union all
select geometry::STGeomFromText(‘COMPOUNDCURVE(CIRCULARSTRING (3 6.3246 NULL 0, 0 7 NULL 3.08, -3 6.3246 NULL 6.15),(-3 6.3246 NULL 6.15, 0 0 NULL 10.1, 3 6.3246 NULL 20.2))’,0) as linestring
)
select a.linestring.STGeometryType() as curve_type,
g.intValue as measure,
o.IntValue as offset,
[lrs].[STFindPointByMeasure](a.linestring,g.IntValue,o.IntValue,3,2).AsTextZM() as fPoint
from data as a
cross apply
[dbo].[generate_series](a.lineString.STPointN(1).M,
round(a.lineString.STPointN(a.linestring.STNumPoints()).M,0,1),
[lrs].[STMeasureRange](a.linestring) / 4.0 ) as g
cross apply
[dbo].[generate_series](-1, 1, 1) as o
order by curve_type, measure
GO

curve_type measure offset fPoint
CircularString 0 -1 POINT (3.428 7.229 NULL 0)
CircularString 0 0 POINT (3 6.325 NULL 0)
CircularString 0 1 POINT (2.572 5.421 NULL 0)
CircularString 1 -1 POINT (2.355 7.646 NULL 1)
CircularString 1 0 POINT (2.061 6.69 NULL 1)
CircularString 1 1 POINT (1.767 5.734 NULL 1)
CircularString 2 -1 POINT (1.234 7.904 NULL 2)
CircularString 2 0 POINT (1.08 6.916 NULL 2)
CircularString 2 1 POINT (0.925 5.928 NULL 2)
CircularString 3 -1 POINT (0.086 8 NULL 3)
CircularString 3 0 POINT (0.076 7 NULL 3)
CircularString 3 1 POINT (0.065 6 NULL 3)
CircularString 4 -1 POINT (-1.063 7.929 NULL 4)
CircularString 4 0 POINT (-0.93 6.938 NULL 4)
CircularString 4 1 POINT (-0.797 5.947 NULL 4)
CircularString 5 -1 POINT (-2.19 7.695 NULL 5)
CircularString 5 0 POINT (-1.916 6.733 NULL 5)
CircularString 5 1 POINT (-1.643 5.771 NULL 5)
CircularString 6 -1 POINT (-3.271 7.301 NULL 6)
CircularString 6 0 POINT (-2.863 6.388 NULL 6)
CircularString 6 1 POINT (-2.454 5.476 NULL 6)
CompoundCurve 0 -1 POINT (3.429 7.228 NULL 0)
CompoundCurve 0 0 POINT (3 6.3246 NULL 0)
CompoundCurve 0 1 POINT (2.571 5.421 NULL 0)
CompoundCurve 5 -1 POINT (-2.19 7.694 NULL 5)
CompoundCurve 5 0 POINT (-1.916 6.733 NULL 5)
CompoundCurve 5 1 POINT (-1.642 5.771 NULL 5)
CompoundCurve 10 -1 POINT (-7 5.325 NULL 10)
CompoundCurve 10 0 POINT (-7 6.325 NULL 10)
CompoundCurve 10 1 POINT (-7 7.325 NULL 10)
CompoundCurve 15 -1 POINT (-12 5.325 NULL 15)
CompoundCurve 15 0 POINT (-12 6.325 NULL 15)
CompoundCurve 15 1 POINT (-12 7.325 NULL 15)
CompoundCurve 20 -1 POINT (-17 5.325 NULL 20)
CompoundCurve 20 0 POINT (-17 6.325 NULL 20)
CompoundCurve 20 1 POINT (-17 7.325 NULL 20)
LineString 1 -1 POINT (-0.707 0.707 NULL 1)
LineString 1 0 POINT (-4 -4 0 1)
LineString 1 1 POINT (0.707 -0.707 NULL 1)
LineString 7 -1 POINT (1.4 1 NULL 7)
LineString 7 0 POINT (1.4 0 NULL 7)
LineString 7 1 POINT (1.4-1 NULL 7)
LineString 13 -1 POINT (7.4 1 NULL 13)
LineString 13 0 POINT (7.4 0 NULL 13)
LineString 13 1 POINT (7.4-1 NULL 13)
LineString 19 -1 POINT (9 3.39 NULL 19)
LineString 19 0 POINT (10 3.39 NULL 19)
LineString 19 1 POINT (11 3.39 NULL 19)
LineString 25 -1 POINT (9 9.39 NULL 25)
LineString 25 0 POINT (10 9.39 NULL 25)
LineString 25 1 POINT (11 9.39 NULL 25)
[/code]