- 1. LRS/STAddMeasure (2012)
- 2. LRS/STEndMeasure (2012)
- 3. LRS/STFilterLineSegmentByLength (2012)
- 4. LRS/STFilterLineSegmentByMeasure (2012)
- 5. LRS/STFindArcPointByMeasure (2012)
- 6. LRS/STFindMeasure (2012)
- 7. LRS/STFindMeasureByPoint (2012)
- 8. LRS/STFindOffset (2012)
- 9. LRS/STFindPointByLength (2012)
- 10. LRS/STFindPointByMeasure (2012)
- 11. LRS/STFindPointByRatio (2012)
- 12. LRS/STFindSegmentByLengthRange (2012)
- 13. LRS/STFindSegmentByMeasureRange (2012)
- 14. LRS/STInterpolatePoint (2012)
- 15. LRS/STIsMeasureDecreasing (2012)
- 16. LRS/STIsMeasureIncreasing (2012)
- 17. LRS/STIsPseudoMultiCurve (2012)
- 18. LRS/STLocateAlong (2012)
- 19. LRS/STLocateBetween (2012)
- 20. LRS/STMeasureRange (2012)
- 21. LRS/STMeasureToPercentage (2012)
- 22. LRS/STPercentageToMeasure (2012)
- 23. LRS/STProjectPoint (2012)
- 24. LRS/STRemoveMeasure (2012)
- 25. LRS/STResetMeasure (2012)
- 26. LRS/STReverseMeasure (2012)
- 27. LRS/STScaleMeasure (2012)
- 28. LRS/STSetM (2012)
- 29. LRS/STSetMeasure (2012)
- 30. LRS/STSplit (2012)
- 31. LRS/STSplitCircularStringByLength (2012)
- 32. LRS/STSplitCircularStringByMeasure (2012)
- 33. LRS/STSplitLineSegmentByLength (2012)
- 34. LRS/STSplitLineSegmentByMeasure (2012)
- 35. LRS/STSplitProcedure (2012)
- 36. LRS/STStartMeasure (2012)
- 37. LRS/STValidLrsGeometry (2012)
- 38. LRS/STValidLrsPoint (2012)
- 39. LRS/STValidMeasure (2012)

[ 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. If the line-string is not measured it returns 0. 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.

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

STFilterLineSegmentByLength -- This function detects and returns all segments (2 point linestring, 3 point circularString) that fall within the defined by the range @p_start_length .. @p_end_length .

SYNOPSIS

Function [$(lrsowner)].[STFilterLineSegmentByLength] ( @p_linestring geometry, @p_start_length Float, @p_end_length Float = null, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns @Segments TABLE ( id int, multi_tag varchar(100), element_id int, element_tag varchar(100), subelement_id int, subelement_tag varchar(100), segment_id int, sx float, sy float, sz float, sm float, mx float, my float, mz float, mm float, ex float, ey float, ez float, em float, length float, startLength float, measureRange float, geom geometry )

DESCRIPTION

Given a start and end length, this function breaks the input @p_linestring into its fundamental 2 Point LineString or 3 Point CircularStrings. If then analyses each segment to see if it falls within the range defined by @p_start_length .. @p_end_length. If the segment falls within the range, it is returned. If a segment's end point = @p_start_length then it is not returned but the next segment, whose StartPoint = @p_start_length is returned.

NOTES

Supports linestrings with CircularString elements.

INPUTS

@p_linestring (geometry) - Linestring geometry. @p_start_length (float) - Length defining start point of located geometry. @p_end_length (float) - Length defining end point of located 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

Table (Array) of Indivitual Line Segments: id (int) - Unique identifier starting at segment 1. multi_tag (varchar100) - WKT Tag if Multi geometry eg MULTILINESTRING/MULTICURVE/MULTIPOLYGON. element_id (int) - Top level element identifier eg 1 for first polygon in multiPolygon. element_tag (varchar100) - WKT Tag for first element eg POLYGON if part of MULTIPOlYGON. subelement_id (int) - SubElement identifier of subelement of element with parts eg OuterRing of Polygon subelement_tag (varchar100) - WKT Tag for first subelement of element with parts eg OuterRing of Polygon segment_id (int) - Unique identifier starting at segment 1 for each element. sx (float) - Start Point X Ordinate sy (float) - Start Point Y Ordinate sz (float) - Start Point Z Ordinate sm (float) - Start Point M Ordinate mx (float) - Mid Point X Ordinate (Only if CircularString) my (float) - Mid Point Y Ordinate (Only if CircularString) mz (float) - Mid Point Z Ordinate (Only if CircularString) mm (float) - Mid Point M Ordinate (Only if CircularString) ex (float) - End Point X Ordinate ey (float) - End Point Y Ordinate ez (float) - End Point Z Ordinate em (float) - End Point M Ordinate length (float) - Length of this segment in SRID units startLength (float) - Cumulative Length (from start of geometry) at the start of this segment in SRID units measureRange (float) - Measure Range ie EndM - StartM geom (geometry) - Geometry representation of segment.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STFilterLineSegmentByMeasure -- This function detects and returns all segments (2 point linestring, 3 point circularString) that fall within the defined by the range @p_start_measure .. @p_end_measure .

SYNOPSIS

Function [$(lrsowner)].[STFilterLineSegmentByMeasure] ( @p_linestring geometry, @p_start_measure Float, @p_end_measure Float = null, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns @Segments TABLE ( id int, multi_tag varchar(100), element_id int, element_tag varchar(100), subelement_id int, subelement_tag varchar(100), segment_id int, sx float, sy float, sz float, sm float, mx float, my float, mz float, mm float, ex float, ey float, ez float, em float, length float, startLength float, measureRange float, geom geometry )

DESCRIPTION

Given a start and end length, this function breaks the input @p_linestring into its fundamental 2 Point LineString or 3 Point CircularStrings. If then analyses each segment to see if it falls within the range defined by @p_start_measure .. @p_end_measure. If the segment falls within the range, it is returned. If a segment's end point = @p_start_measure then it is not returned but the next segment, whose StartPoint = @p_start_measure is returned.

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

Table (Array) of Indivitual Line Segments: id (int) - Unique identifier starting at segment 1. multi_tag (varchar100) - WKT Tag if Multi geometry eg MULTILINESTRING/MULTICURVE/MULTIPOLYGON. element_id (int) - Top level element identifier eg 1 for first polygon in multiPolygon. element_tag (varchar100) - WKT Tag for first element eg POLYGON if part of MULTIPOlYGON. subelement_id (int) - SubElement identifier of subelement of element with parts eg OuterRing of Polygon subelement_tag (varchar100) - WKT Tag for first subelement of element with parts eg OuterRing of Polygon segment_id (int) - Unique identifier starting at segment 1 for each element. sx (float) - Start Point X Ordinate sy (float) - Start Point Y Ordinate sz (float) - Start Point Z Ordinate sm (float) - Start Point M Ordinate mx (float) - Mid Point X Ordinate (Only if CircularString) my (float) - Mid Point Y Ordinate (Only if CircularString) mz (float) - Mid Point Z Ordinate (Only if CircularString) mm (float) - Mid Point M Ordinate (Only if CircularString) ex (float) - End Point X Ordinate ey (float) - End Point Y Ordinate ez (float) - End Point Z Ordinate em (float) - End Point M Ordinate length (float) - Length of this segment in SRID units startLength (float) - Cumulative Length (from start of geometry) at the start of this segment in SRID units measureRange (float) - Measure Range ie EndM - StartM geom (geometry) - Geometry representation of segment.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STFindArcPointByMeasure - Computes point on circular arc @p_measure from start with offset

SYNOPSIS

Function STFindArcPointByMeasure ( @p_circular_arc geometry @p_measure float, @p_offset Float = 0.0, @p_round_xy int = 3, @p_round_zm int = 2 ) Return Geometry (Point)

DESCRIPTION

Supplied with a circular linestring, a measured distance from the start, and an offset, this function computes the point on the circular arc. If the @p_offset value is <> 0, the function computes a new position for the point at a distance of @p_offset on the left (-ve) or right (+ve) side of the circular arc. The returned vertex has its ordinate values rounded using the relevant decimal place values.

INPUTS

@p_circular_arc (geometry) - A circular linestring @p_measure (float) - Measured distance from start vertex to required point. @p_offset (float) - The perpendicular distance to offset the generated point. A negative value instructs the function to offet the point to the left (start-end), and a positive value to the right. @p_round_xy (int) - Number of decimal digits of precision for an X or Y ordinate. @p_round_zm (int) - Number of decimal digits of precision for an Z or M ordinate.

RESULT

point (geometry) - The computed point.

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2017 - Original coding.

COPYRIGHT

(c) 2008-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.

NOTES

Is identical to STInterpolatePoint. Could use SYNONYM but chose not to. 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.

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

NOTES

Is identical to STInterpolatePoint. Could use SYNONYM but chose not to. 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.

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 -- Returns smallest (perpendicular) offset from supplied point to the linestring.

SYNOPSIS

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

NOTES

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

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

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

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

[ Top ] [ Functions ]

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

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

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

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STFindPointByRatio -- Returns (possibly offset) point geometry at supplied length ratio along linestring.

SYNOPSIS

Function [$(lrsowner)].[STFindPointByRatio] ( @p_linestring geometry, @p_ratio Float, @p_offset Float = 0.0, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

DESCRIPTION

Given a ratio (0 to 1.0), this function returns a geometry point at the position described by that ratio. Ratio is combined with length, so @p_ratio of 1.0 is equivalent to @p_linestring.STLength() ie @p_linestring.STEndPoint(). For example, @p_ratio value of 0.5 returns point at exact midpoint of linestring (ct centroid). If a non-zero/null value is suppied for @p_offset, the found point is offset (perpendicular to line) to the left (if @p_offset < 0) or to the right (if @p_offset > 0).

NOTES

Supports LineStrings with CircularString elements. Wrapper over STFindPointByLength

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_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 length ratio from start, with optional offset to left or right.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original Coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

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

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

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

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

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

SYNOPSIS

Function STInterpolatePoint ( @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 measure value of the snapped point.

NOTES

IS a wrapper over STProjectPoint (eg for PostGIS users) that returns only the Measure. Supports linestrings with CircularString elements. 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.

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

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.

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.

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

STIsPseudoMultiCurve -- Checks if supplied @p_geometry is a GeometryCollection object containing only LineString, CircularString and CompoundCurve elements.

SYNOPSIS

Function [$(lrsowner)].[STIsPseudoMultiCurve] ( @p_geometry geometry ) Returns bit

DESCRIPTION

This function checks if the supplied @p_geometry is a GeometryCollection object containing only LineString, CircularString and CompoundCurve elements. One cannot create a MultiLineString object where an element is a CircularString or CompoundCurve. Any attempt to do so causes the GeometryType to be set to GeometryCollection rather than MultiLineString or MultiCurve. If these sorts of GeometryCollection objects are not supported, it is impossible to represent a MultiLine object with a mix of CircularCurve and LineString types. Function returns 1 (true) if provided geometry is a pseudo MultiCurve (or MultiLineString)

INPUTS

@p_geometry (geometry) - GeometryCollection or MultiLineString.

RESULT

1/0 (bit) - 1 (true) if pseudo MultiCurve, 0 (false) otherwise.

EXAMPLE

-- This is how SQL Server merges a LineString and a CircularString that do not touch -- SELECT geometry::STGeomFromText('LINESTRING(0 0 0 5.6, 5 5 0 6.3,5 10 0 9.6)',0) .STUnion( geometry::STGeomFromText('CIRCULARSTRING(-0.5 -0.5 0 5.6,-0.2 -0.4 0 5.5, -0.1 -0.1 0 5.65)',0) ).AsTextZM() as MultiCurve; GO MultiCurve -------------------------------------------------------------------------------------------------- GEOMETRYCOLLECTION (LINESTRING (5 10, 5 5, 0 0), CIRCULARSTRING (-0.5 -0.5, -0.2 -0.4, -0.1 -0.1)) -- Note that STUnion removes Z and M ordinates -- Now put result through STIsPseudoMultiCurve -- SELECT [$(lrsowner)].STIsPseudoMultiCurve ( geometry::STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (5 10, 5 5, 0 0), CIRCULARSTRING (-0.5 -0.5, -0.2 -0.4, -0.1 -0.1))',0)) as isMultiCurve GO isMultiCurve ------------ 1

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2018 - Original coding for SQL Server

COPYRIGHT

(c) 2008-2018 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. 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).

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 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 offset to left or right.

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. Is wrapper over STFindSegmentByMeasureRange. Name is similar to PostGIS function.

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

STMeasureRange -- Returns Last Point M Value - First Point M Value.

SYNOPSIS

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

DESCRIPTION

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: length if not measured.

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 Percentage value to Measure.

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) - Measure expressed as percentage distance long linestring from 0..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.

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

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

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.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2008-2017 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.

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_geometry 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_geometry (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.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

Function STScaleMeasure ( @p_geometry 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))

SYNOPSIS

Function STScaleMeasure Return geometry

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_geometry (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) - Unit of measure for distance calculations. @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 STSplit ( @p_linestring geometry, @p_point geometry, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns @lines TABLE ( line1 geometry, line2 geometry )

USAGE

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

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

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2018 - Original Coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

Function [$(lrsowner)].[STSplitCircularStringByLength] ( @p_circular_arc geometry, @p_start_length Float, @p_end_length Float = null, @p_offset Float = 0.0, @p_round_xy int = 3, @p_round_zm int = 2 ) Returns geometry

DESCRIPTION

Given a start and end length, this function extracts a new CircularString segment from the @p_circular_arc. 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 the circularString @p_offset value causes the CircularString to disappear, NULL is returned.

NOTES

Supports a single (3-point) CircularString element only.

INPUTS

@p_circular_arc (geometry) - A single, 3 point, CircularString. @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_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.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STSplitCircularStringByMeasure -- 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)].[STSplitCircularStringByMeasure] ( @p_circular_arc geometry, @p_start_measure Float, @p_end_measure Float = null, @p_offset Float = 0.0, @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_circular_arc. 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 the circularString offset causes the CircularString to disappear, NULL is returned.

NOTES

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

INPUTS

@p_circular_arc (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_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.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

STSplitLineSegmentByLength -- 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)].[STSplitCircularStringByLength] ( @p_linestring geometry, @p_start_length Float, @p_end_length Float = null, @p_offset Float = 0.0, @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).

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

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

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

DESCRIPTION

Given start and end measures, 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).

NOTES

Supports a single (2-point) LineString 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_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.

TODO

IsMeasureDescending....

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - 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

USAGE

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

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.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2018 - Original Coding.

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. If the line-string is not measured it returns 0. 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.

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

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

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

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.

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