Posted on

## STPointToCircularArc

STPointToCircularArc — Return a measured point by snapping provided point to the provided CircularString

Function Specification

```Function [lrs].[STPointToCircularArc] (
@p_circularString geometry,
@p_vertex         geometry,
@p_round_xy       int   = 3,
@p_round_zm       int   = 2
)
Returns geometry
```

Description

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

Computes Z and M values if exist on @p_circularString.

If input @p_circularString is 2D, length from start of @p_circularString 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 CircularString geometries only.

If @p_point does not fall on CircularString, null is returned.

If @p_point is the same as the centre of the circle formed by the CircularString, the first point in the CircularString is returned.

Parameters

```    @p_circularString (geometry) - (Measured) CircularString with or without Z ordinates.
@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

This function computes position of point on CircularString. If @p_point can’t be found on CircularArc, NULL is returned.

Examples

```select 'Point has relationship with XYZM circular arc' as test,
[lrs].[STPointToCircularArc] (
geometry::STGeomFromText('CIRCULARSTRING (3 6.325 -2.1 0, 0 7 -2.1 3.08, -3 6.325 -2.1 6.15)',0),
geometry::Point(2,8,0),
3,2).AsTextZM() as project_point
union all
select 'Point does not have relationship with XYM CircularSring' as test,
[lrs].[STPointToCircularArc] (
geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0),
geometry::Point(8,8,0),
3,2).AsTextZM() as project_point
union all
select 'Point is on centre of the circular arc' as test,
[lrs].[STPointToCircularArc] (
geometry::STGeomFromText('CIRCULARSTRING (3 6.3246 -1, 0 7 -1, -3 6.3246 -1)',0),
geometry::Point(0,0,0),
3,2).AsTextZM() as project_point
union all
select 'Point projects on to point half way along circular arc' as test,
[lrs].[STPointToCircularArc] (
geometry::STGeomFromText('CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246)',0),
geometry::Point(0,3.5,0),
3,2).AsTextZM() as project_point
go

test                                                    project_point
------------------------------------------------------- -----------------------------
Point has relationship with XYZM circular arc           POINT (1.698 6.791 -2.1 1.37)
Point does not have relationship with XYM CircularSring NULL
Point is on centre of the circular arc                  POINT (3 6.3246 -1)
Point projects on to point half way along circular arc  POINT (0 7 NULL 3.1)
```
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)
```