[ 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
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 except where start/end measure input values are the same.
If same, or @p_start_measure is NULL, @p_start_measure is set to 0.0.
If @p_end_measure is equal to @p_start_measure, @p_end_measure is set to start measure + geometry length.
IF start measure is less than end measure the line is returned unmodified (see TOBEDONE)
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.
PARAMETERS
@p_linestring (geometry) - Supplied Linestring geometry.
@p_start_measure (float) - New Start Measure value: if null it is set to 0.
@p_end_measure (float) - New End Measure value: if null it is set to ST_Length(@p_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
measured linestring (geometry) - Input linestring with measures applied.
TOBEDONE
Handle reverse measure input values eg start measure of 10 and
end measure of 0 (or null - line length - and end_measure = 0)
EXAMPLE
SELECT [lrs].[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)
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.
Value is negative if on left of line; positive if on right.
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)
union all
select ROUND([lrs].[STFindOffset] (
geometry::STGeomFromText('CIRCULARSTRING(0 10,-3.746 9.272,-7.071 7.071)',28355),
geometry::Point(-1,1,28355),
3,2),3)
union all
select ROUND([lrs].[STFindOffset] (
geometry::STGeomFromText('CIRCULARSTRING(-7.071 7.071,-3.746 9.272,0 10)',28355),
geometry::Point(-1,1,28355),
3,2),3)
go
offset_distance
2
0
8.586
-8.586
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('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0) as linestring
)
select 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 offset, length
GO
length offset fPoint
0 -1 POINT (2.571 5.421)
5 -1 POINT (-1.608 5.78)
10 -1 POINT (-0.468 3.321)
15 -1 POINT (-0.133 2.055)
20 -1 POINT (2.01 6.572)
0 0 POINT (3 6.3246)
5 0 POINT (-1.876 6.744)
10 0 POINT (-1.372 2.892)
15 0 POINT (0.771 1.626)
20 0 POINT (2.914 6.143)
0 1 POINT (3.429 7.228)
5 1 POINT (-2.144 7.707)
10 1 POINT (-2.276 2.463)
15 1 POINT (1.675 1.197)
20 1 POINT (3.818 5.714)
AUTHOR
Simon Greener
HISTORY
Simon Greener - December 2017 - Original Coding.
Simon Greener - November 2021 - Fixed cumulative_length and radius_check bugs.
[ 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_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.
EXAMPLE
with data as (
select lrs.STAddMeasure(geometry::STGeomFromText('CIRCULARSTRING (0 0,10 5, 20 0, 30 -5, 40 0)',2274),null,null,3,3) as geom
)
select g.IntValue,
[$(lrsowner)].[STFindPointByMeasure] (a.geom,g.IntValue,13.0,2,3,3).AsTextZM() as fl
from data as a
cross apply
dbo.Generate_Series(a.geom.STStartPoint().M,a.geom.STEndPoint().M,5) as g;
go
measure geom
0 POINT (10 -7.5)
5 POINT (10 -7.5)
10 POINT (10 -7.5)
15 POINT (10 -7.5)
20 POINT (10 -7.5)
25 POINT (12.033 -10.595 NULL 25)
30 POINT (20.497 -16.163 NULL 30)
35 POINT (30.462 -17.996 NULL 35)
40 POINT (40.354 -15.803 NULL 40)
45 POINT (48.611 -9.932 NULL 45)
AUTHOR
Simon Greener
HISTORY
Simon Greener - December 2017 - Original Coding.
Simon Greener - November 2021 - Fixed cumulative_length and radius_check bugs.
[ 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_radius_check bit = 1,
@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).
If the offset is greater than the radius (inside) of a CircularString: 1 causes NULL to be returned; 2 returns centre point; 0 returns the offset point regardless.
The returned point has its ordinate values rounded using the supplied @p_round_xy/@p_round_zm decimal place values.
NOTES
Supports LineStrings with CircularString elements.
Wrapper over 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_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 length ratio from start, with optional offset to left or right.
EXAMPLE
-- LineString test.
select f.ratio,
o.IntValue as offset,
[lrs].[STFindPointByRatio] (
geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',0),
f.ratio,
o.IntValue,
2,
3,
2
).AsTextZM() as fPoint
from (select 0.01 * CAST(t.IntValue as numeric) as ratio
from [dbo].[Generate_Series](0,100,10) as t
) as f
cross apply
[dbo].[generate_series](-1,1,1) as o
order by offset, ratio
GO
ratio offset fPoint
0.00 -1 POINT (-4.70710678118655 -3.29289321881345 0 1)
0.10 -1 POINT (-2.893 -1.479 0 3.09)
0.20 -1 POINT (-1.079 0.335 0 5.17)
0.30 -1 POINT (2.04 1 0 7.64)
0.40 -1 POINT (4.606 1 0 10.21)
0.50 -1 POINT (7.172 1 0 12.78)
0.60 -1 POINT (9.737 1 0 15.35)
0.70 -1 POINT (9 2.303 0 17.86)
0.80 -1 POINT (9 4.869 0 20.38)
0.90 -1 POINT (9 7.434 0 22.89)
1.00 -1 POINT (9 10 0 25.4)
0.00 0 POINT (-4 -4 0 1)
0.10 0 POINT (-2.186 -2.186 0 3.09)
0.20 0 POINT (-0.372 -0.372 0 5.17)
0.30 0 POINT (2.04 0 0 7.64)
0.40 0 POINT (4.606 0 0 10.21)
0.50 0 POINT (7.172 0 0 12.78)
0.60 0 POINT (9.737 0 0 15.35)
0.70 0 POINT (10 2.303 0 17.86)
0.80 0 POINT (10 4.869 0 20.38)
0.90 0 POINT (10 7.434 0 22.89)
1.00 0 POINT (10 10 0 25.4)
0.00 1 POINT (-3.29289321881345 -4.70710678118655 0 1)
0.10 1 POINT (-1.479 -2.893 0 3.09)
0.20 1 POINT (0.335 -1.079 0 5.17)
0.30 1 POINT (2.04 -1 0 7.64)
0.40 1 POINT (4.606 -1 0 10.21)
0.50 1 POINT (7.172 -1 0 12.78)
0.60 1 POINT (9.737 -1 0 15.35)
0.70 1 POINT (11 2.303 0 17.86)
0.80 1 POINT (11 4.869 0 20.38)
0.90 1 POINT (11 7.434 0 22.89)
1.00 1 POINT (11 10 0 25.4)
-- Circular Curve test.
EXAMPLE
select o.IntValue as offset,
f.ratio,
[lrs].[STFindPointByRatio] (
geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING (3 6.3246, 0 7, -3 6.3246),(-3 6.3246, 0 0, 3 6.3246))',0),
f.ratio,
o.IntValue,
2,
3,
2
).AsTextZM() as fPoint
from (select 0.01 * CAST(t.IntValue as numeric) as ratio
from [dbo].[Generate_Series](0,100,10) as t
) as f
cross apply
[dbo].[generate_series](-1,1,1) as o
order by o.IntValue, f.ratio
GO
offset ratio fPoint
-1 0.00 POINT (2.571 5.421)
-1 0.10 POINT (0.922 5.929)
-1 0.20 POINT (-0.803 5.946)
-1 0.30 POINT (-2.462 5.472)
-1 0.40 POINT (-1.29 5.055)
-1 0.50 POINT (-0.425 3.23)
-1 0.60 POINT (0.441 1.405)
-1 0.70 POINT (-0.501 1.278)
-1 0.80 POINT (0.364 3.103)
-1 0.90 POINT (1.23 4.928)
-1 1.00 POINT (2.09649092475221 6.75316895704762)
0 0.00 POINT (3 6.3246)
0 0.10 POINT (1.076 6.917)
0 0.20 POINT (-0.937 6.937)
0 0.30 POINT (-2.872 6.384)
0 0.40 POINT (-2.194 4.626)
0 0.50 POINT (-1.329 2.801)
0 0.60 POINT (-0.463 0.976)
0 0.70 POINT (0.403 0.849)
0 0.80 POINT (1.268 2.674)
0 0.90 POINT (2.134 4.499)
0 1.00 POINT (3 6.3246)
1 0.00 POINT (3.429 7.228)
1 0.10 POINT (1.23 7.905)
1 0.20 POINT (-1.071 7.928)
1 0.30 POINT (-3.283 7.295)
1 0.40 POINT (-3.098 4.197)
1 0.50 POINT (-2.233 2.372)
1 0.60 POINT (-1.367 0.547)
1 0.70 POINT (1.307 0.42)
1 0.80 POINT (2.172 2.245)
1 0.90 POINT (3.038 4.07)
1 1.00 POINT (3.90350907524779 5.89603104295238)
AUTHOR
Simon Greener
HISTORY
Simon Greener - December 2017 - Original Coding.
COPYRIGHT
(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ Functions ]
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 ] [ Functions ]
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 ] [ Methods ]
NAME
SYNOPSIS
Returns @data
DESCRIPTION
NOTES
INPUTS
RESULT
EXAMPLE
AUTHOR
HISTORY
COPYRIGHT
[ 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 ] [ Functions ]
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 ] [ Functions ]
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 [lrs].[STProjectPoint] (
@p_linestring geometry,
@p_point geometry,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
DESCRIPTION
This function snaps supplied point to @p_linestring, returning the snapped point.
If there is more than one possible solution the first point is returned.
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(20)) as test,
[lrs].[STProjectPoint] (
geometry::STGeomFromText('LINESTRING(-4 -4 0 1, 0 0 0 5.6, 10 0 0 15.61, 10 10 0 25.4)',28355),
geometry::Point(8,8,28355),
3,2).AsTextZM() as project_point
union all
select '2D return length in measure' as test,
[lrs].[STProjectPoint] (
geometry::STGeomFromText('LINESTRING(-4 -4, 0 0, 10 0, 10 10)',28355),
geometry::Point(8,8,28355),
3,2).AsTextZM() as project_point
GO
test project_point
Actual Measure POINT (10 8 NULL 23.44)
2D return length in measure POINT (10 8 NULL 23.66)
AUTHOR
Simon Greener
HISTORY
Simon Greener - January 2013 - Original coding.
Simon Greener - December 2017 - Port to SQL Server (TSQL).
Simon Greener - December 2022 - Changed method for finding nearest segment.
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.
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 OR is a result cannot be calculated two LINESTRING EMPTY geometries.
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 ] [ Functions ]
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 ] [ 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-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