- 1. ROBODoc/ROBODoc Cascading Style Sheet
- 2. LRS/STAddMeasure (2012)
- 3. LRS/STEndMeasure (2012)
- 4. LRS/STFindMeasure (2012)
- 5. LRS/STFindMeasureByPoint (2012)
- 6. LRS/STFindOffset (2012)
- 7. LRS/STFindPointByLength (2012)
- 8. LRS/STFindPointByMeasure (2012)
- 9. LRS/STFindPointsByMeasures (2012)
- 10. LRS/STFindSegmentByLengthRange (2012)
- 11. LRS/STFindSegmentByMeasureRange (2012)
- 12. LRS/STInterpolatePoint (2012)
- 13. LRS/STIsMeasured (2012)
- 14. LRS/STIsMeasureDecreasing (2012)
- 15. LRS/STIsMeasureIncreasing (2012)
- 16. LRS/STLineInterpolatePoint (2012)
- 17. LRS/STLineLocatePoint (2012)
- 18. LRS/STLineSubstring (2012)
- 19. LRS/STLocateAlong (2012)
- 20. LRS/STLocateBetween (2012)
- 21. LRS/STLocateBetweenElevations (2008)
- 22. LRS/STMeasureRange (2012)
- 23. LRS/STMeasureToPercentage (2012)
- 24. LRS/STPercentageToMeasure (2012)
- 25. LRS/STPointToCircularArc (2012)
- 26. LRS/STProjectPoint (2012)
- 27. LRS/STRemoveMeasure (2012)
- 28. LRS/STResetMeasure (2012)
- 29. LRS/STReverseMeasure (2012)
- 30. LRS/STScaleMeasure (2012)
- 31. LRS/STSetM (2012)
- 32. LRS/STSetMeasure (2012)
- 33. LRS/STSplit (2012)
- 34. LRS/STSplitProcedure (2012)
- 35. LRS/STSplitSegmentByLength (2012)
- 36. LRS/STSplitSegmentByMeasure (2012)
- 37. LRS/STStartMeasure (2012)
- 38. LRS/STUpdateMeasure (2012)
- 39. LRS/STValidLrsGeometry (2012)
- 40. LRS/STValidLrsPoint (2012)
- 41. LRS/STValidMeasure (2012)

[ Top ] [ Modules ]

FUNCTION

This is the default cascading style sheet for documentation generated with ROBODoc. You can edit this file to your own liking and then use it with the option --css <filename> This style-sheet defines the following layout +----------------------------------------+ | logo | +----------------------------------------+ | extra | +----------------------------------------+ | | navi- | | | gation | | content | | | | | +----------------------------------------+ | footer | +----------------------------------------+ This style-sheet is based on a style-sheet that was automatically generated with the Strange Banana stylesheet generator. See http://www.strangebanana.com/generator.aspx

[ Top ] [ Functions ]

NAME

STAddMeasure -- Function that adds measures to the supplied linestring point.

SYNOPSIS

Function STAddMeasure ( @p_linestring geometry, @p_start_measure float, @p_end_measure float, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

USAGE

SELECT [$(lrsowner).[STAddMeasure] ( geometry::STGeomFromText('LINESTRING(0 0,1 1)',0), 1, 1.414, 3, 2 ).AsTextZM() as MeasureLine; MeasureLine LINESTRING (0 0 NULL 1, 1 1 NULL 1.41)

DESCRIPTION

Function that add measured ordinates to the supplied @p_linestring. Supports LineString, CircularString, CompoundCurve geometries If geometry is already measured it is returned unchanged. Start Point is assigned @p_start_measure and End Point is assigned @p_end_measure. Intermediate Points' measure values are calculated based on length calculations. The updated coordinate's XY ordinates are rounded to @p_round_xy number of decimal digits of precision. The updated coordinate's ZM ordinates are rounded to @p_round_ZM number of decimal digits of precision.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @p_start_measure (float) - New Start M easurevalue. @p_end_measure (float) - New End Measure value. @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

measured linestring (geometry) - Input linestring with measures applied.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original TSQL Coding for SQL Spatial.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STEndMeasure -- Returns M value of last point in a measured geometry.

SYNOPSIS

Function STEndMeasure ( @p_linestring geometry ) Returns geometry

DESCRIPTION

Returns the measure associated with the last point in a measured line-string. Supports Linestrings with CircularString elements (2012).

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry.

RESULT

measure (float) -- Measure value of the last point in a measured line-string.

NOTES

If the line-string is not measured it returns the length of @p_linestring.

EXAMPLE

select [lrs].[STEndMeasure](geometry::STGeomFromText('LINESTRING(1 1 2 3, 2 2 3 4)', 0)) as end_measure union all select [lrs].[STEndMeasure](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))', 0)) union all select [lrs].[STEndMeasure](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0)) GO end_measure 4 6 6.15

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Port to SQL Server.

COPYRIGHT

(c) 2012-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STFindMeasure -- Snaps input point to measured linestring returning measure value

SYNOPSIS

Function STFindMeasure ( @p_linestring geometry, @p_point geometry, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns float

DESCRIPTION

Given a point near a the supplied measure @p_linestring, this function returns the measure of the closest point on the measured @p_linestring. Measure value returned is rounded to supplied @p_round_zm parameter.

NOTES

Is identical to [lrs].[STFindMeasureByPoint]. Srid of @p_linestring and @p_point must be the same. @p_linestring must be measured.

INPUTS

@p_linestring (geometry) - Measured (Multi)LineString geometry. @p_point (geometry) - Offset Point for which closest linestring point measure is needed. @p_round_xy (int) - Decimal digits of precision for XY ordinates. @p_round_zm (int) - Decimal digits of precision for M ordinate.

RESULT

measure (float) - Measure of closest point on line to supplied point.

EXAMPLE

select [lrs].[STFindMeasure] ( 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) as measure union all select [lrs].[STFindMeasure] ( 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(10,0,28355), 3,2) as measure GO measure 23.44 15.61

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2017 - Original coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STFindMeasureByPoint -- Returns value of the measure dimension of a point on the provided linesting closest to the provided point.

SYNOPSIS

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

DESCRIPTION

Given a point near a the supplied measure @p_linestring, this function returns the measure of the closest point on the measured @p_linestring. Returned measure value is rounded to @p_round_zm decimal digits of precision.

NOTES

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

INPUTS

@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

measure value (float) - Measure of point found on @p_linestring.

EXAMPLE

select [$(lrsowner)].[STFindMeasureByPoint] ( 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) as measure union all select [$(lrsowner)].[STFindMeasureByPoint] ( 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(10,0,28355), 3,2) as measure GO measure 23.44 15.61

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original coding. Simon Greener - December 2017 - Port to SQL Server (TSQL).

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STFindOffset -- Compute smallest perpendicular offset from supplied point to the supplied linestring.

SYNOPSIS

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

DESCRIPTION

Given a point near @p_linestring, this function returns the perpendicular distance from it to the closest point on @p_linestring. Returned measure value is rounded to @p_round_zm decimal digits of precision.

NOTES

Calls [$(lrsowner)].[STProjectPoint]. Srid of @p_linestring and @p_point must be the same.

INPUTS

@p_linestring (geometry) - (Multi)LineString geometry. @p_point (geometry) - Offset Point for which closest linestring point measure is needed. @p_round_xy (int) - Decimal digits of precision for XY ordinates. @p_round_zm (int) - Decimal digits of precision for M ordinate.

RESULT

offset (float) - Perpendicular offset distance from point to nearest point on line.

EXAMPLE

select [lrs].[STFindOffset] ( 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) offset_distance union all select [lrs].[STFindOffset] ( 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(10,0,28355), 3,2) go offset_distance 2 0

TODO

Value is negative if on left of line; positive if on right.

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2017 - Original coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STFindPointByLength -- Returns (possibly offset) point geometry at supplied distance along linestring.

SYNOPSIS

Function [$(lrsowner)].[STFindPointByLength] ( @p_linestring geometry, @p_length Float, @p_offset Float = 0.0, @p_radius_check int = 1, @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). If a genenerated point is on the side of the centre of a CircularString ie offset > radius: 0 returns the offset point regardless. 1 causes NULL to be returned; 2 returns centre point; 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.

INPUTS

@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_radius_check (int) - If the offset is greater than the radius inside a CircularString: 1 causes NULL to be returned; 2 returns centre point; 0 returns the offset point regardless. @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

point (geometry) - Point at 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 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, [$(lrsowner)].[STFindPointByLength](a.linestring,g.IntValue,offset.IntValue,0,3,2).AsTextZM() as fPoint from data as a cross apply [$(owner)].[generate_series](0,a.lineString.STLength(),a.linestring.STLength() / 4.0 ) as g cross apply [$(owner)].[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)

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

[ Top ] [ Methods ]

NAME

STFindPointByMeasure -- Returns (possibly offset) point geometry at supplied measure along linestring.

SYNOPSIS

Function [$(lrsowner)].[STFindPointByMeasure] ( @p_linestring geometry, @p_measure Float, @p_offset Float = 0.0, @p_radius_check int = 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). If a genenerated point is on the side of the centre of a CircularString ie offset > radius: 0 returns the offset point regardless. 1 causes NULL to be returned; 2 returns centre point;

NOTES

Supports LineStrings with CircularString elements.

INPUTS

@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 p_units. @p_radius_check (int) - If Point disappears inside a CircularString (offset > radius): 1 causes NULL to be returned; 2 returns centre point; 0 returns the offset point regardless. @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

point (geometry) - Point at provided measure offset to left or right.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

[ Top ] [ Methods ]

NAME

STFindPointsByMeasures -- Returns (possibly offset) point geometry at supplied measure along linestring.

SYNOPSIS

Function [$(lrsowner)].[STFindPointsByMeasures] ( @p_linestring geometry, @p_measure Float, @p_offsets varchar(1000), @p_radius_check int = 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). If a genenerated point is on the side of the centre of a CircularString ie offset > radius: 0 returns the offset point regardless. 1 causes NULL to be returned; 2 returns centre point;

NOTES

Supports LineStrings with CircularString elements.

INPUTS

@p_linestring (geometry) - Linestring geometry with measures. @p_measure (float) - Measure defining position of point to be located. @p_offsets (varchar) - A set of offsets coded in a CSV string eg '-2,0,2' @p_radius_check (int) - If Point disappears in CircularString: 1 causes NULL to be returned; 2 returns centre point; 0 returns the offset point regardless. @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

point (geometry) - Point at provided measure offset to left or right.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

[ Top ] [ Methods ]

NAME

STFindSegmentByLengthRange -- Extracts, and possibly offet, linestring using supplied start and end lengths and @p_offset value.

SYNOPSIS

Function [$(lrsowner)].[STFindSegmentByLengthRange] ( @p_linestring geometry, @p_start_length Float, @p_end_length Float = null, @p_offset Float = 0, @p_radius_check int = 1, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

DESCRIPTION

Given a start and end length, 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). If a genenerated point is on the side of the centre of a CircularString ie offset > radius, @p_radius_check: 0 returns the offset point regardless. 1 causes NULL to be returned; 2 returns centre point;

NOTES

Supports linestrings with CircularString elements.

INPUTS

@p_linestring (geometry) - Linestring geometry. @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 p_units. @p_radius_check (int) - 0 returns the offset point regardless; 1 causes NULL to be returned; 2 returns centre point; @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

line (geometry) - Line between start/end measure with 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 union all select geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0) as linestring union all select geometry::STGeomFromText('MULTILINESTRING ((-4 -4 NULL 0, 0 0 NULL 5.657), (10 0 NULL 5.657, 10 10 NULL 15.657), (11 11 NULL 15.657, 12 12 NULL 17.071))',0) as linestring ) select a.linestring.STGeometryType() as geom_type, CAST(g.IntValue+1 as numeric) / 2.0 as start_length, g.intValue+1.0 as end_length, [$(lrsowner)].[STFindSegmentByLengthRange]( a.linestring, CAST(g.IntValue as numeric) / 2.0, CAST(g.IntValue+1 as numeric), 0.0, 3,2 ).AsTextZM() as fsegment from data as a cross apply generate_series(0, round(a.lineString.STLength(),0), round(a.lineString.STLength(),0)/8.0 ) as g order by geom_type, start_length GO geom_type start_length end_length fsegment CompoundCurve 0.5 1.0 CIRCULARSTRING (2.069 6.687, 0 7, 3 6.325) CompoundCurve 1.5 3.0 CIRCULARSTRING (0.1 6.999, 0 7, 2.069 6.687) CompoundCurve 2.5 5.0 CIRCULARSTRING (-1.876 6.744, 0 7, 1.096 6.914) CompoundCurve 3.5 7.0 COMPOUNDCURVE ((-2.657 5.603, -3 6.325), CIRCULARSTRING (-3 6.325, 0 7, 0.1 6.999)) CompoundCurve 4.5 9.0 COMPOUNDCURVE (CIRCULARSTRING (-0.897 6.942, 0 7, -3 6.325), (-3 6.325, -1.8 3.796)) CompoundCurve 5.5 11.0 COMPOUNDCURVE (CIRCULARSTRING (-1.876 6.744, 0 7, -3 6.325), (-3 6.325, -0.943 1.989)) CompoundCurve 6.5 13.0 COMPOUNDCURVE (CIRCULARSTRING (-2.817 6.408, 0 7, -3 6.325), (-3 6.325, -0.086 0.182)) CompoundCurve 7.5 15.0 LINESTRING (0.771 1.625, 0 0, -2.657 5.603) CompoundCurve 8.5 17.0 LINESTRING (1.628 3.432, 0 0, -2.229 4.699) CompoundCurve 9.5 19.0 LINESTRING (2.485 5.239, 0 0, -1.8 3.796) CompoundCurve 10.5 21.0 LINESTRING (3 6.325, 0 0, -1.372 2.892) LineString 0.5 1.0 LINESTRING (-3.293 -3.293 0 1.81, -4 -4 0 1) LineString 2.0 4.0 LINESTRING (-1.172 -1.172 0 4.25, -2.939 -2.939 0 2.22) LineString 3.5 7.0 LINESTRING (1.343 0 0 6.94, 0 0 0 5.6, -1.879 -1.879 0 3.44) LineString 5.0 10.0 LINESTRING (4.343 0 0 9.95, 0 0 0 5.6, -0.818 -0.818 0 4.66) LineString 6.5 13.0 LINESTRING (7.343 0 0 12.95, 0.343 0 0 5.94) LineString 8.0 16.0 LINESTRING (10 0.343 0 15.95, 10 0 0 15.61, 1.843 0 0 7.44) LineString 9.5 19.0 LINESTRING (10 3.343 0 18.88, 10 0 0 15.61, 3.343 0 0 8.95) LineString 11.0 22.0 LINESTRING (10 6.343 0 21.82, 10 0 0 15.61, 4.843 0 0 10.45) LineString 12.5 25.0 LINESTRING (10 9.343 0 24.76, 10 0 0 15.61, 6.343 0 0 11.95) MultiLineString 0.5 1.0 LINESTRING (-3.293 -3.293 NULL 1, -4 -4 NULL 0) MultiLineString 1.5 3.0 LINESTRING (-1.879 -1.879 NULL 3, -3.293 -3.293 NULL 1) MultiLineString 2.5 5.0 LINESTRING (-0.464 -0.464 NULL 5, -2.586 -2.586 NULL 2) MultiLineString 3.5 7.0 MULTILINESTRING ((10 1.343 NULL 7, 10 0 NULL 5.66), (0 0 NULL 5.66, -1.879 -1.879 NULL 3)) MultiLineString 4.5 9.0 MULTILINESTRING ((10 3.343 NULL 9, 10 0 NULL 5.66), (0 0 NULL 5.66, -1.172 -1.172 NULL 4)) MultiLineString 5.5 11.0 MULTILINESTRING ((10 5.343 NULL 11, 10 0 NULL 5.66), (0 0 NULL 5.66, -0.464 -0.464 NULL 5)) MultiLineString 6.5 13.0 LINESTRING (10 7.343 NULL 13, 10 0.343 NULL 6) MultiLineString 7.5 15.0 LINESTRING (10 9.343 NULL 15, 10 1.343 NULL 7) MultiLineString 8.5 17.0 MULTILINESTRING ((11.95 11.95 NULL 17, 11 11 NULL 15.66), (10 10 NULL 15.66, 10 2.343 NULL 8))

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

[ Top ] [ Functions ]

NAME

STFindSegmentByMeasureRange -- Extracts, and possibly offet, linestring using supplied start and end measures and @p_offset value.

SYNOPSIS

Function [$(lrsowner)].[STFindSegmentByMeasureRange] ( @p_linestring geometry, @p_start_measure Float, @p_end_measure Float = null, @p_offset Float = 0, @p_radius_check int = 1, @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. If a genenerated point is on the side of the centre of a CircularString ie offset > radius: 0 returns the offset point regardless. 1 causes NULL to be returned; 2 returns centre point;

NOTES

Supports linestrings with CircularString elements.

INPUTS

@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_radius_check (int) - 0 returns the offset point regardless; 1 causes NULL to be returned; 2 returns centre point; @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

line (geometry) - Line between start/end measure with offset.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding. Simon Greener - December 2019 - Coalesced circularString and Linestring code under one function; Added @p_radius_check.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STInterpolatePoint -- Returns value of the measure dimension or length of a point on the provided linesting closest to the provided point.

SYNOPSIS

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

DESCRIPTION

This function snaps supplied point to @p_linestring, computes and returns the measure value of the snapped point, or length to the snapped point. @p_linestring can be measured or unmeasured. Returned value is rounded to @p_round_zm decimal digits of precision.

NOTES

IS a wrapper over STFindMeasureByPoint that returns Measure or length. Is implementation of PostGIS ST_InterpolatePoint Supports linestrings with CircularString elements. Srid of @p_linestring and @p_point must be the same.

INPUTS

@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

measure value (float) - Measure of point found on @p_linestring.

EXAMPLE

Print ' [$(lrsowner)].[STInterpolatePoint] ...'; GO Print '....Measured LineStrings'; select [lrs].[STInterpolatePoint] ( 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) as measure union all select [lrs].[STInterpolatePoint] ( 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(10,0,28355), 3,2) as measure GO measure 23.44 15.61 Print '....UnMeasured LineStrings'; select [lrs].[STInterpolatePoint] ( geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355), geometry::Point(8,8,28355), 3,3) as measure union all select [lrs].[STInterpolatePoint] ( geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355), geometry::Point(10,0,28355), 3,3) as measure GO measure 23.657 15.657

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original coding. Simon Greener - December 2017 - Port to SQL Server (TSQL).

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

STIsMeasured -- Function checks if supplied linestring has measures.

SYNOPSIS

Function [$(lrsowner)].[STIsMeasured] ( @@p_geometry geometry ) Returns varchar(5)

USAGE

WITH data AS ( select geometry::STGeomFromText('COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 0, 0 7 1.1 3.1, -3 6.3 1.1 9.3), (-3 6.3 1.1 9.3, 0 0 1.4 16.3, 3 6.3 1.6 20.2))',0) as Geom union all select geometry::STGeomFromText('LINESTRING(0 0, 100 100)',0) union all select geometry::STGeomFromText('LINESTRING(0 0 0.1, 100 100 99.8)',0) union all select geometry::STGeomFromText('LINESTRING(0 0 0 0.1, 100 100 0 99.8)',0) union all select geometry::STPointFromText('POINT(0 0)',0) union all select geometry::STPointFromText('POINT(0 0 1.1)',0) union all select geometry::STPointFromText('POINT(0 0 1.1 2.2)',0) ) SELECT d.geom.STGeometryType() as gType, [dbo].[STCoordDim](d.geom) as cDim, [lrs].[STIsMeasured]( d.geom ) as isMeasured FROM data as d; GO gType cDim isMeasured CompoundCurve 4 TRUE LineString 2 FALSE LineString 3 FALSE LineString 4 TRUE Point 2 FALSE Point 3 FALSE Point 4 TRUE

DESCRIPTION

Returns TRUE if @p_linestring has measures, FALSE otherwise. Supports CircularString and CompoundCurve geometry objects and subelements from 2012 onewards.

INPUTS

@p_geometry (geometry) - Supplied geometry.

RESULT

BOOLEAN (varchar5) - True if @p_geometry has measures otherwise False.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2018 - Original Coding.

COPYRIGHT

(c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STIsMeasureDecreasing -- Checks if M values decrease in value over the whole linestring.

SYNOPSIS

Function STIsMeasureDecreasing ( @p_linestring geometry ) Returns varchar(5)

DESCRIPTION

Checks all measures of all vertices in a linestring from start to end. Computes difference between each pair of measures. If all measure differences decrease then TRUE is returned, otherwise FALSE. For non-measured line-strings the value is always TRUE. Supports Linestrings with CircularString elements (2012).

RESULT

boolean (varchar 5) - TRUE if measures are decreasing, FALSE otherwise.

EXAMPLE

select [lrs].[STIsMeasureDecreasing](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3,2 2 3 4),(3 3 4 5,4 4 5 6))',0)) as is_decreasing union all select [lrs].[STIsMeasureDecreasing](geometry::STGeomFromText('MULTILINESTRING((4 4 5 6,3 3 4 5),(2 2 3 4,1 1 2 3))',0)) as is_decreasing union all select [lrs].[STIsMeasureDecreasing](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0)) as is_decreasing GO is_decreasing FALSE TRUE FALSE

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Port to SQL Server.

COPYRIGHT

(c) 2012-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STIsMeasureIncreasing -- Checks if M values increase in value over the whole linestring.

SYNOPSIS

Function STIsMeasureIncreasing ( @p_linestring geometry ) Returns varchar(5)

DESCRIPTION

Checks all measures of all vertices in a linestring from start to end. Computes difference between each pair of measures. If all measure differences increase then TRUE is returned, otherwise FALSE. For non-measured line-strings the value is always TRUE. Supports Linestrings with CircularString elements (2012).

RESULT

boolean (varchar 5) - TRUE if measures are increasing, FALSE otherwise.

EXAMPLE

select [lrs].[STIsMeasureIncreasing](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0)) as is_increasing union all select [lrs].[STIsMeasureIncreasing]([dbo].[STReverse](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0),1,1)) as is_increasing union all select [lrs].[STIsMeasureIncreasing](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0)) as is_increasing GO is_increasing TRUE FALSE TRUE

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Port to SQL Server.

COPYRIGHT

(c) 2012-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STLineInterpolatePoint -- Returns point geometry at supplied fraction along linestring.

SYNOPSIS

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.

INPUTS

@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

point (geometry) - Point at provided measure/length fraction from start.

EXAMPLE

-- 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, 0, @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)

AUTHOR

Simon Greener

HISTORY

Simon Greener - July 2019 - Original Coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

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

DESCRIPTION

Given a point near a the supplied measure @p_linestring, this function returns the measure/length ratio of the found positions.

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.

INPUTS

@p_linestring (geometry) - Measured linestring 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

ratio value (float) - Ratio of point found on @p_linestring.

EXAMPLE

select [$(lrsowner)].[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.61

AUTHOR

Simon Greener

HISTORY

Simon Greener - July 2019

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

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

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.

RESULT

line (geometry) - Line between 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)

AUTHOR

Simon Greener

HISTORY

Simon Greener - July 2019 - Original Coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

Function [$(lrsowner)].[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. Only the measure's first point is returned as measures are assume to be organised in ascending or descending order and so are unique. 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

Implements PostGIS's ST_LocateAlong(geometry ageom_with_measure, float8 a_measure, float8 offset) except that because measures are assumed to be organised in ascending or descending order and so are unique, only one point can be returned and not multiple as in the PostGIS example. Supports LineStrings with CircularString elements.

INPUTS

@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

point (geometry) - Point at provided measure optionally offset to left or right.

EXAMPLE

-- Linestring. 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)',28355) as linestring ) select g.intValue as measure, o.IntValue as offset, [$(lrsowner)].[STLocateAlong](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), 2 ) as g cross apply [dbo].[generate_series](-1,1,1) as o union all select g.intValue as measure, o.IntValue as offset, [$(lrsowner)].[STLocateAlong](linestring, linestring.STPointN(g.IntValue).M, o.IntValue,3,2).AsTextZM() as fPoint from data as a cross apply [dbo].[generate_series](1, a.lineString.STNumPoints(), 1 ) as g cross apply [dbo].[generate_series](-1,1,1) as o 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)

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

Function [$(lrsowner)].[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).

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

line (geometry) - Line between start/end measure with offset.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

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

INPUTS

@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

geometry (geometry) - Geometry of the appropriate type.

EXAMPLE

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

AUTHOR

Simon Greener

HISTORY

Simon Greener - July 2019 - Original Coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STMeasureRange -- Returns (Last Point M Value) - (First Point M Value) or length if not measured.

SYNOPSIS

Function STMeasureRange ( @p_linestring geometry ) Returns varchar(5)

DESCRIPTION

If @p_linestring is measured, the function returns end point measure value - start point measure value. If line-string not measured, returns length of line.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry.

RESULT

measure range (float) - Measure range for measured line-string; returns NULL if not measured.

NOTES

If @p_linestring is not measured, the function will return STLength.

EXAMPLE

select [lrs].[STMeasureRange](geometry::STGeomFromText('LINESTRING(1 1, 2 2)', 0)) as range union all select [lrs].[STMeasureRange](geometry::STGeomFromText('LINESTRING(1 1 2 3, 2 2 3 4)', 0)) as range union all select [lrs].[STMeasureRange](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))', 0)) union all select [lrs].[STMeasureRange](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0)) GO range NULL 1 3 6.15

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Port to SQL Server.

COPYRIGHT

(c) 2012-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STMeasureToPercentage --Converts supplied measure value to a percentage.

SYNOPSIS

Function STMeasureToPercentage ( @p_linestring geometry, @p_measure Float ) Returns varchar(5)

DESCRIPTION

The end measure minus the start measure of a measured line-string defines the range of the measures (see ST_Measure_Range). The supplied measure is divided by this range and multiplied by 100 to return the measure as a percentage. For non measured line-strings all values are computed using lengths.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @p_measure (float) - Measure somewhere within linestring.

RESULT

Percentage (float) - Returns measure within measure range of linestring as percentage (0..100)

EXAMPLE

select [lrs].[STMeasureToPercentage](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0),4) as percentage union all select [lrs].[STMeasureToPercentage](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0),5) union all select [lrs].[STMeasureToPercentage](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0),6.15) GO percentage 33.3333333333333 66.6666666666667 100

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Port to SQL Server.

COPYRIGHT

(c) 2012-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STPercentageToMeasure -- Converts supplied Percentage value to a Measure.

SYNOPSIS

Function STPercentageToMeasure ( @p_linestring geometry, @p_percentage Float ) Returns varchar(5)

DESCRIPTION

The supplied percentage value (between 0 and 100) is multipled by the measure range (see STMeasureRange) to return a measure value between the start and end measures. For non measured line-strings all values are computed using lengths.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @p_percentage (float) - Percentage within linestring: from 0 to 100.

RESULT

Measure value (float) - Measure at the provided percentage along the linestring.

EXAMPLE

select 50.0 as percentage, [lrs].[STStartMeasure](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0)) as start_measure, [lrs].[STEndMeasure](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0)) as end_measure, [lrs].[STPercentageToMeasure](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0),50) as measure union all select 80.0 as percentage, [lrs].[STStartMeasure](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0)) as start_measure, [lrs].[STEndMeasure](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0)) as end_measure, [lrs].[STPercentageToMeasure](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))',0),80) union all select 10.0 as percentage, [lrs].[STStartMeasure](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0)) as start_measure, [lrs].[STEndMeasure](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0)) as end_measure, [lrs].[STPercentageToMeasure](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0),10) GO percentage start_measure end_measure measure 50.0 3 6 4.5 80.0 3 6 5.4 10.0 0 6.15 0.615

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Port to SQL Server.

COPYRIGHT

(c) 2012-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

STPointToCircularArc -- Return a measured point by snapping provided point to the provided circularstring

SYNOPSIS

Function [$(lrsowner)].[STPointToCircularArc] ( @p_circularString geometry, @p_point 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.

INPUTS

@p_circularString (geometry) - (Measured) CircularString 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

snapped point (geometry) -- First point found on @p_circularString.

EXAMPLE

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)

AUTHOR

Simon Greener

HISTORY

Simon Greener - August 2019 - Original coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

STProjectPoint -- Return a measured point by snapping provided point to the provided line.

SYNOPSIS

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

INPUTS

@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

snapped point (geometry) -- First point found on @p_linestring.

EXAMPLE

select CAST('Actual Measure' as varchar(50)) 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 union all select 'Point has relationship with XYZM circular arc' as test, [lrs].[STProjectPoint] ( 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].[STProjectPoint] ( 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].[STProjectPoint] ( 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].[STProjectPoint] ( geometry::STGeomFromText('CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246)',0), geometry::Point(0,3.5,0), 3,2).AsTextZM() as project_point select 'Closest to LineString' as test, [lrs].[STProjectPoint] ( geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0), geometry::Point(-1,1,0), 3,2).AsTextZM() as project_point Union all select 'Closest to CircularString' as test, [lrs].[STProjectPoint] ( geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0), geometry::Point(2,8,0), 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) 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) Closest to LineString POINT (-0.571 1.204 NULL 5.67) Closest to CircularString POINT (1.698 6.791 NULL 1.39)

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original coding. Simon Greener - December 2017 - Port to SQL Server (TSQL). Simon Greener - August 2019 - Added support for CircularArcs and CompoundCurves.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STRemoveMeasure -- Function that removes measure values from all points in linestring.

SYNOPSIS

Function STRemoveMeasure ( @p_linestring geometry, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

USAGE

SELECT [$(lrsowner).[STRemoveMeasure] ( geometry::STGeomFromText('MULTILINESTRING((1 1 NULL 1,2 2 NULL 2),(3 3 NULL 3,4 4 NULL 4))',0), 3, 2).AsTextZM() as RemoveMeasureLine GO RemoveMeasureLine MULTILINESTRING ((1 1 NULL -999999999, 2 2 NULL -999999999), (3 3 NULL -999999999, 4 4 NULL -999999999))

DESCRIPTION

Removes all measure ordinate values. Linestring with XYM ordinates is returned with XY ordinates. Linestring with XYZM ordinates is returned with XYZ ordinates. Supports CircularString and CompoundCurve geometry objects and subelements from 2012 onewards.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @p_round_xy (int) - Decimal degrees of precision for when formatting XY ordinates in WKT. @p_round_zm (int) - Decimal degrees of precision for when formatting Z ordinate in WKT.

RESULT

linestring with no M (geometry) - Input geometry with all M ordinates set to NULL.

EXAMPLE

select [lrs].[STRemoveMeasure] ( geometry::STGeomFromText('LINESTRING(1 1 NULL 1,2 2 NULL 2)',0), 3, 2 ).AsTextZM() as RemoveMeasureLine union all select [lrs].[STRemoveMeasure] ( geometry::STGeomFromText('LINESTRING(1 1 1 1,2 2 2 2)',0), 3, 2 ).AsTextZM() as RemoveMeasureLine union all select [lrs].[STRemoveMeasure] ( geometry::STGeomFromText('MULTILINESTRING((1 1 NULL 1,2 2 NULL 2),(3 3 NULL 3,4 4 NULL 4))',0), 3, 2 ).AsTextZM() as RemoveMeasureLine union all select [lrs].[STRemoveMeasure] ( geometry::STGeomFromText('MULTILINESTRING((1 1 1 1,2 2 2 2),(3 3 3 3,4 4 4 4))',0), 3, 2 ).AsTextZM() as RemoveMeasureLine GO RemoveMeasureLine LINESTRING (1 1, 2 2) LINESTRING (1 1 1, 2 2 2) MULTILINESTRING ((1 1, 2 2), (3 3, 4 4)) MULTILINESTRING ((1 1 1, 2 2 2), (3 3 3, 4 4 4))

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2018 - Original Coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STResetMeasure -- Function that sets all existing assigned measures to NULL.

SYNOPSIS

Function STResetMeasure ( @p_geometry geometry, @p_marker_measure float = -999999999, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

USAGE

SELECT [$(lrsowner).[STResetMeasure] ( geometry::STGeomFromText('MULTILINESTRING((1 1 NULL 1,2 2 NULL 2),(3 3 NULL 3,4 4 NULL 4))',0), default, 3, 2).AsTextZM() as ResetMeasureLine GO ResetMeasureLine MULTILINESTRING ((1 1 NULL -999999999, 2 2 NULL -999999999), (3 3 NULL -999999999, 4 4 NULL -999999999))

DESCRIPTION

Sets all measures of a measured linesting to null values leaving dimensionality of geometry alone. So, a linestring with XYM remains so, but all measures are set to @c_MarkerMeasure value of -999999999 unless user supplies otherwise. eg Coord 2 of 10.23,5.75,2.65 => 10.23,5.75,-999999999 Supports CircularString and CompoundCurve geometry objects and subelements from 2012 onewards.

NOTES

This is not the same as STTo2D which removes measures etc and returns a pure 2D geometry.

INPUTS

@p_geometry (geometry) - Supplied Linestring geometry. @p_marker_measure (float) - Marker Measure applied to all measure values: Default is -999999999, @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

M Null Geom (geometry) - Input geometry with all M ordinates set to NULL.

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Converted to TSQL for SQL Spatial.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STReverseMeasure -- Function that reverses measures assigned to the points of a linestring.

SYNOPSIS

Function STReverseMeasure ( @p_linestring geometry, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

USAGE

WITH data AS ( select geometry::STGeomFromText('COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 0, 0 7 1.1 3.1, -3 6.3 1.1 9.3), (-3 6.3 1.1 9.3, 0 0 1.4 16.3, 3 6.3 1.6 20.2))',0) as Geom ) SELECT 'Before' as text, d.geom.AsTextZM() as rGeom from data as d UNION ALL SELECT 'After' as text, [$(lrsowner)].[STReverseMeasure](d.geom,3,2).AsTextZM() as rGeom from data as d; GO text rGeom ------ ------------------------------------------------------------------------------------------------------------------------------- Before COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 0, 0 7 1.1 3.1, -3 6.3 1.1 9.3), (-3 6.3 1.1 9.3, 0 0 1.4 16.3, 3 6.3 1.6 20.2)) After COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 20.2, 0 7 1.1 17.1, -3 6.3 1.1 24.9), (-3 6.3 1.1 24.9, 0 0 1.4 17.9, 3 6.3 1.6 0))

DESCRIPTION

Reverses measures assigned to a linestring. Supports CircularString and CompoundCurve geometry objects and subelements from 2012 onewards.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @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

Reverse M Geom (geometry) - Input geometry with all points' M ordinates reversed.

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Converted to TSQL for SQL Spatial. Simon Greener - December 2019 - Fixed bug with handling CircularString.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STScaleMeasure -- Rescales geometry measures and optionally offsets them, stretching the geometry.

SYNOPSIS

Function [$(lrsowner)].[STScaleMeasure] ( @p_linestring geometry, @p_start_measure Float, @p_end_measure Float, @p_shift_measure Float = 0.0, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

USAGE

WITH data AS ( SELECT [$(lrsowner)].[STAddMeasure] ( geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246 1.1, 0 7 1.1, -3 6.3246 1.1),(-3 6.3246 1.1, 0 0 1.4, 3 6.3246 1.55))',0), 0,null,1,1) as aGeom ) SELECT 'Original' as LineType, f.aGeom.AsTextZM() as mLine FROM data as f UNION ALL SELECT 'Scaled' as LineType, [$(lrsowner)].[STScaleMeasure] ( f.ageom, 100.0, 125.1, 5.0, 3, 2).AsTextZM() as sGeom FROM data as f; GO LineType mLine -------- -------------------------------------------------------------------------------------------------------------------------------------------- Original COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 0, 0 7 1.1 3.1, -3 6.3 1.1 6.2), (-3 6.3 1.1 6.2, 0 0 1.4 13.2, 3 6.3 1.6 20.2)) Scaled COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 105, 0 7 1.1 108.852, -3 6.3 1.1 112.704), (-3 6.3 1.1 112.704, 0 0 1.4 121.402, 3 6.3 1.6 125.1))

DESCRIPTION

This function can redistribute measure values between the supplied @p_start_measure (start vertex) and @p_end_measure (end vertex) by adjusting/scaling the measure values of all in between coordinates. In addition, if @p_shift_measure is not 0 (zero), the supplied value is added to each modified measure value performing a translation/shift of those values.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @p_start_measure (float) - Measure defining start point for geometry. @p_end_measure (float) - Measure defining end point for geometry. @p_shift_measure (float) - Shift (scale) value applied to all measure. @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

Scaled M Line (geometry) - Input geometry with all points' M ordinates scaled.

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Converted to TSQL for SQL Spatial.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STSetM -- Function that adds or updates (replaces) M value of supplied geometry point.

SYNOPSIS

Function STSetM ( @p_point geometry, @p_measure float, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

USAGE

SELECT STSetM( STPointFromText('POINT(0 0)',0), 1, 3, 2 ).AsTextZM() as updatedPoint; # updatedPoint 'POINT(0 0 NULL 1)'

DESCRIPTION

Function that adds/updates M ordinate of the supplied @p_point. The updated coordinate's XY ordinates are rounded to @p_round_xy number of decimal digits of precision. The updated coordinate's ZM ordinates are rounded to @p_round_ZM number of decimal digits of precision.

NOTES

Wrapper over STSetMeasure.

INPUTS

@p_point (geometry) - Supplied point geometry. @p_measure (float) - New M value. @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

point with M (geometry) - Input point with new measure value.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original TSQL Coding for SQL Spatial.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STSetMeasure -- Function that adds or updates (replaces) M value of supplied geometry point.

SYNOPSIS

Function STSetMeasure ( @p_point geometry, @p_measure float, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

USAGE

SELECT STSetMeasure( STPointFromText('POINT(0 0)',0), 1, 3, 2 ).AsTextZM() as updatedPoint; # updatedPoint 'POINT(0 0 NULL 1)'

DESCRIPTION

Function that adds/updates M ordinate of the supplied @p_point. The updated coordinate's XY ordinates are rounded to @p_round_xy number of decimal digits of precision. The updated coordinate's ZM ordinates are rounded to @p_round_ZM number of decimal digits of precision.

INPUTS

@p_point (geometry) - Supplied point geometry. @p_measure (float) - New M value. @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

point with M (geometry) - Input point with new measure value.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original TSQL Coding for SQL Spatial.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

STSplit -- Function that splits a line into two parts.

SYNOPSIS

Function [$(lrsowner)].[STSplit] ( @p_linestring geometry, @p_point geometry, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns @lines TABLE ( line1 geometry, line2 geometry )

DESCRIPTION

Splits @p_linestring at position defined by @p_point. If @p_point is not on the line it is first snapped to the line. Supports CircularString and CompoundCurve geometry objects and subelements from 2012 onewards.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @p_point (geometry) - Supplied split point. @p_round_xy (int) - Decimal degrees of precision for when formatting XY ordinates in WKT. @p_round_zm (int) - Decimal degrees of precision for when formatting Z ordinate in WKT.

RESULT

Single record containing one or two linestrings

EXAMPLE

with data as ( select geometry::STGeomFromText('LINESTRING(0 0,10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90,100 100)',0) as line, geometry::STGeomFromText('POINT(50 50)',0) as point ) select s.line1.AsTextZM() as line1, s.line2.AsTextZM() as line2 from data as a cross apply [$(lrsowner)].[STSplit]( a.line, a.point, 3, 2 ) as s; line1 line2 LINESTRING (0 0, 10 10, 20 20, 30 30, 40 40, 50 50) LINESTRING (50 50, 60 60, 70 70, 80 80, 90 90, 100 100)

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2018 - Original Coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

STSplitProcedure -- Procedure that splits a line into two parts.

SYNOPSIS

Function STSplitProcedure ( @p_linestring geometry, @p_point geometry, @p_line1 geometry OUTPUT, @p_line2 geometry OUTPUT, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

DESCRIPTION

Splits @p_linestring at position defined by @p_point. If @p_point is not on the line it is first snapped to the line. Supports CircularString and CompoundCurve geometry objects and subelements from 2012 onewards.

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @p_point (geometry) - Supplied split point. @p_line1 (geometry) - Is an OUTPUT parameter that holds the first part of split line. @p_line2 (geometry) - Is an OUTPUT parameter that holds the second part of split line. @p_round_xy (int) - Decimal degrees of precision for when formatting XY ordinates in WKT. @p_round_zm (int) - Decimal degrees of precision for when formatting Z ordinate in WKT.

RESULT

Two linestrings (geometry) - Two parts of split linestring.

EXAMPLE

declare @v_linestring geometry = geometry::STGeomFromText('LINESTRING(0 0,10 10,20 20,30 30,40 40,50 50,60 60,70 70,80 80,90 90,100 100)',0), @v_point geometry = geometry::STGeomFromText('POINT(50 50)',0), @v_line1 geometry, @v_line2 geometry; exec lrs.STSplitProcedure @p_linestring=@v_linestring, @p_point=@v_point, @p_line1=@v_line1 OUTPUT, @p_line2=@v_line2 OUTPUT, @p_round_xy=3, @p_round_zm=2; select @v_line1.STAsText() as line1, @v_line2.STAsText() as line2 GO line1 line2 LINESTRING (0 0, 10 10, 20 20, 30 30, 40 40, 50 50) LINESTRING (50 50, 60 60, 70 70, 80 80, 90 90, 100 100)

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2018 - Original Coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STSplitSegmentByLength -- Extracts, and possibly offets, that part of the supplied (single) LineString identified by the @p_start_length and @p_end_length parameters.

SYNOPSIS

Function [$(lrsowner)].[STSplitStringByLength] ( @p_linestring geometry, @p_start_length Float, @p_end_length Float = null, @p_offset Float = 0.0, @p_radius_check Bit = 1, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

DESCRIPTION

Given start and end lengths, this function extracts a new LineString segment from the input @p_linestring. If a non-zero value is supplied for @p_offset, the extracted LineString is then offset to the left (if @p_offset < 0) or to the right (if @p_offset > 0). If the circularString offset causes the CircularString to disappear, NULL is returned. If a genenerated point is on the side of the centre of a CircularString ie offset > radius: 0 returns the offset point regardless. 1 causes NULL to be returned; 2 returns centre point;

NOTES

Supports a single (2-point) LineString element only.

INPUTS

@p_linestring (geometry) - A single, 2 point, LineString. @p_start_length (float) - Measure defining start point of located geometry. @p_end_length (float) - Measure defining end point of located geometry. @p_offset (float) - Offset (distance) value left (negative) or right (positive) in SRID units. @p_radius_check (int) - 0 returns the offset point regardless; 1 causes NULL to be returned; 2 returns centre point; @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

LineString (geometry) - New Linestring between start/end lengths with optional offset.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding. Simon Greener - December 2019 - Coalesced circularString and Linestring code under one function; Added @p_radius_check.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STSplitSegmentByMeasure -- Extracts, and possibly offets, that part of the supplied (single) CircularString identified by the @p_start_measure and @p_end_measure parameters.

SYNOPSIS

Function [$(lrsowner)].[STSplitSegmentByMeasure] ( @p_linestring geometry, @p_start_measure Float, @p_end_measure Float = null, @p_offset Float = 0.0, @p_radius_check int = 1, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

DESCRIPTION

Given start and end measures, this function extracts a new CircularString segment from the @p_linestring. If a non-zero value is suppied for @p_offset, the extracted circularSting is then offset to the left (if @p_offset < 0) or to the right (if @p_offset > 0). If a genenerated point is on the side of the centre of a CircularString ie offset > radius: 0 returns the offset point regardless. 1 causes NULL to be returned; 2 returns centre point;

NOTES

Supports a single (3-point) CircularString element only. Currently only supports Increasing measures.

INPUTS

@p_linestring (geometry) - A single, 3 point, CircularString. @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_radius_check (int) - 0 returns the offset point regardless; 1 causes NULL to be returned; 2 returns centre point; @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

CircularString (geometry) - New CircularString between start/end measure with optional offset.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding. Simon Greener - December 2019 - Merged LineString/CircularString code. Added @p_radius_check.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STStartMeasure -- Returns M value of first point in measured geometry.

SYNOPSIS

Function STStartMeasure ( @p_linestring geometry ) Returns geometry

DESCRIPTION

Returns start measure associated with first point in a measured line-string. Supports Linestrings with CircularString elements (2012).

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry.

RESULT

measure (float) -- Measure value of first point in a measured line-string.

NOTES

If the line-string is not measured it returns 0.

EXAMPLE

select [$(lrsowner)].[STStartMeasure](geometry::STGeomFromText('LINESTRING(1 1 2 3, 2 2 3 4)', 0)) as start_measure union all select [$(lrsowner)].[STStartMeasure](geometry::STGeomFromText('MULTILINESTRING((1 1 2 3, 2 2 3 4),(3 3 4 5,4 4 5 6))', 0)) union all select [$(lrsowner)].[STStartMeasure](geometry::STGeomFromText('CIRCULARSTRING (3 6.325 NULL 0, 0 7 NULL 3.08, -3 6.325 NULL 6.15)',0)) GO start_measure 3 3 0

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Port to SQL Server.

COPYRIGHT

(c) 2012-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STUpdateMeasure -- Rescales (multi)linestring vertex measures from start to end using provided values.

SYNOPSIS

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

USAGE

WITH data AS ( SELECT [$(lrsowner)].[STAddMeasure] ( geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246 1.1, 0 7 1.1, -3 6.3246 1.1),(-3 6.3246 1.1, 0 0 1.4, 3 6.3246 1.55))',0), 0,null,1,1) as aGeom ) SELECT 'Original' as LineType, f.aGeom.AsTextZM() as mLine FROM data as f UNION ALL SELECT 'Updated (1)' as LineType, [$(lrsowner)].[STUpdateMeasure] ( f.ageom, 1.0, 21.2, 3, 2).AsTextZM() as sGeom FROM data as f UNION ALL SELECT 'Updated (2)' as LineType, [$(lrsowner)].[STUpdateMeasure] ( f.ageom, 100.0, 125.1, 3, 2).AsTextZM() as sGeom FROM data as f; GO LineType mLine -------- --------------------------------------------------------------------------------------------------------------------------------------- Original COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 0, 0 7 1.1 3.1, -3 6.3 1.1 6.2), (-3 6.3 1.1 6.2, 0 0 1.4 13.2, 3 6.3 1.6 20.2)) Updated (1) COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 1, 0 7 1.1 4.1, -3 6.3 1.1 7.2), (-3 6.3 1.1 7.2, 0 0 1.4 14.2, 3 6.3 1.6 21.2)) Updated (2) COMPOUNDCURVE (CIRCULARSTRING (3 6.3 1.1 100, 0 7 1.1 103.85, -3 6.3 1.1 107.7), (-3 6.3 1.1 107.7, 0 0 1.4 116.4, 3 6.3 1.6 125.1))

DESCRIPTION

This function resets all measures in a measured linestring by applying @p_start_measure to the start vertex and @p_end_measure to the end vertex. All vertices in between have the measures scaled between the start and end measures by ratio based on length. All M ordinates are rounded to supplied @p_round_zm. NOTE Is a wrapper over [$(lrsowner)].[STScaleMeasure].

INPUTS

@p_linestring (geometry) - Supplied Linestring geometry. @p_start_measure (float) - Measure defining start point for geometry. @p_end_measure (float) - Measure defining end point for geometry. @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

Scaled M Line (geometry) - Input linestring with all points' M ordinates updated.

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding. Simon Greener - December 2017 - Converted to TSQL for SQL Spatial.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

STValidLrsGeometry -- Checks if supplied @p_linestring is a valid measured linestring.

SYNOPSIS

Function STValidLrsGeometry ( @p_linestring geometry ) Returns bit

DESCRIPTION

This function checks for geometry type and number of dimensions of the geometric segment. Function returns 1 (true) if provided geometry is a linestring with valid measured, and 0 (false) otherwise. Linestring must have either increasing or decreasing measures. The function supports all Linestring geometry types.

INPUTS

@p_linestring (geometry) - Measured Linestring.

RESULT

1/0 (bit) - 1 (true) if measured linestring, 0 (false) otherwise.

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2017 - Original coding. Simon Greener - December 2017 - Port to SQL Server

COPYRIGHT

(c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STValidLrsPoint -- Checks if supplied @p_point is a valid LRS point.

SYNOPSIS

Function STValidLrsPoint ( @p_point geometry ) Returns bit

DESCRIPTION

Function returns 1 (true) if point is measured, and 0 (false) if point is not measured. A valid LRS point has measure information. The function checks for the Point geometry type and has a measured ordinate.

INPUTS

@p_point (geometry) - Measured Point.

RESULT

1/0 (bit) - 1 (true) if measured point, 0 (false) otherwise.

EXAMPLE

select [lrs].[STValidLrsPoint](geometry::STGeomFromText('POINT(0 0)',0)) as is_measured_point GO is_measured_point 0 select [lrs].[STValidLrsPoint](geometry::STGeomFromText('POINT(0 0 NULL 1)',0)) as is_measured_point GO is_measured_point 1

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2017 - Original coding. Simon Greener - December 2017 - Port to SQL Server

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STValidMeasure -- Checks if supplied measure falls within the linestring's measure range.

SYNOPSIS

Function STValidMeasure ( @p_linestring geometry, @p_measure float ) Returns bit

DESCRIPTION

Function returns 1 (true) if measure falls within the underlying linestring's measure range or the 0 (false) string if the supplied measure does not fall within the measure range. Support All Linestring geometry types (including CircularString and CompoundCurve

INPUTS

@p_linestring (geometry) - Measured linestring. @p_measure (float) - Actual Measure value.

RESULT

1/0 (bit) - 1 (true) if measure within range, 0 (false) otherwise.

EXAMPLE

select t.IntValue, case when [lrs].[STValidMeasure](geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355), cast(t.intValue as float) ) = 1 then 'Yes' else 'No' end as isMeasureWithinLinestring from [dbo].[Generate_Series] (-1,30,4) as t; GO IntValue isMeasureWithinLinestring -1 No 3 Yes 7 Yes 11 Yes 15 Yes 19 Yes 23 Yes 27 No

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2017 - Original coding. Simon Greener - December 2017 - Port to SQL Server

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener