Linear Referencing System (LRS) Functions for TSQL
Introduction
I have written a complete package of Linear Referencing functions in TSQL. While available for a number of years, they have not been well advertised: here is an attempt to do so.
The following tests test all the functions in the package (they include some utility functions like STSegmentize).
Documentation for these is available and is accessible from the main page of this site.
NOTE: Some functions have a parameter called @p_radius_check. This tells the function to check if an offset point collapses to the centre of the circle that defines a CircularString and, if it does, return that centre.
1. Original Linestring with CircularStrings…
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring;
GO
2. Add Measure To LineString…
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING
(2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING
(2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
)
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ).AsTextZM() as mLinestring
FROM data as d;
GO
mLinestring
COMPOUNDCURVE (
(2172150.685 258351.613 NULL 7400, 2171796.817 257562.728 NULL 8264.62),
CIRCULARSTRING (2171796.817 257562.728 NULL 8264.62, 2171785.154 257183.204 NULL 8654.17, 2172044.297 256905.682 NULL 9043.72),
(2172044.297 256905.682 NULL 9043.72, 2172405.655 256740.527 NULL 9441.03),
CIRCULARSTRING (2172405.655 256740.527 NULL 9441.03, 2172647.647 256579.203 NULL 9733.1, 2172826.928 256350.196 NULL 10025.17),
(2172826.928 256350.196 NULL 10025.17, 2172922.015 256178.153 NULL 10321.74))
3. Add Z to Measured Line…
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [dbo].[STAddZ](d.mlinestring, 5.34, 8.3837, 3,2 ).AsTextZM() as zmLinestring
FROM mLine as d;
GO
zmLinestring
COMPOUNDCURVE (
(2172150.685 258351.613 5.34 7400, 2171796.817 257562.728 6.27 8264.62),
CIRCULARSTRING (2171796.817 257562.728 6.27 8264.62, 2171785.154 257183.204 6.27 8654.17, 2172044.297 256905.682 6.27 9043.72),
(2172044.297 256905.682 6.27 9043.72, 2172405.655 256740.527 8.47 9441.03),
CIRCULARSTRING (2172405.655 256740.527 8.47 9441.03, 2172647.647 256579.203 8.47 9733.1, 2172826.928 256350.196 8.47 10025.17),
(2172826.928 256350.196 8.47 10025.17, 2172922.015 256178.153 8.38 10321.74))
4. Reset Measure (All M ordinates set to -9999)…
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
)
SELECT [lrs].[STResetMeasure] (
[lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ),
-9999, 3, 2).AsTextZM()
as mLinestring
FROM data as d;
GO
mLinestring
COMPOUNDCURVE (
(2172150.685 258351.613 NULL -9999, 2171796.817 257562.728 NULL -9999),
CIRCULARSTRING (2171796.817 257562.728 NULL -9999, 2171785.154 257183.204 NULL -9999, 2172044.297 256905.682 NULL -9999),
(2172044.297 256905.682 NULL -9999, 2172405.655 256740.527 NULL -9999),
CIRCULARSTRING (2172405.655 256740.527 NULL -9999, 2172647.647 256579.203 NULL -9999, 2172826.928 256350.196 NULL -9999),
(2172826.928 256350.196 NULL -9999, 2172922.015 256178.153 NULL -9999))
5. Remove Measure (Should equal original linestring)…
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
)
SELECT d.linestring.STEquals(
[lrs].[STRemoveMeasure] (
[lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ),
3,2 )
) as equals
FROM data as d;
GO
equals
1
6. Inspect Start, End Measures, Measure Range, Ascending or Descending…
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STStartMeasure](e.mLinestring) as StartMeasure,
[lrs].[STEndMeasure](e.mLinestring) as EndMeasure,
[lrs].[STMeasureRange](e.mLinestring) as MeasureRange,
[lrs].[STIsMeasureIncreasing](e.mLinestring) as MeasureIncreasing,
[lrs].[STIsMeasureDecreasing](e.mLinestring) as MeasureDecreasing
FROM mLine as e;
GO
StartMeasure EndMeasure MeasureRange MeasureIncreasing MeasureDecreasing
------------ ---------- ------------ ----------------- -----------------
7400 10321.74 2921.74 TRUE FALSE
7. LRS Validity …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STValidMeasure](e.mLinestring,7000.0) as ValidMeasureBefore,
[lrs].[STValidMeasure](e.mLinestring,e.mLinestring.STStartPoint().M) as ValidStartPoint,
[lrs].[STValidMeasure](e.mLinestring,8860.87) as ValidMeasureMiddle,
[lrs].[STValidLrsPoint](e.mLinestring.STPointN(5)) as ValidLrsMeasure
FROM mLine as e;
GO
ValidMeasureBefore ValidStartPoint ValidMeasureMiddle ValidLrsMeasure
------------------ --------------- ------------------ ---------------
0 1 1 1
8. Percentages …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT ROUND([lrs].[STMeasureToPercentage](e.mLinestring,
e.mLinestring.STStartPoint().M + [lrs].[STMeasureRange](e.mLinestring)/4.0),1) as Percentage,
ROUND([lrs].[STPercentageToMeasure](e.mLinestring, 25.3),2) as Measure
FROM mLine as e;
GO
Percentage Measure
---------- -------
25 8139.2
9. Reverse Measures …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STIsMeasureIncreasing](f.oMeasuredLine) as oMeasureIncreasing,
[lrs].[STIsMeasureDecreasing](f.rMeasuredLine) as rMeasureIncreasing,
f.rMeasuredLine.AsTextZM() as geom
FROM (SELECT e.mLinestring as oMeasuredLine,
[lrs].[STReverseMeasure](e.mLinestring,3,2) as rMeasuredLine
FROM mLine as e
) as f;
GO
oMeasureIncreasing rMeasureDecreasing geom
------------------ ------------------ -------------------------------------------------------
TRUE TRUE COMPOUNDCURVE (
(2172150.685 258351.613 NULL 10321.74, 2171796.817 257562.728 NULL 9457.12),
CIRCULARSTRING (2171796.817 257562.728 NULL 9457.12, 2171785.154 257183.204 NULL 9067.57, 2172044.297 256905.682 NULL 8678.02),
(2172044.297 256905.682 NULL 8678.02, 2172405.655 256740.527 NULL 8280.71),
CIRCULARSTRING (2172405.655 256740.527 NULL 8280.71, 2172647.647 256579.203 NULL 7988.64, 2172826.928 256350.196 NULL 7696.57),
(2172826.928 256350.196 NULL 7696.57, 2172922.015 256178.153 NULL 7400))
10. Scale Measured Line …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STScaleMeasure](e.mLinestring, 0.0, (10321.74-7400.0),0.5,3,2).AsTextZM() as ScaledMeasure
FROM mLine as e;
GO
ScaledMeasure
COMPOUNDCURVE (
(2172150.685 258351.613 NULL 0.5, 2171796.817 257562.728 NULL 865.12),
CIRCULARSTRING (2171796.817 257562.728 NULL 865.12, 2171785.154 257183.204 NULL 1254.67, 2172044.297 256905.682 NULL 1644.22),
(2172044.297 256905.682 NULL 1644.22, 2172405.655 256740.527 NULL 2041.53),
CIRCULARSTRING (2172405.655 256740.527 NULL 2041.53, 2172647.647 256579.203 NULL 2333.6, 2172826.928 256350.196 NULL 2625.67),
(2172826.928 256350.196 NULL 2625.67, 2172922.015 256178.153 NULL 2921.74))
Now locate points using various methods.
11. Locate Point By Ratio …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STFindPointByRatio](
e.mLinestring,
0.5/*ratio*/,
0.0 /*Offset*/,
0 /* RadiusCheck*/,
3, 2).AsTextZM() as PointByRatio
FROM mLine as e;
GO
PointByRatio
POINT (2171862.355 257047.58 NULL 8810.87)
12. Locate Point Using By Length (no offset)…
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STFindPointByLength](
e.mLinestring,
e.mLineString.STLength()/2.0, /*Length*/
0.0, /*Offset*/
0, /*RadiusCheck*/
3,2).AsTextZM() as Length2PointNoOffset
FROM mLine as e;
GO
Length2PointNoOffset
POINT (2171862.355 257047.58 NULL 8810.87)
13. Locate Point By Measure (no offset)…
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STFindPointByMeasure](
e.mLinestring,
e.mLinestring.STStartPoint().M + (e.mLineString.STEndPoint().M - e.mLinestring.STStartPoint().M)/2.0, /*Measure*/
0.0, /*Offset*/
0, /* RadiusCheck*/
3, 2).AsTextZM() as Measure2Point10Offset
FROM mLine as e;
GO
Measure2Point10Offset
POINT (2171895.493 257010.17 NULL 8860.87)
The following, given a Point, compute Measures and Offsets …..
14. Find Measure using Point from STFindPointByMeasure …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STFindMeasureByPoint] (
e.mLinestring,
[lrs].[STFindPointByMeasure](e.mLinestring, 9043.72, 0.0, 0, 3, 2),
3, 2) as measure
FROM mLine as e;
GO
measure (Should return starting measure)
9043.72 (Correct)
15. Locate Point By Measure, with 1.1m Offset …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT [lrs].[STFindPointByMeasure](e.mLinestring, 9043.72, 1.1, 1, 3, 2).AsTextZM() as Measure2Point10Offset
FROM mLine as e;
GO
Measure2Point10Offset
POINT (2172043.84 256904.682 NULL 9043.7)
16. Get Offset of Located Measure with 1.1m Offset: should return 1.1 M …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring
FROM data as d
)
SELECT ROUND(
[lrs].[STFindOffset](
e.mLinestring,
[lrs].[STFindPointByMeasure](e.mLinestring, 9043.72, 1.1, 1, 3, 2),
3, 2),
2) as Offset
FROM mLine as e;
GO
Offset (Should be same as original STFindPointByMeasure)
1.1 (Correct)
17. Get Measure of Located Measure (50) with 1.1m Offset: should return 50 …
WITH data as (
select geometry::STGeomFromText('COMPOUNDCURVE (
(2172150.685 258351.613, 2171796.817 257562.728),
CIRCULARSTRING (2171796.817 257562.728, 2171785.154 257183.204, 2172044.297 256905.682),
(2172044.297 256905.682, 2172405.655 256740.527),
CIRCULARSTRING (2172405.655 256740.527, 2172647.647 256579.203, 2172826.928 256350.196),
(2172826.928 256350.196, 2172922.015 256178.153))',2274) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 7400.0, 10321.74, 3,2 ) as mLinestring FROM data as d
), pm as (
select mLinestring, [lrs].[STFindPointByMeasure](e.mLinestring, 9043.72, 1.1, 1, 3, 2) as point from mLine as e
)
SELECT [lrs].[STFindMeasure](e.mLinestring, e.point, 3, 2) as measure
FROM pm as e;
GO
measure (Should be same as input)
9043.72 (Correct)
The following extract linear segments via range variables…
18.1 Locate Segment By Length With/Without Offset…
Note: NO Z and M when linestring with > 1 segments (2 Points) is offset.
WITH data as (
SELECT geometry::STGeomFromText('LINESTRING(63.29 914.361, 73.036 899.855, 80.023 897.179,79.425 902.707, 91.228 903.305,79.735 888.304, 98.4 883.584,115.73 903.305, 102.284 923.026,99.147 899.271,110.8 902.707,90.78 887.02, 96.607 926.911,95.71 926.313, 95.412 928.554, 101.238 929.002, 119.017 922.279)',0) as linestring
)
SELECT f.offset, f.linestring.AsTextZM() as tLinestring
FROM (SELECT 'O' as offset, e.linestring from data as e
union all
SELECT '0.0' as offset, [lrs].[STFindSegmentByLengthRange](
/* @p_linestring */ e.Linestring,
/* @p_start_length */ 5.1,
/* @p_end_length */ 20.2,
/* @p_offset */ 0.0,
/* @p_radius_check */ 0,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2) as linestring FROM data as e
union all
SELECT '-1.1' as offset, [lrs].[STFindSegmentByLengthRange](e.Linestring, 5.1, 20.2, -1.1, 0, 3, 2) as Lengths2Segment FROM data as e
union all
SELECT '+1.1' as offset, [lrs].[STFindSegmentByLengthRange](e.Linestring, 5.1, 20.2, +1.1, 0, 3, 2) as Lengths2Segment FROM data as e
) as f;
GO
offset Linestring
------ ---------------------------------------------------------------------------------------------------------------------
O LINESTRING (63.29 914.361, 73.036 899.855, 80.023 897.179, 79.425 902.707, 91.228 903.305, 79.735 888.304, 98.4 883.584,
115.73 903.305, 102.284 923.026, 99.147 899.271, 110.8 902.707, 90.78 887.02, 96.607 926.911, 95.71 926.313,
95.412 928.554, 101.238 929.002, 119.017 922.279)
0.0 LINESTRING (66.134 910.128, 73.036 899.855, 75.58 898.881)
-1.1 LINESTRING (75.967 899.91, 75.967 899.91, 73.759 900.762, 67.049 910.736, 67.049 910.736, 67.049 910.736, 73.047 899.868)
+1.1 LINESTRING (75.175 897.858, 75.175 897.858, 75.567 898.874, 66.127 910.116, 65.223 909.508, 72.127 899.246, 72.177 899.178,
72.231 899.114, 72.29 899.055, 72.354 899, 72.422 898.95, 72.493 898.906, 72.567 898.867, 72.644 898.834, 75.175 897.858)
18.2 Locate CircularString Segment By Length With/Without Offset…
WITH data as (
SELECT geometry::STGeomFromText('CIRCULARSTRING (0 0 NULL 1, 10.123 10.123 NULL 15.32, 20 0 NULL 33.1)',0) as linestring
)
SELECT f.offset, f.linestring.AsTextZM() as tLinestring
FROM (SELECT 'NONE' as offset, [lrs].[STFindSegmentByLengthRange](e.Linestring, 14.2, 30.1, 0.0, 1, 3, 2) as linestring FROM data as e
union all
SELECT '-1.1', [lrs].[STFindSegmentByLengthRange](e.linestring, 14.2, 30.1, -1.1, 1, 3, 2) as linestring FROM data as e
union all
SELECT '+1.1', [lrs].[STFindSegmentByLengthRange](e.linestring, 14.2, 30.1, +1.1, 1, 3, 2) as linestring FROM data as e
) as f;
GO
offset tLinestring
NONE CIRCULARSTRING (8.375 9.991 NULL 15.4, 10.123 10.123 NULL 15.32, 19.897 1.559 NULL 31.51)
-1.1 CIRCULARSTRING (8.196 11.076 NULL 15.4, 10.137 11.223 NULL 15.32, 20.986 1.717 NULL 31.51)
+1.1 CIRCULARSTRING (8.554 8.906 NULL 15.4, 10.109 9.023 NULL 15.32, 18.808 1.401 NULL 31.51)
19.1 Locate Segment By Measures With/Without Offset…
Note: No Z and M when linestring with > 1 segments (2 Points) is offset
WITH data as (
SELECT geometry::STGeomFromText('LINESTRING(63.29 914.361, 73.036 899.855, 80.023 897.179,79.425 902.707, 91.228 903.305,79.735 888.304, 98.4 883.584,115.73 903.305, 102.284 923.026,99.147 899.271,110.8 902.707,90.78 887.02, 96.607 926.911,95.71 926.313, 95.412 928.554, 101.238 929.002, 119.017 922.279)',0) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 1.0, d.linestring.STLength()+0.999, 3,2 ) as mLinestring
FROM data as d
)
SELECT f.offset, f.linestring.AsTextZM() as tLinestring
FROM (SELECT 'NONE' as offset, [lrs].[STFindSegmentByMeasureRange](e.mLinestring, 5.1, 20.2, 0.0, 1, 3, 2) as linestring FROM mLine as e
union all
SELECT '-1.1', [lrs].[STFindSegmentByMeasureRange](e.mLinestring, 5.1, 20.2, -1.1, 1, 3, 2) as Lengths2Segment FROM mLine as e
union all
SELECT '+1.1', [lrs].[STFindSegmentByMeasureRange](e.mLinestring, 5.1, 20.2, +1.1, 1, 3, 2) as Lengths2Segment FROM mLine as e
) as f;
GO
offset tLinestring
------ -----------------------------------------------------------------------------------
NONE LINESTRING (66.134 910.128 NULL 5.1, 73.036 899.855 NULL 18.48, 91.9 892.63 NULL 20.2)
-1.1 LINESTRING (92.285 893.661, 73.758 900.763, 67.049 910.736, 67.049 910.736, 73.047 899.868)
+1.1 LINESTRING (91.497 891.607, 91.497 891.607, 91.887 892.624, 66.127 910.116, 65.223 909.508,
72.127 899.246, 72.177 899.178, 72.232 899.114, 72.291 899.054, 72.355 898.999,
72.423 898.949, 72.494 898.905, 72.569 898.866, 72.646 898.833, 91.497 891.607)
19.2 Locate CircularString Segment By Measure With/Without Offset…
WITH data as (
SELECT geometry::STGeomFromText('CIRCULARSTRING (0 0 NULL 1, 10.123 10.123 NULL 15.32, 20 0 NULL 33.1)',0) as linestring
)
SELECT f.offset, f.linestring.AsTextZM() as tLinestring
FROM (SELECT 'NONE' as offset, [lrs].[STFindSegmentByMeasureRange](e.Linestring, 14.2, 30.1, 0.0, 1, 3, 2) as linestring FROM data as e
union all
SELECT '-1.1', [lrs].[STFindSegmentByMeasureRange](e.linestring, 14.2, 30.1, -1.1, 1, 3, 2) as linestring FROM data as e
union all
SELECT '+1.1', [lrs].[STFindSegmentByMeasureRange](e.linestring, 14.2, 30.1, +1.1, 1, 3, 2) as linestring FROM data as e
) as f;
GO
offset tLinestring
------ ----------------------------------------------------------------------------------------
NONE CIRCULARSTRING (7.226 9.731 NULL 14.2, 10.123 10.123 NULL 15.32, 19.601 2.921 NULL 30.1)
-1.1 CIRCULARSTRING (6.921 10.788 NULL 14.2, 10.136 11.223 NULL 15.32, 20.657 3.229 NULL 30.1)
+1.1 CIRCULARSTRING (7.531 8.674 NULL 14.2, 10.11 9.023 NULL 15.32, 18.545 2.613 NULL 30.1)
Now test length/measure support functions ….
20.1 Locate Point On CircularString By Length (no offset)…
select [lrs].[STFindPointByLength] (
/* @p_circular_arc */ geometry::STGeomFromText('CIRCULARSTRING (0 0 NULL 1, 10.123 10.123 NULL 15.32, 20 0 NULL 33.1)',0),
/* @p_length */ 31.0,
/* @p_offset */ 0.0,
/* @p_radius_check */ 1,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2
).AsTextZM() as point;
GO
point
-------------------------------
POINT (19.986 0.664 NULL 32.43)
20.2 Locate Point On CircularString By Measure (no offset)…
select [lrs].[STFindPointByMeasure] (
/* @p_circular_arc */ geometry::STGeomFromText('CIRCULARSTRING (0 0 NULL 1, 10.123 10.123 NULL 15.32, 20 0 NULL 33.1)',0),
/* @p_measure */ 32.0,
/* @p_offset */ 0.0,
/* @p_radius_check */ 1,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2
).AsTextZM() as point;
GO
point
----------------------------
POINT (19.955 1.084 NULL 32)
20.3 Split CircularString By Length (no offset)…
select [lrs].[STSplitSegmentByLength] (
/* @p_circular_arc */ geometry::STGeomFromText('CIRCULARSTRING (0 0 NULL 1, 10.123 10.123 NULL 15.32, 20 0 NULL 33.1)',0),
/* @p_start_length */ 14.0,
/* @p_end_length */ 28.0,
/* @p_offset */ 0.0,
/* @p_radius_check */ 1,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2
).AsTextZM() as geom;
GO
geom
------------------------------------------------------------------------------------------
CIRCULARSTRING (8.178 9.956 NULL 15.19, 10.123 10.123 NULL 15.32, 19.38 3.591 NULL 29.39 )
20.4 Split CircularString By Measure (no offset)…
select [lrs].[STSplitSegmentByMeasure] (
/* @p_circular_arc */ geometry::STGeomFromText('CIRCULARSTRING (0 0 NULL 1, 10.123 10.123 NULL 15.32, 20 0 NULL 33.1)',0),
/* @p_start_measure */ 15.0,
/* @p_end_measure */ 29.0,
/* @p_offset */ 0.0,
/* @p_radius_check */ 1,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2
).AsTextZM() geom;
GO
geom
-----------------------------------------------------------------------------------
CIRCULARSTRING (7.992 9.92 NULL 15, 10.123 10.123 NULL 15.32, 19.242 3.945 NULL 29)
GO
20.5 Split LineString By Length (no offset)…
select [lrs].[STSplitSegmentByLength] (
/* @p_circular_arc */ geometry::STGeomFromText('LINESTRING (0 0 NULL 1, 10.123 10.123 NULL 15.32)',0),
/* @p_start_length */ 3.0,
/* @p_end_length */ 5.0,
/* @p_offset */ 0.0,
/* @p_radius_check */ 1,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2
).AsTextZM() as geom;
GO
geom
---------------------------------------------------
LINESTRING (2.121 2.121 NULL 4, 3.536 3.536 NULL 6)
20.6 Split LineString By Measure (no offset)…
select [lrs].[STSplitSegmentByMeasure] (
/* @p_circular_arc */ geometry::STGeomFromText('LINESTRING (0 0 NULL 1, 10.123 10.123 NULL 15.32)',0),
/* @p_start_measure */ 4.0,
/* @p_end_measure */ 6.0,
/* @p_offset */ 0.0,
/* @p_radius_check */ 1,
/* @p_round_xy */ 3,
/* @p_round_zm */ 2
).AsTextZM();
GO
geom
---------------------------------------------------
LINESTRING (2.828 2.828 NULL 4, 4.243 4.243 NULL 6)
20.7 Filter LineString Segments By Length…
WITH data as (
SELECT geometry::STGeomFromText('LINESTRING(63.29 914.361, 73.036 899.855, 80.023 897.179,79.425 902.707, 91.228 903.305,79.735 888.304, 98.4 883.584, 115.73 903.305, 102.284 923.026,99.147 899.271, 110.8 902.707, 90.78 887.02, 96.607 926.911,95.71 926.313, 95.412 928.554, 101.238 929.002, 119.017 922.279)',0) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 1.0, d.linestring.STLength()+0.999, 3,2 ) as mLinestring
FROM data as d
)
SELECT v.id,
v.min_id,
v.max_id,
v.segment_length,
v.start_length,
v.geometry_type,
v.segment.AsTextZM() as geom
FROM mLine as m
cross apply
[dbo].[STSegmentize](
m.mLinestring,
'LENGTH_RANGE',
NULL,
NULL,
30,
50,
3,
3,3
) as v
ORDER BY v.id;
GO
id min_id max_id length startLength geom
-- ------ ------ ---------------- ---------------- -----------------------------------------------------------------
3 3 5 5.56025071377184 24.9578633024073 LINESTRING (80.023 897.179 NULL 25.96, 79.425 902.707 NULL 31.52)
4 3 5 11.8181391513216 30.5181140161791 LINESTRING (79.425 902.707 NULL 31.52, 91.228 903.305 NULL 43.34)
5 3 5 18.8975937621698 42.3362531675007 LINESTRING (91.228 903.305 NULL 43.34, 79.735 888.304 NULL 62.23)
18.7 Filter LineString Segments By Measure…
WITH data as (
SELECT geometry::STGeomFromText('LINESTRING(63.29 914.361, 73.036 899.855, 80.023 897.179,79.425 902.707, 91.228 903.305,79.735 888.304, 98.4 883.584, 115.73 903.305, 102.284 923.026,99.147 899.271, 110.8 902.707, 90.78 887.02, 96.607 926.911,95.71 926.313, 95.412 928.554, 101.238 929.002, 119.017 922.279)',0) as linestring
), mLine as (
SELECT [lrs].[STAddMeasure] (d.linestring, 1.0, d.linestring.STLength()+0.999, 3,2 ) as mLinestring
FROM data as d
)
SELECT v.id,
v.min_id,
v.max_id,
v.segment_length,
v.start_length,
v.geometry_type,
v.segment.AsTextZM() as geom
FROM mLine as m
cross apply
[dbo].[STSegmentize](
m.mLinestring,
'MEASURE_RANGE',
NULL,
NULL,
29,
49,
3,
3,3
) as v
ORDER BY v.id;
GO
id min_id max_id length startLength geom
-- ------ ------ ---------------- ---------------- -----------------------------------------------------------------
3 3 5 5.56025071377184 0 LINESTRING (80.023 897.179 NULL 25.96, 79.425 902.707 NULL 31.52)
4 3 5 11.8181391513216 5.56025071377184 LINESTRING (79.425 902.707 NULL 31.52, 91.228 903.305 NULL 43.34)
5 3 5 18.8975937621698 17.3783898650934 LINESTRING (91.228 903.305 NULL 43.34, 79.735 888.304 NULL 62.23)
Conclusion
As can be seen, this is a powerful set of functions for doing linear referencing and dynamic segmentation.
The functions can be accessed by making a donation at the SQL Server Spatial entry in the Shop accessible from the home page of this website.
I hope this article is of use to someone out there.