Top 5 Recent Articles

ARTICLES CATEGORIES

STLocateBetween

STLocateBetween — Extracts, and possibly offet, linestring using supplied start and end measures and @p_offset value.

Function Specification.

Function [lrs].[STLocateBetween] (
                  @p_linestring    geometry,
                  @p_start_measure Float,
                  @p_end_measure   Float = null,
                  @p_offset        Float = 0,
                  @p_round_xy      int   = 3,
                  @p_round_zm      int   = 2
         )
Returns geometry

Description.

Given a start and end measure, this function extracts the line segment defined between them (a point if start=end).

If a non-zero value is suppied for @p_offset, the extracted line is then offset to the left (if @p_offset < 0) or to the right (if @p_offset > 0).

Computes Z and M values if exist on @p_linestring.

Notes.

Supports linestrings with CircularString elements.

Is wrapper over [lrs].[STFindSegmentByMeasureRange].

Name is similar to PostGIS function.

Parameters.

    @p_linestring (geometry) - Linestring geometry with measures.
    @p_start_measure (float) - Measure defining start point of located geometry.
    @p_end_measure   (float) - Measure defining end point of located geometry.
    @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.

Returns line between start/end measure with optional offset.

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
)
Select locateType, 
       sm,em,
       case when f.measureSegment is not null 
            then f.measureSegment.AsTextZM() 
            else null 
        end as measureSegment 
  from (
select 'SM 1.0/EM 1.0 => Start Point' as locateType,1.0 as sm,1.0 as em,  [lrs].[STLocateBetween](linestring,1.0,1.0,0,3,2) as measureSegment from data as a
union all
select 'SM 1.0/EM NULL => Whole Linestring',1.0,null,                     [lrs].[STLocateBetween](linestring,1.0,null,0,3,2) as measureSegment from data as a
union all
select 'SM NULL/EM 1 => Start Point',null,1.0,                            [lrs].[STLocateBetween](linestring,null,1.0,0,3,2) as measureSegment from data as a
union all
select 'SM NULL/EM 5.6 => Return 1s Segment',null,5.6,                    [lrs].[STLocateBetween](linestring,null,5.6,0.0,3,2) as measureSegment from data as a
union all
select 'SM 5.6/EM 5.6 => 1st Segment EP or 2nd SP',5.6,5.6,               [lrs].[STLocateBetween](linestring,5.6,5.6,0,3,2) as measureSegment from data as a
union all
select 'SM 2.0/EM 5.0 Within First Segment => New Segment',2.0,5.0,       [lrs].[STLocateBetween](linestring,2.0,5.0,0,3,2) as measureSegment from data as a
union all
select 'SM 2.0/EM 6.0 => Two New Segments',2.0,6.0,                       [lrs].[STLocateBetween](linestring,2,6,0,3,2) as measureSegment from data as a
union all
select 'SM 1.1/EM 25.4 => New 1st Segment, 2nd, New 3rd Segment',1.1,25.1,[lrs].[STLocateBetween](linestring,1.1,25.1,0,3,2) as measureSegment from data as a
union all
select 'SM 0.1/EM 30.0 => whole linestring',0.1,30.0,                     [lrs].[STLocateBetween](linestring,0.1,30.0,0,3,2) as measureSegment from data as a
) as f;
GO

locateType                                              sm   em   measureSegment
SM 1.0/EM 1.0 => Start Point                            1.0  1.0  POINT (-4 -4 0 1)
SM 1.0/EM NULL => Whole Linestring                      1.0  NULL LINESTRING (-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)
SM NULL/EM 1 => Start Point                             NULL 1.0  POINT (-4 -4 0 1)
SM NULL/EM 5.6 => Return 1s Segment                     NULL 5.6  LINESTRING (-4 -4 0 1, 0 0 0 5.6)
SM 5.6/EM 5.6 => 1st Segment EP or 2nd SP               5.6  5.6  POINT (0 0 0 5.6)
SM 2.0/EM 5.0 Within First Segment => New Segment       2.0  5.0  LINESTRING (-2.586 -2.586 0 2, -0.464 -0.464 0 5)
SM 2.0/EM 6.0 => Two New Segments                       2.0  6.0  LINESTRING (-2.586 -2.586 0 2, 0 0 0 5.6, 6 0 0 6)
SM 1.1/EM 25.4 => New 1st Segment, 2nd, New 3rd Segment 1.1  25.1 LINESTRING (-3.222 -3.222 0 1.1, 0 0 0 5.6, 10 0 0 15.61, 10 25.1 0 25.1)
SM 0.1/EM 30.0 => whole linestring                      0.1  30.0 LINESTRING (-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)