Posted on

STLocateBetweenElevations

STLocateBetweenElevations — Computes and returns elements that intersect the specified Z range.

Function Specification

Function [$(lrsowner)].[STLocateBetweenElevations] (
               @p_linestring geometry,
               @p_start_z    Float,
               @p_end_z      Float = null,
               @p_round_xy   int   = 3,
               @p_round_zm   int   = 2
             )
     Returns geometry 

Description

Is implementation of PostGIS:

      geometry ST_LocateBetweenElevations(geometry geom_mline, 
                                          float8 elevation_start,
                                          float8 elevation_end);

Processes the supplied (3D, 3DM) (multi)linestring returning the elements that intersect the specified range of elevations inclusively.

May return points and/or linestrings in the appropriate geometry type.

Where a new xy position is to be computed, the value is rounded using @p_round_xm.

Computes M values if exist on @p_linestring and rounds the values based on @p_round_zm.

Notes

Does not currently support Linestrings with CircularString elements (2012+).

Parameters

    @p_linestring (geometry) - Linestring geometry with Z ordinates (could have M ordinates).
    @p_start_z       (float) - Start Elevation.
    @p_end_z         (float) - End Elevation.
    @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 computes and extracts the geometry of the appropriate type.

Examples

-- PostGIS 1
select [$(lrsowner)].[STLocateBetweenElevations](
         geometry::STGeomFromText('LINESTRING(1 2 3, 4 5 6)',0),
         2,4, 
         3,2
       ).AsTextZM() as geomZ;

geomz
LINESTRING (1 2 3, 2 3 4)

-- PostGIS 2
select [$(lrsowner)].[STLocateBetweenElevations](
         geometry::STGeomFromText('LINESTRING(1 2 6, 4 5 -1, 7 8 9)',0),
         6,9, 
         3,2
       ).AsTextZM() as geomZ;
 
geomz
GEOMETRYCOLLECTION (POINT (1 2 6), LINESTRING (6.1 7.1 6, 7 8 9))

-- PostGIS 3
SELECT d.geom.AsTextZM() as geomWKT
  FROM (SELECT [$(lrsowner)].[STLocateBetweenElevations](
                 geometry::STGeomFromText('LINESTRING(1 2 6, 4 5 -1, 7 8 9)',0),
                 6,9,
                 3,2
               ) As the_geom
       ) As foo
       cross apply
       [$(owner)].[STExtract](foo.the_geom,default) as d;

geomWKT
POINT (1 2 6)
LINESTRING (6.1 7.1 6, 7 8 9)
Posted on

STLineSubstring

STLineSubstring — Returns a substring of the providec linestring starting and ending at the given fractions (between 0 and 1) of total 2D length or measure range.

Function Specification

Function [$(lrsowner)].[STLineSubstring] (
               @p_linestring     geometry,
               @p_start_fraction Float,
               @p_end_fraction   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 supplied for @p_offset, the extracted line is then offset to the left (if @p_offset < 0) or to the right (if @p_offset > 0).

Notes

Supports linestrings with CircularString elements.

Supports measured and unmeasured linestrings.

Is wrapper over STFindSegmentByMeasureRange.

Provides implementation of PostGIS’s ST_LocateBetween(geometry geomA, float8 measure_start, float8 measure_end, float8 offset);

Inputs

    @p_linestring  (geometry) - Linestring geometry with measures.
    @p_start_fraction (float) - Value defining start point of located geometry.
    @p_end_fraction   (float) - Value 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.

Notes

Current offset generation is limited; a new more robust algorithm is being developed.

Result

This function computes and returns a line between the supplied start/end measure with offset.

Example

-- Measured Linestring
Print '....Line SubString';
select [lrs].[STLineSubstring] (
         geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
         0.0,1.0,0.0,3,2).AsTextZM() as line
union all
select [lrs].[STLineSubstring] (
         geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
         0.0,0.5,0.0,3,2).AsTextZM() as line
GO

line
LINESTRING (-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)
LINESTRING (-4 -4 0 1, 0 0 0 5.6, 13.2 0 0 13.2)

 -- UnMeasured LineStrings';
select [lrs].[STLineSubstring] (
         geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
         0.0,1.0,0.0,3,2).AsTextZM() as line
union all
select [lrs].[STLineSubstring] (
         geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
         0.0,0.5,0.0,3,2).AsTextZM() as line
GO

line
LINESTRING (-4 -4, 0 0, 10 0, 10 10)
LINESTRING (-4 -4, 0 0, 7.172 0)
Posted on

STLineInterpolatePoint

STLineInterpolatePoint — Returns point geometry at supplied fraction along linestring.

Function Specification

Function [$(lrsowner)].[STLineInterpolatePoint] (
            @p_linestring geometry,
            @p_fraction   Float
            @p_round_xy   int   = 3,
            @p_round_zm   int   = 2
         )
 Returns geometry

Description

Given a fraction between 0 and 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).

Supports measured and unmeasured linestrings.

Supports LineStrings with CircularString elements.

Notes

Wrapper over lrs.STFindPointByRatio

Implements PostGIS ST_LineInterpolatePoint function.

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_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

The function calculates and returns point at provided measure/length fraction from start.

Examples

-- Linestring
select f.fraction,
       [$(lrsowner)].[STLineInterpolatePoint] (
          @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_fraction   f.fraction,
          @p_round_xy   4,
          @p_round_zm   3
       ).AsTextZM() as fPoint
  from (select 0.01 * CAST(t.IntValue as numeric) as fraction
          from [dbo].[Generate_Series](1,100,10) as t
       ) as f
  order by f.fraction
GO

fraction fPoint
0.01     POINT (-3.8186 -3.8186 0 1.046)
0.11     POINT (-2.0044 -2.0044 0 1.506)
0.21     POINT (-0.1902 -0.1902 0 1.966)
0.31     POINT (2.2968 0 0 6.496)
0.41     POINT (4.8625 0 0 7.497)
0.51     POINT (7.4281 0 0 8.498)
0.61     POINT (9.9938 0 0 9.499)
0.71     POINT (10 2.5595 0 16.587)
0.81     POINT (10 5.1252 0 17.566)
0.91     POINT (10 7.6909 0 18.545)
 
-- Unmeasured 2D Compound curve test.
select f.fraction,
       [$(lrsowner)].[STLineInterpolatePoint] (
          geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0),
          f.fraction,
          4,
          3
       ).AsTextZM() as fPoint
  from (select 0.01 * CAST(t.IntValue as numeric) as fraction
          from [dbo].[Generate_Series](1,100,10) as t
       ) as f
  order by f.fraction
GO

fraction fPoint
0.01     POINT (2.8163 6.4085)
0.11     POINT (0.876 6.945)
0.21     POINT (-1.1367 6.9071)
0.31     POINT (-2.9736 6.269)
0.41     POINT (-2.1079 4.4439)
0.51     POINT (-1.2421 2.6187)
0.61     POINT (-0.3764 0.7935)
0.71     POINT (0.4893 1.0316)
0.81     POINT (1.3551 2.8568)
0.91     POINT (2.2208 4.682)
Posted on

STLineLocatePoint

STLineLocatePoint — Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point

Function Specification.

Function [$(lrsowner)].[STLineLocatePoint] (
           @p_linestring geometry,
           @p_point      geometry,
           @p_round_xy   int   = 3,
           @p_round_zm   int   = 2
         )
 Returns geometry

Description.

This function, given a point near a the supplied measure @p_linestring, returns the measure/length ratio of the found position.

Notes.

Is identical to PostGIS’s ST_LineLocatePoint.

Srid of @p_linestring and @p_point must be the same.

If @p_linestring is measured ratio returned is measure of located point / MeasureRange of linestring.

If @p_linestring is not measured the ratio returned is position of located point from start / STLenth of linestring.

Parameters.

    @p_linestring (geometry) - Linestring geometry with or without measures.
    @p_point      (geometry) - Point near to linestring.
    @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.

The function returns the ratio of the position of the point on @p_linestring using measure/measure range or length/total length.

Example.

select [lrs].[STLineLocatePoint] (
           geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
           geometry::Point(8,8,28355),
           default,
           default
       ) as ratio
union all
select [lrs].[STLineLocatePoint] (
           geometry::STGeomFromText('LINESTRING(-4 -4,0 0,10 0,10 10)',28355),
           geometry::Point(10,0,28355),
           4,
           8
       ) as ratio
GO

ratio
23.44
15.6
Posted on

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]

Posted on

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_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 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.

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_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_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_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)
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)
Posted on

STLocateAlong

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

Function Specification.

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

Description.

This function is identical to STFindPointByName and so is a synonym for it.

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).

Computed point’s ordinate values are rounded to @p_round_xy/@p_round_zm decimal digits of precision.

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 STSrid 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.

The function returns the point at the provided measure optionally 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
)
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

measure offset fPoint
      1     -1 POINT (-0.707 0.707 NULL 1)
      1      0 POINT (-4 -4 0 1)
      1      1 POINT (0.707 -0.707 NULL 1)
      3     -1 POINT (-3.293 -1.879 NULL 3)
      3      0 POINT (-2.586 -2.586 NULL 3)
      3      1 POINT (-1.879 -3.293 NULL 3)
      5     -1 POINT (-1.879 -0.465 NULL 5)
      5      0 POINT (-1.172 -1.172 NULL 5)
      5      1 POINT (-0.465 -1.879 NULL 5)
      7     -1 POINT (1.4 1 NULL 7)
      7      0 POINT (1.4 0 NULL 7)
      7      1 POINT (1.4 -1 NULL 7)
      9     -1 POINT (3.4 1 NULL 9)
      9      0 POINT (3.4 0 NULL 9)
      9      1 POINT (3.4 -1 NULL 9)
     11     -1 POINT (5.4 1 NULL 11)
     11      0 POINT (5.4 0 NULL 11)
     11      1 POINT (5.4 -1 NULL 11)
     13     -1 POINT (7.4 1 NULL 13)
     13      0 POINT (7.4 0 NULL 13)
     13      1 POINT (7.4 -1 NULL 13)
     15     -1 POINT (9.4 1 NULL 15)
     15      0 POINT (9.4 0 NULL 15)
     15      1 POINT (9.4 -1 NULL 15)
     17     -1 POINT (9 1.39 NULL 17)
     17      0 POINT (10 1.39 NULL 17)
     17      1 POINT (11 1.39 NULL 17)
     19     -1 POINT (9 3.39 NULL 19)
     19      0 POINT (10 3.39 NULL 19)
     19      1 POINT (11 3.39 NULL 19)
     21     -1 POINT (9 5.39 NULL 21)
     21      0 POINT (10 5.39 NULL 21)
     21      1 POINT (11 5.39 NULL 21)
     23     -1 POINT (9 7.39 NULL 23)
     23      0 POINT (10 7.39 NULL 23)
     23      1 POINT (11 7.39 NULL 23)
     25     -1 POINT (9 9.39 NULL 25)
     25      0 POINT (10 9.39 NULL 25)
     25      1 POINT (11 9.39 NULL 25)
      1     -1 POINT (-0.707 0.707 NULL 1)
      1      0 POINT (-4 -4 0 1)
      1      1 POINT (0.707 -0.707 NULL 1)
      2     -1 POINT (-0.707 0.707 NULL 5.6)
      2      0 POINT (0 0 0 5.6)
      2      1 POINT (0.707 -0.707 NULL 5.6)
      3     -1 POINT (10 1 NULL 15.61)
      3      0 POINT (10 0 0 15.61)
      3      1 POINT (10 -1 NULL 15.61)
      4     -1 POINT (9 10 NULL 25.4)
      4      0 POINT (10 10 0 25.4)
      4      1 POINT (11 10 NULL 25.4)
Posted on

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)
Posted on

STProjectPoint

STProjectPoint — Returns a measured point by snapping provided point to the provided line.

Function Specification.

Function [lrs].[STProjectPoint] (
                  @p_linestring geometry,
                  @p_point      geometry,
                  @p_round_xy   int   = 3,
                  @p_round_zm   int   = 2
             )
Returns geometry

Description.

This function snaps supplied point to @p_linestring, returning the snapped point.

If there is more than one possible solution the first point is retuend.

Computes Z and M values if exist on @p_linestring.

If input @p_linestring is 2D, length from start of @p_linestring to point is returned in M ordinate of snapped point.

Returned points ordinate values are rounded to @p_round_xy/@p_round_zm decimal digits of precision.

Notes.

Supports linestrings with CircularString elements.

Parameters.

    @p_linestring (geometry) - Measured linestring with or without Z ordinates.
    @p_point      (geometry) - Point near to linestring.
    @p_round_xy        (int) - Decimal digits of precision for XY ordinates.
    @p_round_zm        (int) - Decimal digits of precision for M ordinate.

Result.

The function snaps the supplied @p_point to @p_linestring computing XY, Z (if exists) and M ordinate values.

Example.

select CAST('Actual Measure' as varchar(20)) as test,
       [lrs].[STProjectPoint] (
          geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
          geometry::Point(8,8,28355),
          3,2).AsTextZM() as project_point
union all
select '2D return length in measure' as test,
       [lrs].[STProjectPoint] (
          geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
          geometry::Point(8,8,28355),
          3,2).AsTextZM() as project_point
GO

test	project_point
Actual Measure	POINT (10 8 NULL 23.44)
2D return length in measure	POINT (10 8 NULL 23.66)