Posted on

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_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 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.
    @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('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
  union all
  select /*2D*/ geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0) as linestring
)
select a.linestring.STGeometryType() as line_type,
       a.linestring.HasM as is_measured,
       g.intValue as length,
       offset.IntValue as offset,
       [lrs].[STFindPointByLength](a.linestring,g.IntValue,offset.IntValue,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 line_type, is_measured, length
GO

line_type      is_measured length offset fPoint
CircularString           1      0     -1 POINT (3.428 7.229 NULL 0)
CircularString           1      0      0 POINT (3 6.325 NULL 0)
CircularString           1      0      1 POINT (2.572 5.421 NULL 0)
CircularString           1      1     -1 POINT (2.364 7.643 NULL 0.99)
CircularString           1      1      0 POINT (2.069 6.687 NULL 0.99)
CircularString           1      1      1 POINT (1.774 5.732 NULL 0.99)
CircularString           1      2     -1 POINT (1.252 7.901 NULL 1.98)
CircularString           1      2      0 POINT (1.096 6.914 NULL 1.98)
CircularString           1      2      1 POINT (0.939 5.926 NULL 1.98)
CircularString           1      3     -1 POINT (0.115 7.999 NULL 2.98)
CircularString           1      3      0 POINT (0.1 6.999 NULL 2.98)
CircularString           1      3      1 POINT (0.086 5.999 NULL 2.98)
CircularString           1      4     -1 POINT (-1.025 7.934 NULL 3.97)
CircularString           1      4      0 POINT (-0.897 6.942 NULL 3.97)
CircularString           1      4      1 POINT (-0.769 5.951 NULL 3.97)
CircularString           1      5     -1 POINT (-2.144 7.707 NULL 4.96)
CircularString           1      5      0 POINT (-1.877 6.744 NULL 4.96)
CircularString           1      5      1 POINT (-1.609 5.78 NULL 4.96)
CircularString           1      6     -1 POINT (-3.22 7.324 NULL 5.95)
CircularString           1      6      0 POINT (-2.818 6.408 NULL 5.95)
CircularString           1      6      1 POINT (-2.415 5.493 NULL 5.95)
CompoundCurve            0      0     -1 POINT (3.429 7.228)
CompoundCurve            0      0      0 POINT (3 6.3246)
CompoundCurve            0      0      1 POINT (2.571 5.421)
CompoundCurve            0      5     -1 POINT (-2.144 7.707)
CompoundCurve            0      5      0 POINT (-1.876 6.744)
CompoundCurve            0      5      1 POINT (-1.608 5.78)
CompoundCurve            0     10     -1 POINT (-0.468 3.321)
CompoundCurve            0     10      0 POINT (-1.372 2.892)
CompoundCurve            0     10      1 POINT (-2.276 2.463)
CompoundCurve            0     15     -1 POINT (-0.133 2.054)
CompoundCurve            0     15      0 POINT (0.771 1.625)
CompoundCurve            0     15      1 POINT (1.675 1.196)
CompoundCurve            0     20     -1 POINT (2.01 6.572)
CompoundCurve            0     20      0 POINT (2.914 6.143)
CompoundCurve            0     20      1 POINT (3.818 5.714)
CompoundCurve            1      0     -1 POINT (3.429 7.228 NULL 0)
CompoundCurve            1      0      0 POINT (3 6.3246 NULL 0)
CompoundCurve            1      0      1 POINT (2.571 5.421 NULL 0)
CompoundCurve            1      5     -1 POINT (-2.144 7.707 NULL 4.96)
CompoundCurve            1      5      0 POINT (-1.876 6.744 NULL 4.96)
CompoundCurve            1      5      1 POINT (-1.608 5.78 NULL 4.96)
CompoundCurve            1     10     -1 POINT (-0.468 3.321 NULL 6.89)
CompoundCurve            1     10      0 POINT (-1.372 2.892 NULL 6.89)
CompoundCurve            1     10      1 POINT (-2.276 2.463 NULL 6.89)
CompoundCurve            1     15     -1 POINT (-0.133 2.054 NULL 11)
CompoundCurve            1     15      0 POINT (0.771 1.625 NULL 11)
CompoundCurve            1     15      1 POINT (1.675 1.196 NULL 11)
CompoundCurve            1     20     -1 POINT (2.01 6.572 NULL 13.5)
CompoundCurve            1     20      0 POINT (2.914 6.143 NULL 13.5)
CompoundCurve            1     20      1 POINT (3.818 5.714 NULL 13.5)
LineString               1      0     -1 POINT (-4.707 -3.293 0 1)
LineString               1      0      0 POINT (-4 -4 0 1)
LineString               1      0      1 POINT (-3.293 -4.707 0 1)
LineString               1      6     -1 POINT (0.343 1 0 5.73)
LineString               1      6      0 POINT (0.343 0 0 5.73)
LineString               1      6      1 POINT (0.343-1 0 5.73)
LineString               1     12     -1 POINT (6.343 1 0 8.07)
LineString               1     12      0 POINT (6.343 0 0 8.07)
LineString               1     12      1 POINT (6.343-1 0 8.07)
LineString               1     18     -1 POINT (9 2.343 0 16.5)
LineString               1     18      0 POINT (10 2.343 0 16.5)
LineString               1     18      1 POINT (11 2.343 0 16.5)
LineString               1     24     -1 POINT (9 8.343 0 18.79)
LineString               1     24      0 POINT (10 8.343 0 18.79)
LineString               1     24      1 POINT (11 8.343 0 18.79)
Leave a Reply

Your email address will not be published. Required fields are marked *