# 1. AFFINE/STMove (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMove -- Function which moves a geometry using the supplied delta X, Y, Z and M ordinate values.
```

SYNOPSIS

```    Function STMove (
@p_geometry geometry,
@p_dX       float,
@p_dY       float,
@p_dZ       float,
@p_dM       float,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STMove](geometry::ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),-5.0,30.1,default,default,2,1).AsTextZM() as movedGeom;
# movedGeom
'LINESTRING(-4.9 30.2,-3.6 31.5)'
```

DESCRIPTION

```    Function that moves the supplied geometry's ordinates the supplied x, y, z and m deltas.
The computed ordinates of the new geometry are rounded to @p_round_xy/@p_round_zm number of decimal digits of precision.
```

INPUTS

```    @p_geometry (geometry) - Supplied geometry of any type.
@p_dX          (float) - X ordinate delta shift.
@p_dy          (float) - Y ordinate delta shift.
@p_dZ          (float) - Z ordinate delta shift.
@p_dM          (float) - M ordinate delta shift.
@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

```    moved geom  (geometry) - Input geometry moved by supplied X and Y ordinate deltas.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 2. AFFINE/STRotate (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STRotate -- Function which rotates a geometry.
```

SYNOPSIS

```    Function STRotate (
@p_geometry geometry,
@p_rX       float,
@p_rY       float,
@p_angle    float,
@p_round_xy int,
@p_round_zm int
)
Returns geometry
```

USAGE

```    With data as (
select 'Original' as name, geometry::STGeomFromText('POLYGON((1 1, 1 6, 11 6, 11 1, 1 1))',0) as geom
)
SELECT name, geom.STAsText() as rGeom
FROM (select name, geom
from data as d
union all
select '45' + CHAR(176) + ' rotate about 0,0' as name, [\$(owner)].[STRotate](d.geom,0.0,0.0,45,3,3) as geomO
from data as d
union all
select '45' + CHAR(176) + ' rotate about MBR centre' as name, [\$(owner)].[STRotate](d.geom,(a.minx + a.maxx) / 2.0,(a.miny + a.maxy) / 2.0,45,3,3) as geom
from data as d
cross apply
[\$(owner)].[STGEOMETRY2MBR](d.geom) as a
) as f
GO
name    rGeom
Original    POLYGON ((1 1, 1 6, 11 6, 11 1, 1 1))
45° rotate about 0,0    POLYGON ((0 1.414, -3.536 4.95, 3.536 12.021, 7.071 8.485, 0 1.414))
45° rotate about MBR centre    POLYGON ((4.232 -1.803, 0.697 1.732, 7.768 8.803, 11.303 5.268, 4.232 -1.803))
```

DESCRIPTION

```    Function which rotates the supplied geometry around a supplied rotation point (X,Y) a required angle in degrees between 0 and 360.
The computed ordinates of the new geometry are rounded to the appropriate decimal digits of precision.
```

INPUTS

```    @p_geometry (geometry) - supplied geometry of any type.
@p_rX       (float)    - X ordinate of rotation point.
@p_rY       (float)    - Y ordinate of rotation point.
@p_angle    (float)    - Rotation angle expressed in decimal degrees between 0 and 360.
@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 XM ordinates are rounded.
```

RESULT

```    geometry -- Input geometry rotated by supplied values.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

# 3. AFFINE/STScale (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STScale -- TSQL Function that moves a geometry object's ordinates using the supplied delta X, Y, Z and M.
```

SYNOPSIS

```    Function STScale (@p_geometry geometry,
@p_xFactor  float,
@p_yFactor  float,
@p_zFactor  float,
@p_mFactor  float,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
```

USAGE

```    -- XY
SELECT [dbo].[STScale](
geometry::STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),
-5.0,
3.2,
NULL,
NULL,
3,
2
).AsTextZM() as scaledGeom;

scaledGeom
LINESTRING (-0.5 0.64, -7 144.64)

-- XYM
SELECT [dbo].[STScale](
geometry::STGeomFromText('LINESTRING(0.1 0.2 NULL 1.0,1.4 45.2 NULL 45.02)',0),
NULL,
NULL,
NULL,
1.5,
3,
2
).AsTextZM() as scaledGeom;

scaledGeom
LINESTRING (0.1 0.2 NULL 1.5, 1.4 45.2 NULL 67.53)

-- XYZM
SELECT [dbo].[STScale](
geometry::STGeomFromText('LINESTRING(0.1 0.2 0.9 1.0,1.4 45.2 2.1 45.02)',0),
1.0,
1.0,
2.0,
1.5,
3,
2
).AsTextZM() as scaledGeom;

scaledGeom
LINESTRING (0.1 0.2 1.8 1.5, 1.4 45.2 4.2 67.53)
```

DESCRIPTION

```    Function that scales the supplied geometry's ordinates using the supplied scale factors.
The computed ordinates of the new geometry are rounded to the appropriate decimal digits of precision.
```

INPUTS

```    @p_geometry (geometry) - supplied geometry of any type.
@p_xFactor  (double)   - X ordinate scale factor.
@p_yFactor  (double)   - Y ordinate scale factor.
@p_zFactor  (double)   - Z ordinate scale factor.
@p_mFactor  (double)   - M ordinate scale factor.
@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 XM ordinates are rounded.
```

RESULT

```    geometry -- Input geometry scaled by supplied ordinate factor values.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 4. COGO/DD2DMS (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    DD2DMS -- Function converts a decimal degree floating point number to its string equivalent.
```

SYNOPSIS

```    Function [\$(cogoowner)].[DD2DMS] (
@dDecDeg       Float,
@pDegreeSymbol NVarChar(1),
@pMinuteSymbol NVarChar(1),
@pSecondSymbol NVarChar(1)
)
Returns nvarchar(50)
```

USAGE

```     SELECT [\$(cogoowner)].[DD2DMS](45.5083333333333,'^','''','"') as DMS;
DMS
45^30'30.00"

SELECT [\$(cogoowner)].[DD2DMS](45.5083333333333,CHAR(176),CHAR(39),'"') as DMS;
DMS
45°30'30.00"
```

DESCRIPTION

```    Function that converts the supplied decimal degrees value to a string using the supplied symbols.
```

NOTES

```    Normalization of the returned value to ensure values are between 0 and 360 degrees can be conducted via the STNormalizeBearing function.
Useful for working with Google Earth
```

INPUTS

```    @dDecDeg       (Float)       - Decimal degrees value.
@pDegreeSymbol (NVarChar(1)) - Degrees symbol eg ^
@pMinuteSymbol (NVarChar(1)) - Seconds symbol eg '
@pSecondSymbol (NVarChar(1)) - Seconds symbol eg "
```

RESULT

```    DMS (string) : Decimal degrees string equivalent.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 5. COGO/DMS2DD (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    DMS2DD -- Function computes a decimal degree floating point number from individual degrees, minutes and seconds values.
```

SYNOPSIS

```    Function DMS2DD(@p_dDeg  Int,
@p_dMin  Int,
@p_dSec  Float )
Returns Float
```

USAGE

```    SELECT [\$(cogoowner)].[DMS2DD](45,30,30) as DD;
DD
45.5083333333333
```

DESCRIPTION

```    Function that computes the decimal equivalent to the supplied degrees, minutes and seconds values.
No checking of the values of each of the inputs is conducted: one can supply 456 minutes if one wants to!
```

NOTES

```    Normalization of the returned value to ensure values are between 0 and 360 degrees can be conducted via the STNormalizeBearing function.
```

INPUTS

```    @p_dDeg (int)   : Non-NULL degree value (0-360)
@p_dMin (int)   : Non-NULL minutes value (0-60)
@p_dSec (float) : Non-NULL seconds value (0-60)
```

RESULT

```    DecimalDegrees (float) : Decimal degrees equivalent value.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 6. COGO/DMSS2DD (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    DMSS2DD -- Function computes a decimal degree floating point number from individual degrees, minutes and seconds values encoded in supplied string.
```

SYNOPSIS

```    Function DMSS2DD(@p_strDegMinSec nvarchar(100))
Returns Float
```

USAGE

```    SELECT [\$(cogoowner)].[DMSS2DD]('43° 0'' 50.00"S') as DD;
DD
-43.0138888888889
```

DESCRIPTION

```    The function parses the provided string (eg extracted from Google Earth) that contains DD MM SS.SS values, extracts and creates a single floating point decimal degrees value.
No checking of the values of each of the inputs is conducted: one can supply 456 minutes if one wants to!
The function honours N, S, E and W cardinal references.
```

NOTES

```    Normalization of the returned value to ensure values are between 0 and 360 degrees can be conducted via the STNormalizeBearing function.
```

INPUTS

```    @p_strDegMinSec (nvarchar(100)) : DD MM SS.SS description eg 43° 0'' 50.00"S
```

RESULT

```    DecimalDegrees (float) : Decimal degrees equivalent value.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 7. COGO/STAddSegmentByCOGO (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STAddSegmentByCOGO - Returns a projected point given starting point, a bearing in Degrees, and a distance (geometry SRID units).
```

SYNOPSIS

```    Function STAddSegmentByCOGO (
@p_linestring geometry,
@p_dBearing   float,
@p_dDistance  float
@p_round_xy   int = 3,
@p_round_zm   int = 2
)
Returns float
```

USAGE

```    SELECT [\$(cogoowner)].[STAddSegmentByCOGO] (geometry::STGeomFromText('LINESTRING(0 0,10 0)',0),90,10,3,2).STAsText() as newSegment;
newSegment
LINESTRING (0 0,10 0,20 0)
```

DESCRIPTION

```    Function that adds a new segment (two vertices) to an existing linestring's beginning or end.
New point is created from a start or end coordinate, using a whole circle bearing (p_dBearing) and a distance (p_dDistance) in SRID Units.
Returned point's XY ordinates are rounded to @p_round_xy decimal digits of precision.
```

INPUTS

```    @p_linestring (geometry) - Linestring.
@p_dBearing      (float) - Whole circle bearing between 0 and 360 degrees.
@p_dDistance     (float) - Distance in SRID units from starting point to required point.
@p_round_xy        (int) - XY ordinates decimal digitis of precision.
@p_round_zm        (int) - ZM ordinates decimal digitis of precision.
```

RESULT

```    Modified line (geometry) - modified Linestring.
```

TODO

```    Z,M extrapolation.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 8. COGO/STArcToChordSeparation (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STArcToChordSeparation -- Returns the distance between the midpoint of the Arc and the Chord for an angle given the radius
```

SYNOPSIS

```    Function STArcToChordSeparation (
@p_dAngle  Float
)
Returns float
SELECT [\$(cogoowner)].[STArcToChordSeparation](100, 10);
```

DESCRIPTION

```    Chords are needed when "stroking" a circularstring to a vertex-connected linestring.
To do this, one needs to compute such parameters as arc length, chord length and arc to chord separation.
The arc to chord separation is important in that large values create linestring segments that clearly diverge from the cicular arc.
Different values therefore given different ascetic results.
This function computes the arc to chord separation (meters or in srid distance units) given a radius and an
angle (degrees 0..360) subtended at the centre of the circle defining the CircularString
```

NOTES

```    Assumes planar projection eg UTM.
```

INPUTS

```    @p_dRadius          (float) : Radius of Circle.
@p_dAngle           (float) : The Angle subtended at the centre of the circle in degrees between 0 and 360.
```

RESULT

```    separation distance (float) - ArcToChord separation distance.
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - Feb 2015 - Converted to TSQL for SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 9. COGO/STAvergeBearing (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    [STAvergeBearing] -- Function that computes average bearing of segments in linestring.
```

SYNOPSIS

```    Function [STAvergeBearing] (
@p_linestring geometry
)
Returns geometry
```

DESCRIPTION

```    Function that computes the bearing of each and every segment of a linestring, and then averages the result across all segments.
If @p_linestring contains CircularStrings the bearings will be from the startPoint to the midPoint, and the midPoint to the endPoint.
```

INPUTS

```    @p_linestring (geometry) - Supplied Linestring geometry.
```

RESULT

```    averge bearing   (float) - Aveage of bearing of all segments in linestring.
```

EXAMPLE

```    -- All testing includes reverse.
-- Testing 4 Point Linestring All Points Collinear
select [\$(owner)].[STAvergeBearing] (geometry::STGeomFromText('LINESTRING(0 0,1 0,2 0,3 0)',0)  ) as avgBearing
union all
select [\$(owner)].[STAvergeBearing] ([\$(owner)].[STReverse](geometry::STGeomFromText('LINESTRING(0 0,1 0,2 0,3 0)',0),DEFAULT,DEFAULT)) as avgBearing
go

avgBearing
90
270

--Non Collinear test ...

select [\$(owner)].[STAvergeBearing] ( geometry::STGeomFromText('LINESTRING(0 0, 1 0, 1 1, 10 0, 10 -10, 5 -5)',0) ) as avgBearing
union all
select [\$(owner)].[STAvergeBearing] ( [\$(owner)].[STReverse](geometry::STGeomFromText('LINESTRING(0 0, 1 0, 1 1, 10 0, 10 -10, 5 -5)',0),DEFAULT,DEFAULT)) as avgBearing
GO

avgBearing
136.268038349182
172.268038349182

-- CircularString Test

select [\$(owner)].[STAverageBearing] (geometry::STGeomFromText('CIRCULARSTRING(0 0,1 1,2 0)',0)  ) as avgBearing;

avgBearing
90

select [\$(owner)].[STAverageBearing] (geometry::STGeomFromText('COMPOUNDCURVE((-2 -2,-1 -1,0 0),CIRCULARSTRING(0 0,1 1,2 0))',0) ) as avgBearing;

avgBearing
67.5
```

NOTES

```    Uses [\$(owner)].[STFindDeflectionAngle]
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - October 2019 - Original TSQL Coding
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 10. COGO/STAzimuth (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STAzimuth -- Returns a (Normalized) azimuth in Degrees between two non-geodetic (XY) coordinates
```

SYNOPSIS

```    Function STBearing (
@p_dE1 float,
@p_dN1 float,
@p_dE2 float,
@p_dN2 float
)
Returns float
```

DESCRIPTION

```    Function that computes the azimuth from the supplied start point (@p_dx1) to the supplied end point (@p_dx2).
The result is expressed as a whole circle bearing in decimal degrees.
This function is an alternate implemetation of STBearing whose results should always be the same.
```

INPUTS

```    @p_dE1 (float) - X ordinate of start point.
@p_dN1 (float) - Y ordinate of start point.
@p_dE2 (float) - Z ordinate of start point.
@p_dN2 (float) - M ordinate of start point.
```

RESULT

```    decimal degrees (float) - Azimuth between point 1 and 2 from 0-360.
```

EXAMPLE

```    SELECT [\$(cogoowner)].[STAzimuth](0,0,45,45) as Bearing;
Bearing
45
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 11. COGO/STBearing (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STBearing -- Returns a (Normalized) bearing in Degrees between two non-geodetic (XY) coordinates
```

SYNOPSIS

```    Function STBearing (
@p_dE1 float,
@p_dN1 float,
@p_dE2 float,
@p_dN2 float
)
Returns float
```

USAGE

```    SELECT [\$(cogoowner)].[STBearing](0,0,45,45) as Bearing;
Bearing
45
```

DESCRIPTION

```    Function that computes the bearing from the supplied start point (@p_dx1) to the supplied end point (@p_dx2).
The result is expressed as a whole circle bearing in decimal degrees.
```

INPUTS

```    @p_dE1 (float) - X ordinate of start point.
@p_dN1 (float) - Y ordinate of start point.
@p_dE2 (float) - Z ordinate of start point.
@p_dN2 (float) - M ordinate of start point.
```

RESULT

```    decimal degrees (float) - Bearing between point 1 and 2 from 0-360.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 12. COGO/STBearingAlongLine (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STBearingAlongLine -- Returns a (Normalized) bearing in Degrees along a non-geodetic linestring geometry's first and last point
```

SYNOPSIS

```    Function STBearingAlongLine (
@p_linestring geometry
)
Returns float
```

DESCRIPTION

```    Function that computes the bearing from the supplied linestring's start and end points.
The result is expressed as a whole circle bearing in decimal degrees.
```

INPUTS

```    @p_linestring (geometry) - LineString.
```

RESULT

```    decimal degrees (float) - Bearing between point 1 and 2 from 0-360.
```

EXAMPLE

```    SELECT [\$(cogoowner)].[STBearingAlongLine] (
geometry::STGeomFromText('LINESTRING(0 0,45 45)',0)
) as Bearing;
Bearing
45
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2019 - Original TSQL Coding for SQL Server.
```

```    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 13. COGO/STBearingBetweenPoints (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STBearingBetweenPoints -- Returns a (Normalized) bearing in Degrees between two non-geodetic (XY) geometry points
```

SYNOPSIS

```    Function STBearingBetweenPoints (
@p_start_point geometry,
@p_end_point   geometry
)
Returns float
```

USAGE

```    SELECT [\$(cogoowner)].[STBearingBetweenPoints] (
geometry::Point(0,0,0),
geometry::Point(45,45,0)
) as Bearing;
Bearing
45
```

DESCRIPTION

```    Function that computes the bearing from the supplied start point to the supplied end point.
The result is expressed as a whole circle bearing in decimal degrees.
```

INPUTS

```    @p_start_point (geometry) - Start point.
@p_end_point   (geometry) - End point.
```

RESULT

```    decimal degrees (float) - Bearing between point 1 and 2 from 0-360.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2008 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 14. COGO/STCircle2Polygon (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCircle2Polygon -- Returns stroked Polygon shape from circle definition of centre XY and radius.
```

SYNOPSIS

```    Function STCircle2Polygon (
@p_dCentreX  Float,
@p_dCentreY  Float,
@p_iSegments int
@p_srid      int,
@p_round_xy  Int = 3
)
Returns geometry
SELECT [\$(cogoowner)].[STCircle2polygon](100,100,5.0,144,0,3);
```

DESCRIPTION

```    Given a 3 points defining a circular arc this function computes the centre and radius of the circle of
which it is a part of its circumference.
```

NOTES

```    Assumes planar projection eg UTM.
Created polyon geometry has required ring rotation.
```

INPUTS

```    @p_dCentreX  (float) : X Ordinate of centre of Circle
@p_dCentreY  (float) : Y Ordinate of centre of Circle
@p_iSegments   (int) : Number of arc (chord) segments in circle (+ve clockwise, -ve anti-clockwise)
@p_Srid        (int) : Spatial Reference Id of geometry
@p_Round_xy    (int) : Precision of any XY ordinate value ie number of significant digits. If null then 3 is assumed (ie 1 mm): 3456.2345245 -> 3456.235.
```

RESULT

```    polygon   (geometry) : Circle as stroked polygon.
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - May 2011 - Converted to TSQL for SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 15. COGO/STCogo2Line (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STCogo2Line - Creates linestring from move, bearing and distance instructions supplied in the XML parameter.
```

SYNOPSIS

```   Function [\$(cogoowner)].[STCogo2Line] (
@p_cogo     xml,
@p_round_xy int = 3,
@p_round_z  int = 2
)
Returns Geometry
```

EXAMPLE

```    Print 'Generate XYZ linestring using ordinate string moveTo.';
Declare @v_cogo xml;
SET @v_cogo =
'<Cogo srid="28356">
<Segments>
<Segment id="1"><MoveTo>10 10 -1</MoveTo><DegMinSec> 345°21''48.75"</DegMinSec><Distance>7.26992</Distance><DeltaZ>1</DeltaZ></Segment>
<Segment id="2"><DegMinSec>  83° 2''4.652"</DegMinSec><Distance>151.712</Distance><DeltaZ>2</DeltaZ></Segment>
<Segment id="3"><DegMinSec> 186°34''30.73"</DegMinSec><Distance>10.3924</Distance><DeltaZ>3</DeltaZ></Segment>
</Segments>
</Cogo>';
select [cogo].[STCogo2Line] (@v_cogo, 3, 2).AsTextZM() as cogoLine
GO
LINESTRING(10 10 -1,8.163 17.034 0,158.755 35.432 2,157.565 25.108 5)
```

DESCRIPTION

```    This function takes a set of bearings and distances supplied in XML format, and creates a linestring from it.
The COGO bearings can be supplied as decimal degrees or as a text string sutable for use with DMSS2DD.
If @p_start_point is supplied then its XY ordinates, and SRID, are used for the starting point of the line, otherwise 0,0 and 0 SRID.
The final geometry will have its XY ordinates rounded to @p_round_xy of precision, similarly for Z.
COGO XML Format:
<Cogo srid={int}>
<Segments>
<Segment id="?">
<MoveTo></MoveTo>
<DegMinSec></DegMinSec>
<Bearing></Bearing>
<Distance></Distance>
<DeltaZ></DeltaZ>
<Segment id="?">
<Segment>
....
<Segment>
</Segments>
</Cogo>
<moveTo> allows for a point object to be provided for the start point, or can denote a break between linestrings.
<moveTo> should contain either a POINT() WKT object or the coordinate string part of a POINT() WKT object eg
POINT(1 2 -1) -- XYZ
1 2 -1
<moveTo> associated with first <Segment> determines if linestring 2D or 3D.
If <moveTo> missing for first <Segment>, linestring is 2D regardless as to whether any other <moveTo>s exist in any other <Segment>
If linestring is XYZ then <DeltaZ> elements are expected.
<DegMinSec> does not have to exist if <Bearing> (decimal degrees) exists.
<DeltaZ> is optional, if not, a 3D <MoveTo> is expected for the first <Segment>
```

INPUTS

```    @p_cogo     (xml) - MoveTos, Bearings, Distances and DeltaZ instructions
@p_round_xy (int) - Rounding factor for XY ordinates.
@p_round_z  (int) - Rounding factor for Z ordinate.
```

RESULT

```    linestring geometry - New linestring geometry object.
NOTE
Measures not supported: see LRS functions.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - June 2018 - Original coding.
```

```    (c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 16. COGO/STComputeArcLength (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STComputeArcLength -- Returns the length of the Circular Arc subtended by @p_dAngle (degrees between 0 and 360) at the centre of a circular of radius @p_dRadius.
```

SYNOPSIS

```    Function STComputeArcLength (
@p_dAngle  Float
)
Returns float
SELECT [\$(cogoowner)].[STComputeArcLength](100, 0.003);
```

DESCRIPTION

```    Returns the length of the chord subtended by the supplied angle (degrees between 0 and 360) at the centre of a circular with the given radius.
```

NOTES

```    Assumes planar projection eg UTM.
```

INPUTS

```    @p_dRadius (float) : Radius of Circle.
@p_dAngle  (float) : The Angle subtended at the centre of the circle in degrees between 0 and 360.
```

RESULT

```    ArcLength  (float) : The length of the circular arc.
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - Feb 2015 - Converted to TSQL for SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 17. COGO/STComputeChordLength (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STComputeChordLength -- Returns the length of the chord for an angle given the radius.
```

SYNOPSIS

```    Function STComputeChordLength (
@p_dAngle  Float
)
Returns float
SELECT [\$(cogoowner)].[STComputeChordLength](100, 0.003);
```

DESCRIPTION

```    Returns the length of the chord subtended by an angle (degrees between 0 and 360) at the centre of a circular of radius @p_dRadius.
```

NOTES

```    Assumes planar projection eg UTM.
```

INPUTS

```    @p_dRadius   (float) : Radius of Circle.
@p_dAngle    (float) : The Angle subtended at the centre of the circle in degrees between 0 and 360.
```

RESULT

```    ChordLength  (float) : The length of the chord in metres.
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - May 2011 - Converted to TSQL for SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 18. COGO/STComputeLengthToMidPoint (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STComputeLengthToMidPoint - Returns the length of the arc defined by the first and second (mid) points of a CircularString.
```

SYNOPSIS

```    Function STComputeLengthToMidPoint (
@p_circular_arc geometry
)
Returns float (arc length)
```

DESCRIPTION

```    Supplied with a circular arc with 3 points, this function computes the arc length from the first to the second points.
```

NOTES

```    Assumes planar projection eg UTM.
Only supports SQL Server Spatial 2012 onwards as 2008 does not support CIRCULARSTRINGs
```

TODO

```    Support measuring arc length from 1st to 3rd or 2nd to 3rd point
```

INPUTS

```    @p_circular_arc (geometry) - A Single CircularString with 3 points.
```

RESULT

```    length             (float) - The length of the arc in SRID units.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2017 - Original TSQL coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 19. COGO/STComputeTangentPoint (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STComputeTangentPoint -- Computes point that would define a tandential line at the start or end of a circular arc
```

SYNOPSIS

```    Function STComputeTangentPoint (
@p_circular_arc geometry,
@p_position     varchar(5) = 'START', -- or 'END'
@p_round_xy     int = 3
)
Returns geometry
SELECT [\$(cogoowner)].[STComputeTangentPoint](100, 0.003);
```

DESCRIPTION

```    There is a need to be able to compute an angle between a linestring and a circularstring.
To do this, one needs to compute a tangential line at the start or end of a circularstring.
This function computes point that would define a tandential line at the start or end of a circular arc.
```

NOTES

```    Assumes planar projection eg UTM.
Only supports SQL Server Spatial 2012 onwards as 2008 does not support CircularString
```

TODO

```    Enable creating of tangent at mid point of circularstring (@p_position=MID).
Enable creating of tangent at a distance along the circularstring.
```

INPUTS

```    @p_circular_arc (geometry) - CircularString.
@p_position     (varchar5) - Requests tangent point for 'START' or 'END' of circular arc.
@p_round_xy     (int)      - Decimal degrees of precision for XY ordinates.
```

RESULT

```    point           (geometry) - A tangent point that combined with the start or end of the circularstring creates a tangential line.
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - Feb 2015 - Converted to TSQL for SQL Server
```

```    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 20. COGO/STCreateCircle (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCreateCircle -- Creates Circular polygon from Centre XY, Radius, Srid and Ordinate Round
```

SYNOPSIS

```    Function STCreateCircle (
@dCentreX Float,
@dCentreY Float,
@iSrid    int,
@iRound   Int = 3
)
Returns geometry
```

DESCRIPTION

```    Given a 3 points defining a circular arc this function computes the centre and radius of the circle of
which it is a part of its circumference.
```

NOTES

```    Assumes planar projection eg UTM.
Only supports SQL Server Spatial 2012 onwards as 2008 does not support CURVEPOLYGONs
```

INPUTS

```    dCentreX   (float) : X Ordinate of centre of Circle
@dCentreY  (float) : Y Ordinate of centre of Circle
@dSrid       (int) : Spatial Reference Id of geometry
@iRound      (int) : Float of decimal digits for ordinates.
```

RESULT

```    polygon (geometry) : Circle as CURVEPOLYGON object
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - Oct 2015 - Original coding for TSQL.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 21. COGO/STCrossProductLength (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCrossProductLength -- Computes cross product between two vectors subtended at centre.
```

SYNOPSIS

```    Function STCrossProductLength (
@dStartX  float,
@dStartY  float,
@dCentreX float,
@dCentreY float,
@dEndX    float,
@dEndY    float
)
Returns float
```

DESCRIPTION

```    Computes cross product between vector Centre/Start and Centre/ENd
```

INPUTS

```    @dStartX      (float) - X Ordinate of end of first vector
@dStartY      (float) - Y Ordinate of end of first vector
@dCentreX     (float) - X Ordinate of common end point of vectors
@dCentreY     (float) - Y Ordinate of common end point of vectors
@dEndX        (float) - X Ordinate of end of second vector
@dEndY        (float) - Y Ordinate of end of second vector
```

RESULT

```    cross product (float) - FLoating point cross product value
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - Feb 2011 - Converted to TSQL for SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 22. COGO/STDirectVincenty (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STDirectVincenty -- Vincenty Direct Solution of Geodesics on the Ellipsoid
```

SYNOPSIS

```    Function [\$(cogoowner)].[STDirectVincenty] (
@p_point      geography,
@p_initialBearing float,
@p_distance       float
)
Returns geography
```

DESCRIPTION

```    Computes a destination point given a start point, and initial bearing, and a distance.
Calculated on an ellipsoidal earth model using direct solution of geodesics on the ellipsoid devised by Thaddeus Vincenty.
```

NOTES

```    1. From: T Vincenty, "Direct and Inverse Solutions of Geodesics on the Ellipsoid with application of
nested equations", Survey Review, vol XXIII no 176, 1975. www.ngs.noaa.gov/PUBS_LIB/inverse.pdf.
2. Ellipsoid parameters are taken from sys.spatial_reference_systems.
3. The semi-major axis of the ellipse, a, becomes the equatorial radius of the ellipsoid:
the semi-minor axis of the ellipse, b, becomes the distance from the centre to either pole.
These two lengths completely specify the shape of the ellipsoid.
```

INPUTS

```    @p_point      (geography) - Latitude/Longitude Point
@p_initialBearing (float) - Initial bearing in degrees from north.
@p_distance       (float) - Distance along bearing in metres.
```

RESULT

```    point         (geography) - Destination point, bearing and distance from @p_point.
```

EXAMPLE

```    select [cogo].[STDirectVincenty](geography::Point(-42.5,147.23,4326),90.0,100.0).STAsText() as newPoint
GO

newPoint
POINT (147.23121655963791 -42.499999993543213)

select [cogo].[STDirectVincenty](geography::Point(55.634269978244,12.051864414446,4326),0.0,10.0).STAsText() as newPoint
GO

newPoint
POINT (12.051864414446 55.634359797125562)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Mike Gavaghan (mike@gavaghan.org) - Original Java coding (originally called "STDirectVincenty")
Simon Greener - October 2019 - Ported to SQL Server TSQL.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
MIT Licence
```

# 23. COGO/STDotProduct (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STDotProduct -- Computes Dot product between two vectors subtended at centre.
```

SYNOPSIS

```    Function STDotProduct (
@dStartX  float,
@dStartY  float,
@dCentreX float,
@dCentreY float,
@dEndX    float,
@dEndY    float
)
Returns float
```

DESCRIPTION

```    Computes Dot product between vector Centre/Start and Centre/ENd
```

INPUTS

```    @dStartX    (float) - X Ordinate of end of first vector
@dStartY    (float) - Y Ordinate of end of first vector
@dCentreX   (float) - X Ordinate of common end point of vectors
@dCentreY   (float) - Y Ordinate of common end point of vectors
@dEndX      (float) - X Ordinate of end of second vector
@dEndY      (float) - Y Ordinate of end of second vector
```

RESULT

```    Dot product (float) - FLoating point Dot product value
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - Feb 2011 - Converted to TSQL for SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 24. COGO/STEllipsoidParameters (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STEllipsoidParameters -- Given a SRID returns the semi major axis and flattening from the sys.spatial_reference_systems table.
```

SYNOPSIS

```    Function STEllipsoidParameters (
@p_srid integer
)
Returns geometry
```

DESCRIPTION

```    Supplied with a valid geographic srid (@p_srid) this function extracts the ellipsoidal parameters from
sys.spatial_reference_systems from the ELLOPSOID parameter of the well_known_text field.
```

PARAMETERS

```    @p_srid integer - Spatial reference identifier that exists in sys.spatial_reference_systems.
```

RESULT

```    semi-major axi, flattening (string) -- <semi-major,<flattening> eg "6378249.145, 293.4663077"
```

EXAMPLE

```    select spatial_reference_id,
[cogo].[STEllipsoidParameters](
spatial_reference_id
) as a_f
from sys.spatial_reference_systems
where spatial_reference_id in (4283,4326,4222);
GO

spatial_reference_id a_f
4222                 6378249.145, 293.4663077
4283                 6378137, 298.257222101
4326                 6378137, 298.257223563
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - October 2019 - SQL Server TSQL original coding.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 25. COGO/STFindAngleBetween (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STFindAngleBetween - Computes left or right angle between first and second linestrings in the direction from @p_line to @p_next_line
```

SYNOPSIS

```    Function STFindAngleBetween
@p_line      geometry
@p_next_line geometry,
@p_side      int = -1 -- Left -1; Right +1
)
Return Float
```

DESCRIPTION

```    Supplied with a second linestring (@p_next_line) whose first point is the same as
the last point of @p_line, this function computes the angle between the two linestrings
on either the left (-1) or right (+1) side in the direction of the two segments.
```

NOTES

```    Only supports CircularStrings from SQL Server Spatial 2012 onwards, otherwise supports LineStrings from 2008 onwards.
@p_line must be first segment whose STEndPoint() is the same as @p_next_line STStartPoint(). No other combinations are supported.
```

INPUTS

```    @p_line      (geometry) - A vector that touches the next vector at one end point.
@p_next_line (geometry) - A vector that touches the previous vector at one end point.
@p_side           (int) - The side whose angle is required;
A negative value instructs the function to compute the left angle;
and a positive value the right angle.
```

RESULT

```    angle           (float) - Left or right side angle
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - April 2018 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 26. COGO/STFindCircle (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STFindCircle -- Finds a circle's centre X and Y and Radius from three points.
```

SYNOPSIS

```     Function STFindCircle (
@p_X1 float, @p_Y1 float,
@p_X2 float, @p_Y2 float,
@p_X3 float, @p_Y3 float,
@p_SRID int)
Returns Geometry
```

DESCRIPTION

```    Given a 3 points defining a circular arc this function computes the centre and radius of the circle of
which it is a part of its circumference.
```

NOTES

```    Returns geometry POINT with X = CX, Y = CY, Z = Radius.
Returns -1 as value of all parameters if three points do not define a circle.
Assumes planar projection eg UTM.
```

INPUTS

```    @p_X1   (Float) : X ordinate of first point on circle
@p_Y1   (Float) : Y ordinate of first point on circle
@p_X2   (Float) : X ordinate of second point on circle
@p_Y2   (Float) : Y ordinate of second point on circle
@p_X3   (Float) : X ordinate of third point on circle
@p_Y3   (Float) : Y ordinate of third point on circle
@p_SRID (int)   : Planar SRID value.
```

RESULT

```    Point (geometry) : X ordinate of centre of circle.
Y ordinate of centre of circle.
SRID as supplied.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original coding.
Simon Greener - January 2020  -- Removed call to STMakePoint to speed up funtion.
```

```    (c) 2012-2020 by TheSpatialDBAdvisor/Simon Greener
```

# 27. COGO/STFindCircleByPoint (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STFindCircleByPoint -- Finds the circle centre X and Y and Radius for supplied three points.
```

SYNOPSIS

```     Function STFindCircleByPoint (
@p_point_1 geometry,
@p_point_2 geometry,
@p_point_3 geometry
)
Returns Geometry
```

DESCRIPTION

```    Given 3 points on circumference of a circle this function computes the centre and radius of the circle that defines it.
```

NOTES

```    Returns geometry POINT with X = CX, Y = CY, Z = Radius.
Returns -1 as value of all parameters if three points do not define a circle.
Assumes planar projection eg UTM.
```

INPUTS

```    @p_point_1 (geometry) - First point on circumference of circle
@p_point_2 (geometry) - Second point on circumference of circle
@p_point_3 (geometry) - Third point on circumference of circle
```

RESULT

```    Point           (geometry) : With STX = CX, STY = CY, Z = Radius, STSrid = @p_circular_arc.STSrid
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - June 2018 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 28. COGO/STFindCircleFromArc (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STFindCircleFromArc -- Finds the circle centre X and Y and Radius for supplied CircularString
```

SYNOPSIS

```     Function STFindCircleFromArc (
@p_circular_arc geometry
)
Returns Geometry
```

DESCRIPTION

```    Given a 3 point circular arc this function computes the centre and radius of the circle that defines it.
```

NOTES

```    Returns geometry POINT with X = CX, Y = CY, Z = Radius.
Returns -1 as value of all parameters if three points do not define a circle.
Assumes planar projection eg UTM.
```

INPUTS

```    @p_circular_arc (geometry) : 3 Point Circular Arc geometry
```

RESULT

```    Point           (geometry) : With STX = CX, STY = CY, Z = Radius, STSrid = @p_circular_arc.STSrid
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 29. COGO/STFindDeflectionAngle (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STFindDeflectionAngle - Computes deflection angle between from line and to line.
```

SYNOPSIS

```    Function STFindDeflectionAngle
@p_from_line geometry
@p_to_line   geometry
)
Return Float
```

DESCRIPTION

```    Two modes:
If @p_from_line and @p_to_line are supplied the function generates the deflection angle between them.
If only @p_from_line is supplied it must be a CIRCULARSTRING whereby deflection angle is computed from
points 1,2 and 2,3. *Useful for computing rotation of circular arc to left or to right.
If @p_to_line is supplied its first point must be the same as the last point of @p_from_line.
```

NOTES

```    Only supports CircularStrings from SQL Server Spatial 2012 onwards, otherwise supports LineStrings from 2008 onwards.
@p_line must be first segment whose STEndPoint() is the same as @p_next_line STStartPoint(). No other combinations are supported.
```

INPUTS

```    @p_from_line (geometry) - A linestring segment
@p_to_line   (geometry) - A second linestring segment whose direction is computed from the start linestring direction + deflection angle.
```

RESULT

```    angle           (float) - Deflection angle in degrees.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - April 2018 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 30. COGO/STFindLineIntersection (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STFindLineIntersection -- Finds intersection point between two simple, two ordinate, line segments.
```

SYNOPSIS

```    Function STFindLineIntersection (
@x11     float, @y11     float,
@x12     float, @y12     float,
@x21     float, @y21     float,
@x22     float, @y22     float
)
Returns geometry
```

USAGE

```    Print 'Crossed Lines ...';
GO
SELECT f.intersections.AsTextZM() as intersection,
f.intersections.STGeometryN(1).AsTextZM() as iPoint,
f.intersections.STGeometryN(2).AsTextZM() as iPointOnSegment1,
f.intersections.STGeometryN(3).AsTextZM() as iPointOnSegment1
FROM (SELECT [cogo].[STFindLineIntersection](0,0,10,10,0,10,10,0) as intersections ) as f;
GO
intersection                     iPoint      iPointOnSegment1 iPointOnSegment1
-------------------------------- ----------- ---------------- ----------------
MULTIPOINT ((5 5), (5 5), (5 5)) POINT (5 5) POINT (5 5)      POINT (5 5)

Print 'Extended Intersection ...';
GO
SELECT f.intersections.AsTextZM() as intersection,
f.intersections.STGeometryN(1).AsTextZM() as iPoint,
f.intersections.STGeometryN(2).AsTextZM() as iPointOnSegment1,
f.intersections.STGeometryN(3).AsTextZM() as iPointOnSegment1
FROM (SELECT [cogo].[STFindLineIntersection](0,0,10,10,0,10,10,0) as intersections ) as f;
GO
intersection                     iPoint      iPointOnSegment1 iPointOnSegment1
-------------------------------- ----------- ---------------- ----------------
MULTIPOINT ((5 5), (5 5), (4 6)) POINT (5 5) POINT (5 5)      POINT (4 6)

Print 'Parallel Lines (meet at single point)....';
GO
SELECT f.intersections.AsTextZM() as intersection,
f.intersections.STGeometryN(1).AsTextZM() as iPoint,
f.intersections.STGeometryN(2).AsTextZM() as iPointOnSegment1,
f.intersections.STGeometryN(3).AsTextZM() as iPointOnSegment1
FROM (SELECT [\$(cogoowner)].[STFindLineIntersection] (0,0,10,0, 0,20,10,0) as intersections ) as f;
GO
intersection                        iPoint       iPointOnSegment1 iPointOnSegment1
----------------------------------- ------------ ---------------- ----------------
MULTIPOINT ((10 0), (10 0), (10 0)) POINT (10 0) POINT (10 0)     POINT (10 0)

Print 'Parallel Lines that do not meet at single point....';
GO
SELECT f.intersections.AsTextZM() as intersection,
f.intersections.STGeometryN(1).AsTextZM() as iPoint,
f.intersections.STGeometryN(2).AsTextZM() as iPointOnSegment1,
f.intersections.STGeometryN(3).AsTextZM() as iPointOnSegment1
FROM (SELECT [\$(cogoowner)].[STFindLineIntersection] (0,0,10,0, 0,1,10,1) as intersections ) as f;
GO
intersection                        iPoint       iPointOnSegment1 iPointOnSegment1
----------------------------------- ------------ ---------------- ----------------
MULTIPOINT ((10 0), (10 0), (10 0)) POINT (10 0) POINT (10 0)     POINT (10 0)
```

DESCRIPTION

```    Finds intersection point between two lines:
1. If first and second segments have a common point, it is returned for all three points.
2. Point(1) is the point where the lines defined by the segments intersect.
3. Point(2) is the point on segment 1 that is closest to segment 2 (can be Point(1) or Start/End point )
4. Point(3) is the point on segment 2 that is closest to segment 1 (can be Point(1) or Start/End point )
5. If the lines are parallel, all returned ordinates are set to @c_MaxFloat of -1.79E+308
6. If the point of intersection is not on both segments, then this is almost certainly not the
point where the two segments are closest.

If the lines are parallel, all returned
-------
Method:
Treat the lines as parametric where line 1 is:
X = x11 + dx1 * t1
Y = y11 + dy1 * t1
and line 2 is:
X = x21 + dx2 * t2
Y = y21 + dy2 * t2
Setting these equal gives:
x11 + dx1 * t1 = x21 + dx2 * t2
y11 + dy1 * t1 = y21 + dy2 * t2
Rearranging:
x11 - x21 + dx1 * t1 = dx2 * t2
y11 - y21 + dy1 * t1 = dy2 * t2
(x11 - x21 + dx1 * t1) *   dy2  = dx2 * t2 *   dy2
(y11 - y21 + dy1 * t1) * (-dx2) = dy2 * t2 * (-dx2)
(x11 - x21) * dy2 + ( dx1 * dy2) * t1 +
(y21 - y11) * dx2 + (-dy1 * dx2) * t1 = 0
Solving for t1 gives:
t1 * (dy1 * dx2 - dx1 * dy2) =
(x11 - x21) * dy2 + (y21 - y11) * dx2
t1 = ((x11 - x21) * dy2 + (y21 - y11) * dx2) /
(dy1 * dx2 - dx1 * dy2)
Now solve for t2.
----------
@Note       : If 0 <= t1 <= 1, then the point lies on segment 1.
: If 0 <= t2 <= 1, then the point lies on segment 1.
: If dy1 * dx2 - dx1 * dy2 = 0 then the lines are parallel.
: If the point of intersection is not on both
: segments, then this is almost certainly not the
: point where the two segments are closest.
```

INPUTS

```    @x11 (float) - X Ordinate of the start point for the first vector
@y11 (float) - Y Ordinate of the start point for the first vector
@x12 (float) - X Ordinate of the end point for the first vector
@y12 (float) - Y Ordinate of the end point for the first vector
@x21 (float) - X Ordinate of the start point for the second vector
@y21 (float) - Y Ordinate of the start point for the second vector
@x22 (float) - X Ordinate of the end point for the second vector
@y22 (float) - Y Ordinate of the end point for the second vector
```

RESULT

```    MultiPoint (geometry) - (iPoint)  Intersection point,
(iPoint1) Intersection point on linestring 1.
(iPoint2) Intersection point on linestring 2.
```

NOTES

```    Assumes planar projection eg UTM.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - May 2008 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 31. COGO/STFindLineIntersectionBySegment (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STFindLineIntersectionBySegment -- Finds intersection point between two simple, two ordinate, line segments.
```

SYNOPSIS

```    Function STFindLineIntersectionBySegment (
@p_line_segment_1 geometry,
@p_line_segment_2 geometry
)
Returns geometry;
```

USAGE

```    SELECT [\$(cogoowner)].[STFindLineIntersectionBySegment] (
geometry::STLineFromText('LINESTRING(0 0,10 10)',0),
geometry::STLineFromText('LINESTRING(0 10,10 0)',0)
).AsTextZM() as Intersection
GO
Intersection
MULTIPOINT ((5 5), (5 5), (5 5))
```

DESCRIPTION

```    Finds intersection point between two lines:
Calls STFindLineIntersection so see its documentation.
```

INPUTS

```    @p_line_segment_1 (geometry) - 2 Point LineString.
@p_line_segment_2 (geometry) - 2 Point LineString.
```

RESULT

```    MultiPoint (geometry) - (iPoint)  Intersection point,
(iPoint1) Intersection point on linestring 1.
(iPoint2) Intersection point on linestring 2.
```

NOTES

```    Only Supports 2 Point LineStrings.
Assumes planar projection eg UTM.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - May 2008 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 32. COGO/STFindLineIntersectionDetails (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STFindLineIntersectionDetails -- Interprets intersection that results from a call to STFindLineIntersectionBySegment with same parameter values.
```

SYNOPSIS

```    Create Function STFindLineIntersectionDetails
@p_line_segment_1 geometry,
@p_line_segment_2 geometry
)
Returns varchar(max);
```

USAGE

```    with data as (
select c.IntValue as offset,
geometry::STGeomFromText('LINESTRING (0.0 0.0, 20.0 0.0, 20.0 10.0)',0) as line
from [\$(owner)].[Generate_Series] (0,-25,-5) as c
)
select f.offset,
[\$(owner)].[STRound]([\$(cogoowner)].[STFindLineIntersectionBySegment] (first_segment,second_segment),3,3,1,1).STAsText() as geom,
[\$(cogoowner)].[STFindLineIntersectionDetails](first_segment,second_segment) as reason
from (select b.offset,
[\$(owner)].[STOffsetSegment](                                                   a.geom,b.offset,8,8) as first_segment,
[\$(owner)].[STOffsetSegment](lead(a.geom,1) over (partition by b.offset order by a.id),b.offset,8,8) as second_segment
from data as b
cross apply
[\$(owner)].[STSegmentLine] (b.line) as a
) as f
where second_segment is not null
order by offset;
GO
offset geom                                   reason
-25    MULTIPOINT ((-5 25), (0 25), (-5 10))  Virtual Intersection Near Start 1 and End 2
-20    MULTIPOINT ((0 20), (0 20), (0 10))    Virtual Intersection Within 1 and Near End 2
-15    MULTIPOINT ((5 15), (5 15), (5 10))    Virtual Intersection Within 1 and Near End 2
-10    MULTIPOINT ((10 10), (10 10), (10 10)) Intersection within both segments
-5    MULTIPOINT ((15 5), (15 5), (15 5))    Intersection within both segments
0    MULTIPOINT ((20 0), (20 0), (20 0))    Intersection at End 1 Start 2
```

DESCRIPTION

```    Describes intersection point between two lines:
Internal code is same as STFindLineIntersection with parameters from STFindLineIntersectionBySegment so see their documentation.
Processes code that determines intersections as per STFindLineIntersection but determines nature of intersection ie whether physical, virtual, nearest point on segment etc.
```

INPUTS

```    @p_line_segment_1 (geometry) - 2 Point LineString.
@p_line_segment_2 (geometry) - 2 Point LineString.
```

RESULT

```    Interpretation (varchar) - One of:
Intersection at End 1 End 2
Intersection at End 1 Start 2
Intersection at Start 1 End 2
Intersection at Start 1 Start 2
Intersection within both segments
Parallel
Unknown
Virtual Intersection Near End 1 and End 2
Virtual Intersection Near End 1 and Start 2
Virtual Intersection Near Start 1 and End 2
Virtual Intersection Near Start 1 and Start 2
Virtual Intersection Within 1 and Near End 2
Virtual Intersection Within 1 and Near Start 2
Virtual Intersection Within 2 and Near End 1
Virtual Intersection Within 2 and Near Start 1
```

NOTES

```    Only Supports 2 Point LineStrings.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - March 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 33. COGO/STFindPointBisector (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

```   FindPointBisector - Computes offset point on the bisector between two linestrings.
```

SYNOPSIS

```    Function STFindPointBisector
@p_line      geometry
@p_next_line geometry,
@p_offset    Float = 0.0,
@p_round_xy  int   = 3,
@p_round_z   int   = 2,
@p_round_m   int   = 1
)
Return Geometry (Point)
```

DESCRIPTION

```    Supplied with a second linestring (@p_next_line) whose first point is the same as
the last point of @p_line, this function computes the bisector between the two linestrings
and then creates a new vertex at a distance of @p_offset from the shared intersection point.
If an @p_offset value of 0.0 is supplied, the intersection point is returned.
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 linestrings.
The returned vertex has its ordinate values rounded using the relevant decimal place values.
```

NOTES

```    Only supports CircularStrings from SQL Server Spatial 2012 onwards, otherwise supports LineStrings from 2008 onwards.
```

INPUTS

```    @p_line      (geometry) - A vector that touches the next vector at one end point.
@p_next_line (geometry) - A vector that touches the previous vector at one end point.
@p_offset       (float) - The perpendicular distance to offset the point generated using p_ratio.
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_z        (int) - Number of decimal digits of precision for an Z ordinate.
@p_round_m        (int) - Number of decimal digits of precision for an M ordinate.
```

RESULT

```    point        (geometry) - New point on bisection point or along bisector line with optional perpendicular offset.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2013 - Original coding.
Simon Greener - December 2019 - Fixed bug with Z/M handling.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 34. COGO/STGeographicDistance (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STGeographicDistance -- Computes distance in meters along parallel latitude/medidian longitude or direct.
```

SYNOPSIS

```    Function [\$(cogoowner)].[STGeographicDistance] (
@p_point1 geography,
@p_point2 geography,
@p_method varchar(20) = 'Latitude' -- Or Longitude or Point2Point
)
Returns float
```

DESCRIPTION

```    Computes distance between two points either:
1 Along parallel of latitude;
2. Meridian of Longitude
3. Or directly from point to point.
NOTE
Uses geography STDistance function.
```

INPUTS

```    @p_point1 (geography) - Geographic point
@p_point2 (geography) - Geographic point
@p_method   (varchar) - Type of distance:  Latitude, Longitude, or Point2Point
```

RESULT

```    distance      (float) - Distance in meters.
```

EXAMPLE

```    with two_points as (
select geography::Point(55.4748508,12.1603670,4268) as point1,
geography::Point(55.4786191,12.1713976,4268) as point2
)
select srid, method, uom_distance, srs.unit_of_measure,
uom_distance * CAST(srs.unit_conversion_factor as float) as meters
from (select 'Longitude' as method,
point1.STSrid as srid,
[cogo].[STGeographicDistance] (point1,point2,'Longitude') as uom_distance
from two_points
union all
select 'Latitude' as method,
point1.STSrid as srid,
[cogo].[STGeographicDistance] (point1,point2,'Latitude') as uom_distance
from two_points
union all
select 'Point2Point' as method,
point1.STSrid as srid,
[cogo].[STGeographicDistance] (point1,point2,'Point2Point') as uom_distance
from two_points
) as f
inner join
sys.spatial_reference_systems as srs
on (srs.spatial_reference_id = f.srid)
GO

srid method      uom_distance     unit_of_measure meters
4268 Longitude   1376.48851124913 US survey foot  419.554537886726
4268 Latitude    2288.65879496564 US survey foot  697.584596787393
4268 Point2Point 2670.61416540797 US survey foot  814.004826690991
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - October 2019 - SQL Server TSQL Original Coding.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 35. COGO/STGreatCircleBearing (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STGreatCircleBearing -- Returns a (Normalized) bearing in Degrees between two lat/long coordinates
```

SYNOPSIS

```    Function STGreatCircleBearing (
@p_dLon1 float,
@p_dLat2 float,
@p_dLon1 float,
@p_dLat2 float
)
Returns float
```

USAGE

```    SELECT [GISDB].[\$(owner)].[STGreatCircleBearing](0,0,45,45) as Great_Circle_Bearing;
Great_Circle_Bearing
35.2643896827547
```

DESCRIPTION

```    Function that computes the bearing from the supplied start point (@p_dx1) to the supplied end point (@p_dx2).
The result is expressed as a whole circle bearing in decimal degrees.
```

INPUTS

```    @p_dLon1 (float) - Longitude of starting point.
@p_dLat1 (float) - Latitude of starting point.
@p_dLon2 (float) - Longitude of finish point.
@p_dLat2 (float) - Latitude of finish point.
```

RESULT

```    decimal degrees -- Bearing from point 1 to 2 in range 0-360.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 36. COGO/STGreatCircleDistance (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STGreatCircleDistance -- Computes great circle distance between two lat/long coordinates
```

SYNOPSIS

```    Function STGreatCircleDistance (
@p_dLon1             float,
@p_dLat2             float,
@p_dLon1             float,
@p_dLat2             float,
@p_equatorial_radius Float = 6378137,      -- Default is WGS-84 in meters
@p_flattening        Float = 298.257223563 -- Default is WGS-84 ellipsoid flattening factor
)
Returns float
```

USAGE

```    SELECT well_known_text FROM sys.spatial_reference_systems where spatial_reference_id = 4326;
well_known_text
GEOGCS["WGS 84", DATUM["World Geodetic System 1984", ELLIPSOID["WGS 84", 6378137, 298.257223563]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]

select [GISDB].[\$(owner)].[STGreatCircleDistance](0,0,45,45,6378137,298.257223563) as Great_Circle_Distance
union all
select [GISDB].[\$(owner)].[STGreatCircleDistance](0,0,45,45,default,default)       as Great_Circle_Distance
union all
select geography::Point(0,0,4326).STDistance(geography::Point(45,45,4326))         as Great_Circle_Distance;

Great_Circle_Distance
6662444.94352008
6662444.94352008
6662473.57317356
```

DESCRIPTION

```    Function that computes a great circle distance between the supplied start (@p_dx1) and end points (@p_dx2).
The result is expressed in meters.
```

NOTES

```    Should be same as geographic::STPointFromText(
```

INPUTS

```    @p_dLon1             (float) - Longitude of starting point.
@p_dLat1             (float) - Latitude of starting point.
@p_dLon2             (float) - Longitude of finish point.
@p_dLat2             (float) - Latitude of finish point.
@p_flattening        (float) - Ellipsoid flattening factor: Default is WGS-84
```

RESULT

```    distance -- Distance from point 1 to 2 in meters.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 37. COGO/STInverseVincenty (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STInverseVincenty -- Vincenty inverse calculation.
```

SYNOPSIS

```    Function [\$(cogoowner)].[STInverseVincenty] (
@p_point1 geography,
@p_point2 geography
)
Returns float
```

DESCRIPTION

```    Computes distance in meters between two geographic points.

Vincenty Inverse Solution of Geodesics on the Ellipsoid (c) Chris Veness 2002-2019
www.movable-type.co.uk/scripts/latlong-ellipsoidal-vincenty.html
www.movable-type.co.uk/scripts/geodesy-library.html#latlon-ellipsoidal-vincenty

Distances & bearings between points, and destination points given start points & initial bearings,
calculated on an ellipsoidal earth model using direct solution of geodesics on the ellipsoid
```

```    [\$(cogoowner)].[STGeographicDistance]
```

NOTES

```    1. From: T Vincenty, "Direct and Inverse Solutions of Geodesics on the Ellipsoid with application of
nested equations", Survey Review, vol XXIII no 176, 1975. www.ngs.noaa.gov/PUBS_LIB/inverse.pdf.
2. Ellipsoid parameters are taken from sys.spatial_reference_systems.
```

INPUTS

```    @p_point1 (geography) - First Latitude/Longitude Point
@p_point2 (geography) - Second Latitude/Longitude Point
```

RESULT

```    distance     (float) - Distance between @p_point1 and @p_point2 in meters.
```

EXAMPLE

```    SELECT [\$(cogoowner)].[STInverseVincenty] (
geography::Point(12.1603670,55.4748508,4326),
geography::Point(12.1713976,55.4786191,4326)) as meters;
GO

meters
1287.32279362667
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Chris Veness  - Original JavaScript coding
Simon Greener - October 2019 - Ported to SQL Server TSQL.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
MIT Licence
```

# 38. COGO/STisClockwiseAngle (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STisClockwiseAngle - Supplied with a positive or negative angle this function returns 1 or 0 to indicate if Clockwise (+) or AntiClockwise (-)
```

SYNOPSIS

```    Function STisClockwiseAngle (
@p_angle float
)
Returns bit
```

DESCRIPTION

```    Supplied with an angle this function returns 1 if clockwise and 0 is anticlockwise.
```

INPUTS

```    @p_angle (float) - Angle in radians
```

RESULT

```    TrueFalse  (bit) - 1 if clockwise and 0 is anticlockwise.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original TSQL coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 39. COGO/STisClockwiseArc (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STisClockwiseArc - Supplied with a positive or negative angle this function returns 1 or 0 to indicate if Clockwise (+) or AntiClockwise (-)
```

SYNOPSIS

```    Function STisClockwiseArc (
@p_circular_arc geometry
)
Returns Int
```

DESCRIPTION

```    Supplied with a single CircularString this function returns 1 if CircularString is defecting to the right (clockwise) or -1 to the left (anticlockwise).
```

INPUTS

```    @p_CircularArc (geometry) - Single CircularString geometry (3 points)
```

RESULT

```    TrueFalse  (bit) - 1 if clockwise and -1 is anticlockwise.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2018 - Original TSQL coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 40. COGO/STLine2Cogo (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STLine2Cogo - Converts LineString into COGO XML structure for use in STCogo2Line.
```

SYNOPSIS

```   Function STLine2Cogo (
@p_linestring  geometry,
@pDegreeSymbol NVarChar(1),
@pMinuteSymbol NVarChar(1),
@pSecondSymbol NVarChar(1)
)
Returns XML
```

EXAMPLE

```    -- Write 2D with DMS string bearings
SELECT [\$(cogoowner)].[STLine2Cogo] (
geometry::STGeomFromText('LINESTRING (10 10, 8.163 17.034, 158.755 35.432, 157.565 25.108)',0),
CHAR(176),CHAR(39),'"')
GO
<Cogo srid="0">
<Segments>
<Segment>
<MoveTo>POINT (10 10)</MoveTo>
<DegMinSec> 345°21'48.75"</DegMinSec>
<Distance>345.364</Distance>
</Segment>
<Segment>
<DegMinSec>  83° 2'4.652"</DegMinSec>
<Distance>83.0346</Distance>
</Segment>
<Segment>
<DegMinSec> 186°34'30.73"</DegMinSec>
<Distance>186.575</Distance>
</Segment>
</Segments>
</Cogo>
```

DESCRIPTION

```    This function converts each segment of a (multi)linestring into a COGO bearing and distance XML Segment.
The COGO references are returning in the order they appear in the geometry object.
The first point of the start of a LineString element is returned as a <MoveTo> element.
If all three symbol parameters are NULL, <Bearing> elements are created holding decimal degrees, else <DegMinSec> elements are written.
NOTE
Measured lines are unsupported.
```

INPUTS

```    @p_linestring  (geometry)    - A LINESTRING or MULTILINESTRING object.
@pDegreeSymbol (NVarChar(1)) - Degrees symbol eg ^
@pMinuteSymbol (NVarChar(1)) - Seconds symbol eg '
@pSecondSymbol (NVarChar(1)) - Seconds symbol eg "
```

RESULT

```    COGO Object    (XML);
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - June 2018 - Original coding.
```

```    (c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 41. COGO/STLine2CogoAsTable (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STLine2CogoAsTable - Dumps all segment of supplied linestring geometry object to bearing and distance tuples.
```

SYNOPSIS

```   Function STLine2CogoAsTable (
@p_linestring  geometry,
@pDegreeSymbol NVarChar(1),
@pMinuteSymbol NVarChar(1),
@pSecondSymbol NVarChar(1)
)
Returns @segments Table (
segment_id int,          -- Unique integer
element_id int,          -- If Linestring, 1, else part of MULTILINESTRING
dms        varchar(100), -- Bearing expressed as DMS string
bearing    float,        -- Bearing expressed as DD
distance   float,        -- Length of segment
deltaZ     float         -- delta Z along segment
)
```

EXAMPLE

```    SELECT t.*
FROM [\$(cogoowner)].[STLine2CogoAsTable](geometry::STGeomFromText('MULTILINESTRING((0 0,1 1,2 2),(100 100,110 110,130 130))',0),
NULL,NULL,NULL) as t
ORDER BY t.segment_id;
GO
segment_id element_id    dms          bearing distance         deltaZ
1          1          45° 0'0.000" 45      1.4142135623731  NULL
2          1          45° 0'0.000" 45      1.4142135623731  NULL
3          2          45° 0'0.000" 45      14.142135623731  NULL
4          2          45° 0'0.000" 45      28.2842712474619 NULL
```

DESCRIPTION

```    This function converts each segment of a (multi)linestring into a COGO bearing and distance.
The COGO references are returning in the order they appear in the geometry object.
```

INPUTS

```    @p_linestring  (geometry)    - A LINESTRING or MULTILINESTRING object.
@pDegreeSymbol (NVarChar(1)) - Degrees symbol eg ^
@pMinuteSymbol (NVarChar(1)) - Seconds symbol eg '
@pSecondSymbol (NVarChar(1)) - Seconds symbol eg "
```

RESULT

```    Table (Array) of Points :
segment_id  (int) - Unique segment id starting at first and ending at last regardless as to whether multilinestring.
element_id  (int) - Identifier unique to each element eg MultiLineString first LINESTRING is 1, second is 2 etc.
dms     (varchar) - DMS eg 149^10'11.1"
bearing   (float) - DD eg 149.1334343
distance  (float) - length of segment (if geodetic SRID will be in meters).
deltaZ    (float) - end z ordinate minus start z ordinate for each segment.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - June 2018 - Original coding.
```

```    (c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 42. COGO/STNormalizeBearing (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STNormalizeBearing -- Function ensures supplied bearing is between 0 and 360.
```

SYNOPSIS

```    Function STNormalizeBearing(@p_bearing float)
Returns Float
```

USAGE

```    SELECT [\$(cogoowner)].[STNormalizeBearing](450.39494) as bearing;
bearing
90.39494
```

DESCRIPTION

```    Function that ensures supplied bearing is between 0 and 360 degrees (360 = 0).
```

INPUTS

```    @p_bearing (float) : Non-NULL decimal bearing.
```

RESULT

```    bearing (float) : Bearing between 0 and 360 degrees.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 43. COGO/STOptimalCircleSegments (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STOptimalCircleSegments -- Computes optimal number of chord segments to stroke circle as vertex-connected polygon.
```

SYNOPSIS

```    Function STOptimalCircleSegments (
@p_dArcToChordSeparation Float
)
Returns int
SELECT [\$(cogoowner)].[STOptimalCircleSegments](100, 0.003);
```

DESCRIPTION

```    Returns the optimal integer number of circle segments for an arc-to-chord separation given the radius
```

NOTES

```    Assumes planar projection eg UTM.
```

INPUTS

```    @p_dRadius               (float) : Radius of Circle
@p_dArcToChordSeparation (float) : Distance between the midpoint of the Arc and the Chord in metres
```

RESULT

```    number of segments         (int) : The optimal number of segments at the given arc2chord separation
```

AUTHOR

```    Simon Greener
```

HISTORY

```   Simon Greener - May 2011 - Converted to TSQL for SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 44. COGO/STOrientationIndexFilter (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STOrientationIndexFilter -- A filter for computing the orientation index of three coordinates.
```

SYNOPSIS

```    Function STOrientationIndexFilter (
@p_pa geometry,
@p_pb geometry,
@p_pc geometry
)
Returns geometry
```

DESCRIPTION

```    If the orientation can be computed safely this routine returns the orientation index.
Otherwise, a value i > 1 is returned. In this case the orientation index must be computed using some other more robust method.
```

INPUTS

```    @p_pa (geometry) -- Point A
@p_pb (geometry) -- Point B
@p_pc (geometry) -- Point C
```

RESULT

```    orientation index (integer) -- The orientation index if it can be computed safely or index > 1 if the orientation index cannot be computed safely
NOTE
This is a port of the algorithm in JTS.
Uses an approach due to Jonathan Shewchuk, which is in the public domain.
```

EXAMPLE

```    with data as (
SELECT geometry::STGeomFromText('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (1 1, 1 9, 9 9, 9 1, 1 1))',0) as polygon
)
select 'Exterior' as Ring, [\$(owner)].[STisCCW](d.polygon.STExteriorRing()) as isCCW from data as d
union all
select 'Interior' as Ring, [\$(owner)].[STisCCW](d.polygon.STInteriorRingN(1)) as isCCW from data as d;
GO

Ring     isCCW
Exterior 1
Interior 0
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Martin Davis  - Original Java coding for Java Topology Suite
Simon Greener - October 2019 - Ported to SQL Server TSQL.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 45. COGO/STPointFromBearingAndDistance (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STPointFromBearingAndDistance -- Returns a projected point given starting point, a bearing in Degrees, and a distance (geometry SRID units).
```

SYNOPSIS

```    Function STPointFromBearingAndDistance (
@p_dStartE   float,
@p_dStartN   float,
@p_dBearing  float,
@p_dDistance float
@p_round_xy  int = 3,
@p_srid      int = 0
)
Returns float
```

USAGE

```    SELECT [\$(cogoowner)].[STPointFromBearingAndDistance] (0,0,45,100,3,0).STAsText() as endPoint;
endPoint
POINT (70.711 70.711)
```

DESCRIPTION

```    Function that computes a new point given a starting coordinate, a whole circle bearing and a distance (SRID Units).
Returned point's XY ordinates are rounded to @p_round_xy decimal digits of precision.
@p_SRID is the SRID of the supplied start point.
```

INPUTS

```    @p_dStartE   (float) - Easting of starting point.
@p_dStartN   (float) - Northing of starting point.
@p_dBearing  (float) - Whole circle bearing between 0 and 360 degrees.
@p_dDistance (float) - Distance in SRID units from starting point to required point.
@p_round_xy    (int) - XY ordinates decimal digitis of precision.
@p_srid        (int) - SRID associated with @p_dStartE/p_dStartN.
```

RESULT

```    point    (geometry) - Point
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 46. COGO/STPointFromCOGO (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STPointFromCOGO -- Returns a projected point given starting point, a bearing in Degrees, and a distance (geometry SRID units).
```

SYNOPSIS

```    Function STPointFromCOGO (
@p_Start_Point geometry,
@p_dBearing    float,
@p_dDistance   float
@p_round_xy    int = 3
)
Returns geometry
```

USAGE

```    SELECT [\$(cogoowner)].[STPointFromCOGO] (geometry::Point(0,0,0),45,100,3).STAsText() as endPoint;
endPoint
POINT (70.711 70.711)
```

DESCRIPTION

```    Is a wrapper function over STPointFromBearingAndDistance.
Function that computes a new point given a starting coordinate, a whole circle bearing and a distance (SRID Units).
Returned point's XY ordinates are rounded to @p_round_xy  decimal digits of precision.
SRID of the returned geometry is the SRID supplied start point.
```

INPUTS

```    @p_Start_Point (geometry) - Starting point.
@p_dBearing       (float) - Whole circle bearing between 0 and 360 degrees.
@p_dDistance      (float) - Distance in SRID units from starting point to required point.
@p_round_xy         (int) -    XY ordinates decimal digitis of precision.
```

RESULT

```    point          (geometry) - Point
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 47. COGO/STResection [ Functions ]

[ Top ] [ Functions ]

NAME

```    ST_Resection -- Creates a square buffer to left or right of a linestring.
```

SYNOPSIS

```    Function [\$(cogoowner)].[STResection] (
@p_point1     geometry,
@p_angle1     float,
@p_point2     geometry,
@p_angle2     float,
@p_point3     geometry,
@p_angle3     float,
@p_angle_type varchar(1) = 'I'
)
Returns geometry
```

NOTES

```    All three angles must add up to 360.0
Points must be supplied in clockwise order.
```

TODO

```    Still under development.
```

RESULT

```    point (geometry) - Result of resection
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2019 - Original TSQL coding
```

```    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 48. COGO/STSubtendedAngle (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STSubtendedAngle - Returns the angle (radians) between three points.
```

SYNOPSIS

```    Function STSubtendedAngle (
@p_startX  float,
@p_startY  float,
@p_centreX float,
@p_centreY float,
@p_endX    float,
@p_endY    float
)
```

DESCRIPTION

```    Supplied with three points, this function computes the angle from the first to the third subtended by the seconds.
Angle could be positive or negative.
```

NOTES

```    Assumes planar projection eg UTM.
Always choses smallest angle ie 90 not 270
```

INPUTS

```    @p_startX  (float) - X ordinate of first point
@p_startY  (float) - Y ordinate of first point
@p_centreX (float) - X ordinate of first point
@p_centreY (float) - Y ordinate of first point
@p_endX    (float) - X ordinate of first point
@p_endY    (float) - Y ordinate of first point
```

RESULT

```    angle      (float) - Subtended angle in radians.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original TSQL coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 49. COGO/STSubtendedAngleByPoint (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STSubtendedAngleByPoint - Returns the angle (radians) between three points.
```

SYNOPSIS

```    Function STSubtendedAngle (
@p_start  geometry,
@p_centre geometry,
@p_end    geometry
)
```

DESCRIPTION

```    Supplied with three points, this function computes the angle from the first to the third subtended by the seconds.
Angle could be positive or negative.
```

NOTES

```    Assumes planar projection eg UTM.
Always choses smallest angle ie 90 not 270
```

INPUTS

```    @p_start  (geometry) - First point
@p_centre (geometry) - Second point
@p_end    (geometry) - Third point
```

RESULT

```    angle      (float) - Subtended angle in radians.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original TSQL coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 50. CONVERSION/STPointGeomAsText (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STPointGeomAsText -- Function that returns a formatted string representation of a point's ordinates rounded to supplied tolerances.
```

SYNOPSIS

```    Function STPointGeomAsText (
@p_point   geometry,
@p_round_x    int = 3,
@p_round_y    int = 3,
@p_round_z    int = 2,
@p_round_m    int = 2
)
Returns varchar(max)
```

USAGE

```    SELECT [\$(owner)].[STPointGeomAsText] (
geometry::STPointFromText('POINT (0.1232332 0.21121 0.1213 0.41)',0),
3, 2, 1
) as point;
GO
point
'0.123 0.211 0.12 0.4'
```

DESCRIPTION

```    This function returns a formatted string representation of a point with up to 4 ordinates.
The function is suitable for use in WKT text constructors as shown in the USAGE element of this documentation.
The function correctly rounds each ordinate using the supplied rounding factor.
This function is different from the standard .AsTextZM() as it also rounds the ordinates and does not return the POINT () elements.
```

NOTES

```    Wrapper over STPointAsText
```

INPUTS

```    @p_point (geometry) - Geometry Point
@p_round_xy   (int) - XY Ordinates rounding factor.
@p_round_z    (int) - Z Ordinate rounding factor.
@p_round_m    (int) - M Ordinate rounding factor.
```

RESULT

```    formatted string (varchar max) - Formatted string.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2008 - Original Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 51. CONVERSION/STToGeography (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STToGeography - Given a geometry object returns a geography object.
```

SYNOPSIS

```    Function STToGeography (
@p_geom geometry,
@p_srid Int = NULL
)
Returns geography
```

EXAMPLE

```    SELECT [\$(owner)].[STToGeography] (
geometry::STGeomFromText('LINESTRING(147.234 -43.2345, 148.234 -43.2345)',0),
4326
).STAsText()
as geog
GO
geog
LINESTRING(147.234 -43.2345, 148.234 -43.2345)
```

DESCRIPTION

```    Given valid geometry object this function converts it to a geography object, assigns @p_srid value and returns.
The @p_srid value should be a valid geography SRID.
Mainly useful for converting 2008 geometry objects with geographic data to 2012+ geography objects.  *  INPUTS
@p_geom (geometry) - A valid geometry object.
@p_srid      (int) - Valid geographic SRID. If null, uses SRID associated with supplied geometry.
```

RESULT

```    geography (geography) - Geography object.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Aug 20012 - Initial SQL Server 20012 implementation
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 52. CONVERSION/STToGeometry (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STToGeometry - Given a geography object returns a geometry object.
```

SYNOPSIS

```    Function STToGeometry (
@p_geog geography,
@p_srid Int = NULL
)
Returns geometry
```

EXAMPLE

```    SELECT [\$(owner)].[STToGeometry] (
geography::STGeomFromText('LINESTRING(147.234 -43.2345, 148.234 -43.2345)',4326),
NULL
).STAsText() as geom;
GO
geom
LINESTRING (147.234 -43.2345, 148.234 -43.2345)
```

DESCRIPTION

```    Given valid geography object this function converts it to a geometry object, assigns @p_srid value and returns.
The @p_srid value should be a valid projected SRID.
Mainly useful for converting 2012 geography objects to geometry equalivalent to be able to use functions only available for geometry.
```

INPUTS

```    @p_geog (geography) - A valid geographic object.
@p_srid       (int) - Valid projected SRID. If null, uses SRID associated with supplied geography
```

RESULT

```    geometry (geometry) - Geometry object.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Aug 20012 - Initial SQL Server 20012 implementation
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 53. CREATE/STMakeEnvelope (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakeEnvelope -- Function that constructs a 5 point polygon from supplied ordinates.
```

SYNOPSIS

```    Function [\$(owner)].[STMakeEnvelope] (
@p_mbr_coords in varchar,
@p_srid       in integer default null
)
Returns geometry
```

DESCRIPTION

NOTES

```    2D only.
```

INPUTS

```    @p_minx   (float) - Lower left x ordinate.
@p_miny   (float) - Lower left y ordinate.
@p_maxx   (float) - Upper left x ordinate.
@p_maxy   (float) - Upper left x ordinate.
@p_srid (integer) - geometry srid
```

RESULT

```    geometry (geometry) - Input coordinates converted to 5 point polygon.
```

EXAMPLE

```    USE GISDB
GO
SELECT [\$(owner)].[STMakeEnvelope](0,0,1,1,null) as mbr;
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - August 2019 - Original TSQL Coding.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 54. CREATE/STMakeLine (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakeLine -- Creates a two point linestring.
```

SYNOPSIS

```    Function STMakeLine (
@p_start_point geometry,
@p_end_point   geometry,
@p_round_xy    int = 10,
@p_round_zm    int = 10
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STMakeLine](geometry::Point(0,0,0),geometry::Point(10,10,28355)) as line;
LINE
45
```

DESCRIPTION

```    Function creates a two point linestring from supplied start and end points.
The output linestring's XY ordinates are rounded to the supplied @p_round_xy value.
The output linestring's ZM ordinates are rounded to the supplied @p_round_zm value.
```

NOTES

```    If @p_start_point or @p_end_point are null, a null result is returned.
If @p_start_point or @p_end_point have different SRIDS, a null result is returned.
```

INPUTS

```    @p_start_point  (geometry) - Not null start point.
@p_end_point    (geometry) - Not null end point.
@p_round_xy     (int)      - XY ordinate precision.
@p_round_zm     (int)      - ZM ordinate precision.
```

RESULT

```    linestring      (geometry) - LineString from start point to end point.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 55. CREATE/STMakeLineFromMultiPoint (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakeLineFromMultiPoint -- Creates a linestring from supplied MULTIPOINT geometry.
```

SYNOPSIS

```    Function STMakeLineFromMultiPoint (
@p_points geometry
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].STMakeLineFromMultiPoint(geometry::STGeomFromText('MULTIPOINT(((0,0),(10,10))',28355) as line;
LINE
LINESTRING(0 0,10 10)
```

DESCRIPTION

```    Function creates linestring from supplied points in @p_points (MultiPoint).
```

NOTES

```    Only MultiPoint input geometry supported.
```

INPUTS

```    @p_points (geometry) - Not null MultiPoint geometry.
```

RESULT

```    linestring -- LineString from provided multipoint geometry.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 56. CREATE/STMakeLineWKTFromGeographyCollection (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakeLineWKTFromGeographyCollection -- Creates a linestring from supplied GeometryCollection geography.
```

SYNOPSIS

```    Function [STMakeLineWKTFromGeographyCollection] (
@p_points geography
)
Returns varchar(max)
```

USAGE

```    SELECT [\$(owner)].[STMakeLineWKTFromGeographyCollection](geography::STGeomFromText('GEOMETRYCOLLECTION(POINT(0 0),POINT(10 10))',28355) as line;
LINE
LINESTRING(0 0,10 10)
```

DESCRIPTION

```    Function creates linestring from supplied Points in @p_points (GeometryCollection).
```

NOTES

```    Only Point geometries within @p_points supported (LineString etc sub-geometries ignored).
```

INPUTS

```    @p_points (geography) - Not null GeometryCollection containing Point geography types.
```

RESULT

```    linestring (varchar) - LineString from provided GeometryCollection's Point geometries.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 57. CREATE/STMakeLineWKTFromGeometryCollection (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakeLineWKTFromGeometryCollection -- Creates a linestring from supplied GeometryCollection geometry.
```

SYNOPSIS

```    Function [STMakeLineWKTFromGeometryCollection] (
@p_points geometry
)
Returns varchar(max)
```

USAGE

```    SELECT [\$(owner)].[STMakeLineWKTFromGeometryCollection](geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(0 0),POINT(10 10))',28355) as line;
LINE
LINESTRING(0 0,10 10)
```

DESCRIPTION

```    Function creates linestring from supplied Points in @p_points (GeometryCollection).
```

NOTES

```    Only Point geometries within @p_points supported (LineString etc sub-geometries ignored).
```

INPUTS

```    @p_points (geometry) - Not null GeometryCollection containing Point geometry types.
```

RESULT

```    linestring (varchar) - LineString from provided GeometryCollection's Point geometries.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 58. CREATE/STMakeLineXY (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakeLineXY -- Creates a two point 2D XY linestring.
```

SYNOPSIS

```    Function STMakeLine (
@p_start_x  float,
@p_start_y  float,
@p_end_x    float,
@p_end_y    float,
@p_srid     int = 0,
@p_round_xy int = 10
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].STMakeLineXY(0,0,10,10,28355).STAsText() as line;
LINE
LINESTRING (0 0,10 10)
```

DESCRIPTION

```    Function creates a two point 2D linestring from supplied start and end XY values.
The output linestring's XY ordinates are rounded to the supplied @p_round_xy value.
```

NOTES

```    If any of @p_start_x/y or @p_end_x/y are null, a null result is returned.
```

INPUTS

```    @p_start_x  (float) - Start X ordinate
@p_start_y  (float) - Start Y ordinate
@p_end_x    (float) - End X ordinate
@p_end_y    (float) - End Y ordinate
@p_srid       (int) - Srid
@p_round_xy   (int) - rounding value for ordinates.
```

RESULT

```    linestring (geometry) - LineString from start point to end point.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 59. CREATE/STMakePoint (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakePoint -- Creates a point from input ordinates.
```

SYNOPSIS

```    Function STMakePoint (
@p_x    float,
@p_y    float,
@p_z    float,
@p_m    float,
@p_srid int = 0
)
Returns geometry
```

USAGE

```    SELECT f.point.AsTextZM() as point, f.point.STSrid as srid
FROM (SELECT [\$(owner)].[STMakePoint](1,2,3,4,28355) as point) as f;
point    srid
POINT (1 2 3 4)    28355
```

DESCRIPTION

```    Function creates a point from the supplied ordinates.
```

NOTES

```    Extends supplied non-OGC static function Point.
Alternative to extended WKT constructor:
```

INPUTS

```    @p_x        (float) - Not null X Ordinate.
@p_y        (float) - Not null Y Ordinate.
@p_z        (float) - Not null Z Ordinate.
@p_m        (float) - Not null M Ordinate.
```

RESULT

```    Point                      - Geometry point.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 60. EDIT/STRemoveDuplicatePoints (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STRemoveDuplicatePoints -- Function which removes duplicate points from a linestring.
```

SYNOPSIS

```    Function [\$(owner)].[STRemoveDuplicatePoints] (
@p_geometry geometry,
@p_round_xy int = 3,
@p_round_z  int = 2,
@p_round_m  int = 2
)
Returns geometry
```

USAGE

```    select 'Text XY Ordinates' as test, [\$(owner)].[STRemoveDuplicatePoints](geometry::STGeomFromText('LINESTRING(0 0,1 1,1 1,2 2)',0),3,null,null).AsTextZM() as fixedLine
union all
select 'Test XY ordinates of XYZ' as test, [\$(owner)].[STRemoveDuplicatePoints](geometry::STGeomFromText('LINESTRING(0 0 0,1 1 1,1 1 1.1,2 2 2)',0),3,null,null).AsTextZM() as fixedLine
union all
select 'Test XYZ ordinates of XYZ with Z digits that maintains Z' as test, [\$(owner)].[STRemoveDuplicatePoints](geometry::STGeomFromText('LINESTRING(0 0 0,1 1 1,1 1 1.1,2 2 2)',0),3,2,null).AsTextZM() as fixedLine
union all
select 'Test XYZ ordinates of XYZ with Z digits that does not maintain Z' as test, [\$(owner)].[STRemoveDuplicatePoints](geometry::STGeomFromText('LINESTRING(0 0 0,1 1 1,1 1 1.1,2 2 2)',0),3,0,null).AsTextZM() as fixedLine
union all
select 'Test XY ordinates of XYZM with precision that ignores Z and M differences' as test, [\$(owner)].[STRemoveDuplicatePoints](geometry::STGeomFromText('LINESTRING(0 0 0 0,1 1 1 1,1 1 1.1 1.1,2 2 2.1 2.1)',0),3,null,null).AsTextZM() as fixedLine
union all
select 'Test XYZ ordinates of XYZM with Z digits that maintains Z but ignores M differences' as test, [\$(owner)].[STRemoveDuplicatePoints](geometry::STGeomFromText('LINESTRING(0 0 0 0,1 1 1 1,1 1 1.1 1.1,2 2 2.1 2.1)',0),3,2,null).AsTextZM() as fixedLine
union all
select 'Test XYM ordinates of XYZM with M digits that maintains M but ignores Z differences' as test, [\$(owner)].[STRemoveDuplicatePoints](geometry::STGeomFromText('LINESTRING(0 0 0 0,1 1 1 1,1 1 1.1 1.1,2 2 2.1 2.1)',0),3,null,1).AsTextZM() as fixedLine
union all
select 'Test XYMZ ordinates of XYZM with Z/M digits that maintains Z/M' as test, [\$(owner)].[STRemoveDuplicatePoints](geometry::STGeomFromText('LINESTRING(0 0 0 0,1 1 1 1,1 1 1.1 1.1,2 2 2.1 2.1)',0),3,1,1).AsTextZM() as fixedLine
go

test      fixedLine
----------------------------------------------------------------------------------- -------------------------------------------------------
Text XY Ordinates                                                                   LINESTRING (0 0, 1 1, 2 2)
Test XY ordinates of XYZ                                                            LINESTRING (0 0 0, 1 1 1, 2 2 2)
Test XYZ ordinates of XYZ with Z digits that maintains Z                            LINESTRING (0 0 0, 1 1 1, 1 1 1.1, 2 2 2)
Test XYZ ordinates of XYZ with Z digits that does not maintain Z                    LINESTRING (0 0 0, 1 1 1, 2 2 2)
Test XY ordinates of XYZM with precision that ignores Z and M differences           LINESTRING (0 0 0 0, 1 1 1 1, 2 2 2.1 2.1)
Test XYZ ordinates of XYZM with Z digits that maintains Z but ignores M differences LINESTRING (0 0 0 0, 1 1 1 1, 1 1 1.1 1.1, 2 2 2.1 2.1)
Test XYM ordinates of XYZM with M digits that maintains M but ignores Z differences LINESTRING (0 0 0 0, 1 1 1 1, 1 1 1.1 1.1, 2 2 2.1 2.1)
Test XYMZ ordinates of XYZM with Z/M digits that maintains Z/M                      LINESTRING (0 0 0 0, 1 1 1 1, 1 1 1.1 1.1, 2 2 2.1 2.1)
```

DESCRIPTION

```    Function that removes any duplicate vertices in the supplied linestring.
When comparing two adjacent points, the ordinates are compared to the supplied @p_round_xy and @p_round_z/m digits of precision.
All ordinates are included in the comparison not just XY unless @p_round_z or @p_round_m is null.
```

INPUTS

```    @p_geometry (geometry) - Supplied geometry of type linestring.
@p_round_xy (int)      - Decimal degrees of precision to which calculated XY ordinates are compared.
@p_round_z  (int)      - Decimal degrees of precision to which calculated Z ordinates are compared.
@p_round_m  (int)      - Decimal degrees of precision to which calculated M ordinates are compared.
```

RESULT

```    fixed line  (geometry) - Corrected input geometry.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 61. EDITOR/STAddZ (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STAddZ -- Function that adds elevation (Z) ordinates to the supplied linestring.
```

SYNOPSIS

```    Function STAddZ (
@p_linestring geometry,
@p_start_z    float,
@p_end_z      float,
@p_round_xy   int = 3,
@p_round_zm   int = 2
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STAddZ] (
geometry::STGeomFromText('LINESTRING(0 0,0.5 0.5,1 1)',0),
1.232,
1.523,
3,
2
).AsTextZM() as LineWithZ;
MeasureLine
LINESTRING (0 0 1.232,0.5 0.5  1 1 1.523)
```

DESCRIPTION

```    Function that add elevation values to the ordinates of the supplied p_linestring.
Supports LineString, CircularString, CompoundCurve geometries
If geometry already has elevation/Z values is returned unchanged.
Start Point is assigned @p_start_Z and End Point is assigned @p_end_Z.
If @p_start_Z or @p_end_Z is null, the original linestring is returned.
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_z           (float) - New Start Z Value.
@p_end_z             (float) - New End Z 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

```    linestring with Z (geometry) - Input linestring with measures applied.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 62. EDITOR/STAppend (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STAppend -- Appends second linestring to end of first linestring.
```

SYNOPSIS

```    Function [\$(lrsowner)].[STAppend] (
@p_linestring1 geometry,
@p_linestring2 geometry,
@p_round_xy    int   = 3,
@p_round_zm    int   = 2
)
Returns geometry
```

DESCRIPTION

```    Given two linestrings, this function appends the second to the first, though their direction could change (eg Start/Start Point relationship).
While one can use STUnion() for this, STUnion does not handle geometries with Z and M.
This function specifically supports Z and M for LRS type operations.
The input geometry objects must conform to the following:
1. Normally, both linestrings should be either a CircularString or a Linestring.
2. A CompoundCurve is supported only when it is supplied as @p_linestring1.
3. A MultiLineString can only be supplied via @p_linestring1.
4. A LineString can only be supplied via @p_linestring2 when @p_linestring1 is a MultiLineString.
5. Two supplied MultiLineString geometries are not currently supported.
6. Both linestring parameters must have the same SRID
7. Both linestring parameters must have the same Coordinate Dimension ie XYZ=XYZ, XYM=XYM or XYZM=XYZM.
8. Both linestring parameters geometries must be valid.
It is up to the caller to ensure these conditions are met.
For optimal performance one should append a smaller second geometry (@p_linestring2) to a larger @p_linestring1.
```

NOTES

```    Supports Linestrings with CircularString elements.
```

INPUTS

```    @p_linestring1 (geometry) - Linestring geometry possibly with elevation (Z) and measures (M).
@p_linestring2 (geometry) - Linestring geometry possibly with elevation (Z) and measures (M).
@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

```    appended line  (geometry) - New line with second appended to first
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2018 - Original Coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 63. EDITOR/STConvertToLineString (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STConvertToLineString -- Creates a (multi)linestring from supplied GeometryCollection geometry's (multi)linestring objects.
```

SYNOPSIS

```    Function STConvertToLineString (
@p_geometry_collection geometry
)
Returns geometry
```

USAGE

```    SELECT [dbo].STConvertToLineString(geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0,1 1),MULTILINESTRING((3 3,8 8),(9 9,20 20)))'',0) as line;
LINE
LINESTRING(0 0,10 10)
```

DESCRIPTION

```    Function creates linestring from linestrings within supplied @p_geometry_collection (GeometryCollection).
```

NOTES

```    Only LineString/MultiLinestring/CircularString/CompoundString geometries within @p_geometry_collection supported
```

INPUTS

```    @p_geometry_collection (geometry) - Not null GeometryCollection containing some linear geometry types.
```

RESULT

```    (Multi)LineString -- (Multi)LineString from provided GeometryCollection's linear geometries.
```

EXAMPLE

```    With gc As (
select geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(0 0), POLYGON((0 0,10 0,10 10,0 10,0 0)) )',0) as geom
union all
select geometry::STGeomFromText('GEOMETRYCOLLECTION (MULTILINESTRING((0 0,20 0),(20 20,0 20,0 0)))', 0) as geom
union all
select geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0,20 0,20 20,0 20,0 0), POINT(0 0), POLYGON((0 0,10 0,10 10,0 10,0 0)) )',0) as geom
union all
select geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(0 0,20 0,20 20,0 20,0 0),LINESTRING(1 1,2 2),LINESTRING(3 3,19 19),POINT(0 0), POLYGON((0 0,10 0,10 10,0 10,0 0)) )',0) as geom
union all
select geometry::STGeomFromText('GEOMETRYCOLLECTION(MULTILINESTRING((0 0,20 0),(20 20,0 20,0 0)), LINESTRING(1 1,2 2),LINESTRING(3 3,19 19),POINT(0 0),POLYGON((0 0,10 0,10 10,0 10,0 0)) )',0) as geom
)
select [dbo].[STConvertToLineString](geom).STAsText() as geomWKT
from gc as a
go

geomWKT
---------------------------------------------------------------------------
LINESTRING EMPTY
MULTILINESTRING ((0 0, 20 0), (20 20, 0 20, 0 0))
LINESTRING (0 0, 20 0, 20 20, 0 20, 0 0)
MULTILINESTRING ((0 0, 20 0, 20 20, 0 20, 0 0), (1 1, 2 2), (3 3, 19 19))
MULTILINESTRING ((0 0, 20 0), (20 20, 0 20, 0 0), (1 1, 2 2), (3 3, 19 19))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - August  2018 - Original TSQL Coding for SQL Server.
Simon Greener - October 2019 - Fixed bug with single MultiLineString in Geometry Collection.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 64. EDITOR/STDelete (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STDelete -- Function which deletes one or more coordinates from the supplied geometry.
```

SYNOPSIS

```    Function STDelete (
@p_geometry   geometry,
@p_point_list varchar(max)
@p_round_xy   int = 3,
@p_round_zm   int = 2
)
Returns geometry
```

USAGE

```    SELECT STDelete(STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2,120 394.23)',0),'2',3,2).STAsText() as deleteGeom;
# deleteGeom
'LINESTRING(0.1 0.2,120 394.23)'
```

DESCRIPTION

```    Function that removes one or more coordinates from the supplied geometry.
The function does not process POINT or GEOMETRYCOLLECTION geometries.
The list of points to be deleted is supplied as a comma separated string of point numbers.
The point numbers are from 1 to the total number of points in a WKT representation of the object.
Point numbers do not refer to specific points within a specific sub-geometry eg point number 1 in the 2nd interiorRing in a polygon object.
```

INPUTS

```    @p_geometry     (geometry) - supplied geometry of any type.
@p_point_line    (varchar) - Comma separated list of point numbers from 1 to the total number in a geometry's WKT representation.
@p_round_xy          (int) - Rounding value for XY ordinates.
@p_round_zm          (int) - Rounding value for ZM ordinates.
```

RESULT

```    modified geom   (geometry) - With referenced points deleted.
```

NOTES

```    May throw error message STGeomFromText error if point deletion invalidates the geometry.
```

TODO

```    Support for CircularString and CompundCurve objects.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original Coding for MySQL.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 65. EDITOR/STDeleteN (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STDeleteN -- Function which deletes referenced coordinate from the supplied geometry.
```

SYNOPSIS

```    Function STDeleteN (
@p_geometry geometry,
@p_position int,
@p_round_xy int = 3,
@p_round_zm int = 2
(
Returns geometry
```

USAGE

```    SELECT STDeleteN(STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2,120 394.23)',0),2,3,2).STAsText() as deleteGeom;
# deleteGeom
'LINESTRING(0.1 0.2,120 394.23)'
```

DESCRIPTION

```    Function that removes a single, nominated, coordinates from the supplied geometry.
The function does not process POINT or GEOMETRYCOLLECTION geometries.
The point to be deleted is supplied as a single integer.
The point number can be supplied as -1 (last number), or 1 to the total number of points in a WKT representation of the object.
A point number does not refer to a specific point within a specific sub-geometry eg point number 1 in the 2nd interiorRing in a polygon object.
```

INPUTS

```    @p_geometry   (geometry) - supplied geometry of any type.
@p_position   (int) - Valid point number in geometry.
@p_round_xy   (int) - Rounding value for XY ordinates.
@p_round_zm   (int) - Rounding value for ZM ordinates.
```

RESULT

```    modified geom (geometry) - With referenced point deleted.
```

NOTES

```    May throw error message STGeomFromText error if point deletion invalidates the geometry.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original Coding for MySQL.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 66. EDITOR/STDensify (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STDensify -- Implements a basic geometry densification algorithm.
```

SYNOPSIS

```    Function [\$(owner)].[STDensify](
@p_geometry geometry,
@p_distance Float,
@p_round_xy int = 10,
@p_round_zm int = 10
)
Returns geometry
```

DESCRIPTION

```    This function add vertices to an existing vertex-to-vertex described (m)linestring or (m)polygon sdo_geometry.
New vertices are added in such a way as to maintain existing vertices, that is, no existing vertices are removed.
Densification occurs on a single vertex-to-vertex segment basis.
If segment length is < p_distance no vertices are added.
The implementation does not guarantee that the added vertices will be exactly p_distance apart; mostly they will be < @p_distance..
The implementation honours 3D and 4D shapes and averages these dimension values for the new vertices.
The function does not support compound objects or objects with circles, or described by arcs.
Any non (m)polygon/(m)linestring shape is simply returned as it is.
```

ARGUMENTS

```    @p_geometry (geometry) - (M)Linestring or (m) polygon.
@p_distance    (Float) - The desired optimal distance between added vertices.
@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 ZM ordinates are compared.
```

RESULT

```    geometry (T_GEOMETRY) -- Densified geometry.
```

EXAMPLE

```    -- Densify 2D line into 4 segments
with data as (
select geometry::STGeomFromText('LINESTRING(0 0,10 10)',0) as geom
)
select [dbo].[STDensify](a.geom,a.geom.STLength()/4.0,3,2).AsTextZM() as dGeom
from data as a;

dGeom
LINESTRING (0 0, 2.5 2.5, 5 5, 7.5 7.5, 10 10)

-- Distance between all vertices is < 4.0
select [dbo].[STDensify](geometry::STGeomFromText('LINESTRING (5 5, 5 7, 7 7, 7 5, 5 5)',0),4.0,3,2).AsTextZM() as dGeom;

dGeom
LINESTRING (5 5, 5 7, 7 7, 7 5, 5 5)

-- Simple Straight line.
select [\$(owner)].[STDensify] (geometry::STGeomFromText('LINESTRING(100 100,900 900.0)',0),125.0,3,2).AsTextZM() as dGeom;

DGeom
LINESTRING (100 100, 188.889 188.889, 277.778 277.778, 366.667 366.667, 455.556 455.556, 544.444 544.444, 633.333 633.333, 722.222 722.222, 811.111 811.111, 900 900)

-- LineString with Z
select [dbo].[STDensify] (geometry::STGeomFromText('LINESTRING(100 100 1.0,900 900.0 9.0)',0),125.0,3,2).AsTextZM() as dGeom;

dGeom
LINESTRING (100 100 1, 180 180 1.8, 260 260 2.6, 340 340 3.4, 420 420 4.2, 500 500 5, 580 580 5.8, 660 660 6.6, 740 740 7.4, 820 820 8.2, 900 900 9)

-- LineStrings with ZM
select [dbo].[STDensify] (geometry::STGeomFromText('LINESTRING(100.0 100.0 -4.56 0.99, 110.0 110.0 -6.73 1.1)',0),2.5,3,2).AsTextZM() as dGeom;

dGeom
LINESTRING (100 100 -4.56 0.99, 101.667 101.667 -4.92 1.01, 103.333 103.333 -5.28 1.03, 105 105 -5.64 1.04, 106.667 106.667 -6.01 1.06, 108.333 108.333 -6.37 1.08, 110 110 -6.73 1.1)

GEOM
LINESTRING (1100.765 964.286, 1107.568 939.343, 1114.371 914.399, 1121.173 889.456, 1127.976 864.513, 1134.779 839.569, 1141.582 814.626, 1148.384 789.683, 1155.187 764.739, 1161.99 739.796, 1139.881 723.923,
1117.772 708.05, 1095.663 692.177, 1073.554 676.304, 1051.446 660.431, 1029.337 644.558, 1007.228 628.685, 985.119 612.812, 963.01 596.939, 941.032 610.675, 919.054 624.411, 897.076 638.148,
875.098 651.884, 853.12 665.62, 831.142 679.356, 809.164 693.093, 787.186 706.829, 765.208 720.565, 743.23 734.301, 721.252 748.038, 699.274 761.774, 677.296 775.51, 653.203 787.131, 629.11
798.753, 605.017 810.374, 580.924 821.995, 556.831 833.617, 532.738 845.238, 508.645 856.859, 484.552 868.481, 460.459 880.102, 434.63 869.26, 408.801 858.418, 382.972 847.576, 357.143
836.735, 331.314 825.893, 305.485 815.051, 279.656 804.209, 253.827 793.367, 242.53 770.043, 231.232 746.72, 219.935 723.396, 208.637 700.073, 197.34 676.749, 186.042 653.426, 174.745
630.102, 185.459 603.571, 196.173 577.041, 206.888 550.51, 217.602 523.98, 228.316 497.449, 253.543 500.85, 278.77 504.252, 303.996 507.653, 329.223 511.054, 354.45 514.456, 379.677
517.857, 404.903 521.258, 430.13 524.66, 455.357 528.061, 479.244 520.64, 503.131 513.219, 527.017 505.798, 550.904 498.377, 574.791 490.956, 598.678 483.534, 622.565 476.113, 646.452 468.692,
670.338 461.271, 694.225 453.85, 718.112 446.429, 717.262 420.493, 716.411 394.558, 715.561 368.622, 714.711 342.687, 713.86 316.751, 713.01 290.816, 698.66 270.089, 684.311 249.362,
669.962 228.635, 655.612 207.908, 641.263 187.181, 626.913 166.454, 612.564 145.727, 598.214 125, 573.271 120.181, 548.327 115.363, 523.384 110.544, 498.441 105.726, 473.497 100.907,
448.554 96.089, 423.611 91.27, 398.667 86.452, 373.724 81.633, 351.858 94.935, 329.992 108.236, 308.126 121.538, 286.261 134.84, 264.395 148.142, 242.529 161.443, 220.663 174.745,
198.797 188.047, 176.931 201.348, 155.065 214.65, 133.2 227.952, 111.334 241.254, 89.468 254.555, 67.602 267.857)

-- MultiLineString.
select [dbo].[STDensify](geometry::STGeomFromText('MULTILINESTRING ((0 0, 5 5, 10 10),(20 20, 25 25, 30 30))',0),2.1,3,2).AsTextZM() as dGeom;

dGeom
MULTILINESTRING ((0 0, 1.25 1.25, 2.5 2.5, 3.75 3.75, 5 5, 6.25 6.25, 7.5 7.5, 8.75 8.75, 10 10), (20 20, 21.25 21.25, 22.5 22.5, 23.75 23.75, 25 25, 26.25 26.25, 27.5 27.5, 28.75 28.75, 30 30))

-- Polygon
select [dbo].[STDensify](
geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0),
4.0,
3,2
).AsTextZM() as dGeom;

dGeom
POLYGON ((0 0, 4 0, 8 0, 12 0, 16 0, 20 0, 20 4, 20 8, 20 12, 20 16, 20 20, 16 20, 12 20, 8 20, 4 20, 0 20, 0 16, 0 12, 0 8, 0 4, 0 0), (10 10, 10 11, 11 11, 11 10, 10 10), (5 5, 5 7, 7 7, 7 5, 5 5))

-- MultiPolygon
select [dbo].[STDensify](
geometry::STGeomFromText('MULTIPOLYGON(((100 100,110 100,110 110,100 110,100 100)),((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5)))',0),
4.0,
3,2
).AsTextZM() as dGeom;

dGeom
MULTIPOLYGON (((100 100, 103.333 100, 106.667 100, 110 100, 110 103.333, 110 106.667, 110 110, 106.667 110, 103.333 110, 100 110, 100 106.667, 100 103.333, 100 100)),
((0 0, 4 0, 8 0, 12 0, 16 0, 20 0, 20 4, 20 8, 20 12, 20 16, 20 20, 16 20, 12 20, 8 20, 4 20, 0 20, 0 16, 0 12, 0 8, 0 4, 0 0), (10 10, 10 11, 11 11, 11 10, 10 10), (5 5, 5 7, 7 7, 7 5, 5 5)))
```

NOTES

```    Only supports stroked (m)linestrings and (m)polygon rings.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - June  2006 - Original coding in Oracle.
Simon Greener - April 2019 - Port to SQL Server Spatial
```

```    (c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 67. EDITOR/STExtend (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STExtend -- Function which extends the supplied linestring required distance at its start/end or both.
```

SYNOPSIS

```    Function STExtend (
@p_linestring    geometry,
@p_extend_length float,
@p_end           int,
@p_keep          int,
@p_round_xy      int = 3,
@p_round_zm      int = 2
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STExtend](geometry::ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),5.0,'START',1,2,1).AsTextZM() as extendedGeom;
# extendedGeom
'LINESTRING(-4.9 30.2,-3.6 31.5)'
```

DESCRIPTION

```    Function that extends the supplied linestring at either its start or end (p_end), in the direction of a line formed by the
first and second vertices (if START) or last and second last vertices (if END). @p_end value of BOTH means line is extended at both ends.
If @p_keep is set to 1, the start or end vertex is kept and a new vertex added at the extended length from the start/end.
If @p_keep is 0, the actual first or last vertex is moved.
The computed ordinates of the new geometry are rounded to @p_round_xy/@p_round_zm number of decimal digits of precision.
```

NOTES

```    MultiLinestrings and CircularString linestrings are not supported.
Assumes planar projection eg UTM.
```

INPUTS

```    @p_linestring    (geometry) - Supplied geometry of type LINESTRING only.
@p_extend_length (float)    - Length to extend linestring in SRID units.
@p_end           (varchar5) - START means extend line at the start; END means extend at the end and BOTH means extend at both START and END of line.
@p_keep          (int)      - Keep existing first/last vertex and add new (1) vertices, or move (0) existing start/end vertex.
@p_round_xy      (int)      - Round XY ordinates to supplied decimal digits of precision.
@p_round_zm      (int)      - Round ZM ordinates to supplied decimal digits of precision.
```

RESULT

```    linestring       (geometry) - Input geometry extended as instructed.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 68. EDITOR/STGeogRound (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STGeogRound -- Function which rounds the Long/Lat ordinates of the supplied geography.
```

SYNOPSIS

```    Function [\$(owner)].[STGeogRound] (
@p_geometry geography,
@p_round_lat  int = 8,
@p_round_long int = 8,
@p_round_z    int = 2,
@p_round_m    int = 2
)
Returns geography
```

DESCRIPTION

```    The result of many geoprocessing operations in any spatial type can be geometries
with ordinates (X, Y etc) that have far more decimal digits of precision than the initial geometry.

Additionally, some input GIS formats, such as shapefiles (which has no associated precision model),
when loaded, can show far more decimal digits of precision in the created ordinates misrepresenting
the actual accuracy of the data.

STGeogRound takes a geography object and some specifications of the precision of any X, Y, Z or M ordinates,
applies those specifications to the geography and returns the corrected geometry.

The @p_round_ll/@p_round_zm values are decimal digits of precision, which are used in TSQL's ROUND function
to round each ordinate value.
```

NOTES

```    Is wrapper over [STRound]
```

PARAMETERS

```    @p_geometry (geometry) - supplied geometry of any type.
@p_round_lat     (int) - Decimal degrees of precision to which Lat ordinate is rounded.
@p_round_long    (int) - Decimal degrees of precision to which Long ordinate is rounded.
@p_round_z       (int) - Decimal degrees of precision to which Z ordinate is rounded.
@p_round_m       (int) - Decimal degrees of precision to which M ordinate is rounded.
```

RESULT

```    geometry -- Input geometry moved by supplied X and Y ordinate deltas.
```

EXAMPLE

```    -- Geography
SELECT [\$(owner)].[STGeogRound](
geography::STGeomFromText('LINESTRING(141.29384764892390 -43.93834736282 234.82756,
141.93488793487934 -44.02323872332 235.26384)',
4326),
7,7
1,1
)
).AsTextZM() as rGeog

rGeom
LINESTRING (141.2938476 -43.9383474 234.8, 141.9348879 -44.0232387 235.3)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - November 2019 - Original Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 69. EDITOR/STInsertN (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STInsertN -- Function which inserts new coordinate (p_point) at position @p_position in the supplied geometry.
```

SYNOPSIS

```    Function STInsertN (
@p_geometry geometry,
@p_point    geometry,
@p_position int,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
```

USAGE

```    SELECT STInsertN(
STGeomFromText ('LINESTRING(0 0,2 2)',0),
STPointFromText('POINT(1 1)',0),
2,
1,1).AsTextZM() as newGeom;
# newGeom
'LINESTRING(0 0,1 1,2 2)'
```

DESCRIPTION

```    Function that inserts the coordinate @p_point into position @p_position in the supplied geometry.
@p_position Values:
1. null -> defaults to 1;
2. -1   -> maximum number of points ie STNumPoints(p_geometry)
3. Greater than STNumPoints(p_geometry) -> maximum number of points ie STNumPoints(p_geometry)
The inserted coordinate's ordinates are rounded to @p_round_xy number of decimal digits of precision.
```

NOTES

```    This version is for SQL Server 2008 only.
```

INPUTS

```    @p_geometry (geometry) - Supplied geometry of any type.
@p_point    (geometry) - Insert coordinate
@p_position (int)      - Coordinate position in @p_geometry: between 1 and STNumPoints.
@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 -- Input geometry with coordinate inserted.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original Coding for MySQL.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 70. EDITOR/STInsertN (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STInsertN -- Function which inserts new coordinate (p_point) at position @p_position in the supplied geometry.
```

SYNOPSIS

```    Function STInsertN (
@p_geometry geometry,
@p_point    geometry,
@p_position int,
@p_round_xy int,
@p_round_zm int
)
Returns geometry
```

DESCRIPTION

```    Function that inserts the coordinate @p_point into position @p_position in the supplied geometry.
@p_position Values:
1. null -> defaults to 1;
2. -1   -> maximum number of points ie STNumPoints(p_geometry)
3. Greater than STNumPoints(p_geometry) -> maximum number of points ie STNumPoints(p_geometry)
The inserted coordinate's ordinates are rounded to @p_round_xy number of decimal digits of precision.
```

NOTES

```    This version is for SQL Server versions from 2012 onwards.
```

INPUTS

```    @p_geometry (geometry) - Supplied geometry of any type.
@p_point    (geometry) - Insert coordinate
@p_position (int)      - Coordinate position in @p_geometry: between 1 and STNumPoints.
@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 -- Input geometry with coordinate inserted.
```

EXAMPLE

```    SELECT STInsertN(
STGeomFromText ('LINESTRING(0 0,2 2)',0),
STPointFromText('POINT(1 1)',0),
2,
1,1 ).AsTextZM() as newGeom;
# updatedGeom
'LINESTRING(0 0,1 1,2 2)'
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original Coding for MySQL.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 71. EDITOR/STIsCollinear (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STIsCollinear -- Function that checks if a linestring's points all lie on straight line.
```

SYNOPSIS

```    Function [\$(owner)].[STIsCollinear] (
@p_linestring          geometry,
@p_collinear_threshold float = -1
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STIsCollinear] (
geometry::STGeomFromText('LINESTRING(0 0,0.5 0.5,1 1)',0),
0.5
) as is_collinear;

is_collinear
---------------------------------------------
1
```

DESCRIPTION

```    Function that checks if a line is straight defined by points that are all collinear.
Threshold is applied to deflection angle between a pair of segments.
If deflection angle < threshold then the two linestring pairs are considered to be
collinar (ie delfection angle = 0.)
```

INPUTS

```    @p_linestring       (geometry) - Supplied Linestring geometry.
@p_collinear_threshold (float) - Deflection tolerance between a pair of segments.
```

RESULT

```    boolean                  (bit) - 1 (true) if collinear, 0 otherwise.
```

NOTES

```    Uses [location].[STFindDeflectionAngle]
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - October 2019 - Original TSQL Coding
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 72. EDITOR/STIsCompound (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STIsCompound -- Tests if supplied geometry has circularString elements.
```

SYNOPSIS

```    Function STIsCompound (
@p_geometry geometry
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STIsCompound](geometry::STGeomFromText('
CURVEPOLYGON(
COMPOUNDCURVE(
CIRCULARSTRING(0 5,5 0,10 5,5 10,0 5)
)
)',0)) as isCompound
GO
isCompound
1
```

DESCRIPTION

```    Function that checks if the supplied geometry is described by CircularString elements.
```

NOTES

```    While will run in SQL Server 2008 (always 0), is aimed at 2012 onwards.
```

INPUTS

```    @p_geometry (geometry) - Any geometry object.
```

RESULT

```    true/false  (bit)      - 1 (True) or 0 (False)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2012 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 73. EDITOR/STIsGeo (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STIsGeo -- Tests if referenced schema/table/column is of type geography (1) or geometry (0).
```

SYNOPSIS

```    Function STIsGeo (
@schema_name nvarchar(128),
@object_name nvarchar(128),
@column_name nvarchar(128)
)
Returns integer
```

USAGE

```    SELECT [\$(owner)].[STIsGeo]('dbo','Table','Column') as isGeo;
GO
isGeo
geography
```

DESCRIPTION

```    Function that checks the SQL Server metadata and returns whether the referenced column contains geographic or geometry objects.
```

NOTES

```    While will run in SQL Server 2008 (always geometry), is aimed at 2012 onwards.
```

INPUTS

```    @schema_name nvarchar(128) - Schema name
@object_name nvarchar(128) - Table/View name
@column_name nvarchar(128) - Column name
```

RESULT

```    result (integer)           - no geography/geometry (-1), geography (1), geometry (0)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2012 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 74. EDITOR/STMakeCircularLine (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STMakeCircularLine -- Creates a three point Circular linestring.
```

SYNOPSIS

```    Function STMakeCircularLine (
@p_start_point geometry,
@p_mid_point   geometry,
@p_end_point   geometry,
@p_round_xy    int = 8,
@p_round_z     int = 8,
@p_round_m     int = 8
)
Returns geometry
```

USAGE

```    SELECT \$(owner).STMakeCircularLine(
geometry::Point(0,0,28355),
geometry::Point(5,5,28355),
geometry::Point(10,10,28355),
1,1,null
) as cline;
LINE
45
```

DESCRIPTION

```    Function creates a three point Circular linestring from supplied start, mid and end points.
The output linestring's XY ordinates are rounded to the supplied p_round_xy value.
The output linestring's Z ordinates are rounded to the supplied p_round_z value.
The output linestring's M ordinates are rounded to the supplied p_round_m value.
```

NOTES

```    If @p_start_point, or @p_mid_point, or @p_end_point are null, a null result is returned.
If @p_start_point, or @p_mid_point, or @p_end_point have different SRIDS, a null result is returned.
If points are collinear (XY only), null is returned.
Z is returned if ALL points have Z ordinates and all values are equal.
M is returned if ALL points have M ordinates.
```

INPUTS

```    @p_start_point (geometry) - Not null start point.
@p_mid_point   (geometry) - Not null start point.
@p_end_point   (geometry) - Not null end point.
@p_round_xy         (int) - XY ordinate precision.
@p_round_z          (int) - Z ordinate precision.
@p_round_m          (int) - M ordinate precision.
```

RESULT

```    circular linestring (geometry) - Circular LineString from start point, through mid point, to end point.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - March 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 75. EDITOR/STMakeLineFromGeometryCollection (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakeLineFromGeometryCollection -- Creates a linestring from supplied GeometryCollection geometry.
```

SYNOPSIS

```    Function STMakeLineFromGeometryCollection (
@p_geometry_collection geometry
)
Returns geometry
```

DESCRIPTION

```    Function creates linestring from supplied Points or LineStrings, CircularString in @p_geometry_collection.
```

INPUTS

```    @p_geometry_collection (geometry) - Not null GeometryCollection containing valid geometry types.
```

RESULT

```    linestring -- LineString from provided GeometryCollection's geometries.
```

EXAMPLE

```    SELECT [dbo].STMakeLineFromGeometryCollection(geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(0,0),POINT(10,10))',28355) as line;

line
LINESTRING(0 0,10 10)

SELECT [dbo].[STMakeLineFromGeometryCollection] (
geometry::STGeomFromText('GEOMETRYCOLLECTION(LINESTRING(1 1, 3 5),LINESTRING(3 10,6 -5))',0),
3,
2
).STAsText() as line;

line
MULTILINESTRING ((1 1, 3 5), (3 10, 6 -5))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2018 - Original TSQL Coding for SQL Server.
Simon Greener - October  2019 - Modified to support linestring and circularstring elements.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 76. EDITOR/STMulti (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMulti -- Function that return @p_geometry as a MULTI* geometry
```

SYNOPSIS

```    Function STMulti (
@p_geometry geometry
)
Returns geometry
```

DESCRIPTION

```    Returns @p_geometry as a MULTI* geometry.
For example: POINT -> MULTIPOINT
If @p_geometry is already a MULTI*, it is returned unchanged.
For example: MULTIPOINT -> MULTIPOINT
MULTI returned only contains 1 geometry so STNumGeometries will return 1.
```

NOTES

```    CircularString input geometry is returned as a GEOMETRYCOLLECTION as it
cannot be returned as a MUTLICURVE as it is not instantiable. Additionally,
one cannot construct a MULTILINESTRING containing it MUTLILINESTRING(CIRCULARSTRING(....)).
```

INPUTS

```    @p_geometry (geometry) - supplied geometry of any type.
```

RESULT

```    geometry    (geometry) - Input geometry converted to MULTI geometry.
```

EXAMPLE

```    USE GISDB
GO

SELECT f.mGeom.AsTextZM() as mGeom, f.mGeom.STNumGeometries() as numGeometries
FROM (SELECT [dbo].[STMulti](geometry::STPointFromText('POINT(0 0)',0)) as mGeom
UNION ALL
SELECT [dbo].[STMulti](geometry::STGeomFromText ('POLYGON ((0 0,10 0,10 10,0 10,0 0))',0)) as mgeom
UNION ALL
SELECT [dbo].[STMulti](geometry::STGeomFromText ('LINESTRING(0 0,10 10,20 20)',0)) as mgeom
UNION ALL
SELECT [dbo].[STMulti](geometry::STGeomFromText('CIRCULARSTRING(9.962 -0.872,10.1 0,9.962 0.872)',0)) as mgeom
) as f;
GO

mGeom                                                                  numGeometries
MULTIPOINT ((0 0))                                                                 1
MULTIPOLYGON (((0 0, 10 0, 10 10, 0 10, 0 0)))                                     1
MULTILINESTRING ((0 0, 10 10, 20 20))                                              1
GEOMETRYCOLLECTION (CIRCULARSTRING (9.962 -0.872, 10.1 0, 9.962 0.872))            1
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - August 2019 - Original TSQL Coding.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 77. EDITOR/STReduce (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STReduce -- Function which extends the first or last vertex connected segment of a linestring.
```

SYNOPSIS

```    Function STReduce (
@p_linestring       geometry,
@p_reduction_length float,
@p_end              varchar(5),
@p_round_xy         int = 3,
@p_round_zm         int = 2
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STReduce](geometry::ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),5.0,'START',2,1).AsTextZM() as reducedGeom;
# reducedGeom
'LINESTRING(-4.9 30.2,-3.6 31.5)'
```

DESCRIPTION

```    Function that shortens the supplied linestring at either its start or end (p_end) the required length.
The function can apply the reduction at either ends (or both).
The function will remove existing vertices as the linestring is shortened.
If the linestring reduces to nothing, an error will be thrown by STGeomFromText.
Any computed ordinates of the new geometry are rounded to @p_round_xy/@p_round_zm number of decimal digits of precision.
```

NOTES

```    MultiLinestrings and CircularString linestrings are not supported.
Assumes planar projection eg UTM.
```

INPUTS

```    @p_linestring        (geometry) - Supplied geometry of type LINESTRING only.
@p_reduction_length  (float)    - Length to reduce linestring by in SRID units.
@p_end               (varchar5) - START means reduce line at its start; END means extend at its end and BOTH means extend at both START and END of line.
@p_round_xy          (int)      - Round XY ordinates to supplied decimal digits of precision.
@p_round_zm          (int)      - Round ZM ordinates to supplied decimal digits of precision.
```

RESULT

```    linestring           (geometry) - Input geometry extended as instructed.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 78. EDITOR/STRemoveCollinearPoints (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STRemoveCollinearPoints -- Function that removes unnecessary points that lie on straight line between adjacent points.
```

SYNOPSIS

```    Function [\$(owner)].[STRemoveCollinearPoints] (
@p_linestring          geometry,
@p_collinear_threshold float = -1,
@p_round_xy            int = 3,
@p_round_z             int = 2,
@p_round_m             int = 2
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STRemoveCollinearPoints] (
geometry::STGeomFromText('LINESTRING(0 0,0.5 0.5,1 1)',0),
0.5,
3,
2,2
).AsTextZM() as LineWithCollinearPointsRemoved;
LineWithCollinearPointsRemoved
---------------------------------------------
LINESTRING (0 0,1 1)
```

DESCRIPTION

```    Function that checks each triple of adjacent points and removes middle one if collinear with start and end point.
Collinearity is determined by computing the deflection angle (degrees) at the mid point and comparing it to the @p_collinear_threshold parameter value (degrees).
If the collinear threshold value is < the deflection angle, the mid point is removed.
The updated coordinate's XY ordinates are rounded to p_round_xy number of decimal digits of precision.
The updated coordinate's Z ordinate is rounded to @p_round_Z number of decimal digits of precision.
The updated coordinate's M ordinate is rounded to @p_round_M number of decimal digits of precision.
```

INPUTS

```    @p_linestring       (geometry) - Supplied Linestring geometry.
@p_round_xy              (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_z               (int) - Decimal degrees of precision to which any calculated Z ordinates is rounded.
@p_round_m               (int) - Decimal degrees of precision to which any calculated M ordinates is rounded.
```

RESULT

```    Modified linestring (geometry) - Input linestring with any collinear points removed.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2018 - Original TSQL Coding for SQL Spatial.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 79. EDITOR/STRemoveSpikes (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STRemoveSpikes -- Function that removes spikes and unnecessary points that lie on straight line between adjacent points.
```

SYNOPSIS

```    Function [\$(owner)].[STRemoveSpikes] (
@p_linestring      geometry,
@p_angle_threshold float = 0.5,
@p_round_xy        int   = 3,
@p_round_z         int   = 2,
@p_round_m         int   = 2
)
Returns geometry
```

USAGE

```    with data as (
select geometry::STGeomFromText('LINESTRING(0 0,1 0,2 0,2.1 0,2.2 10.0,2.3 0,3 0)',0) as geom
)
select 'Angle Threshold' as test, [dbo].[STRemoveSpikes](a.geom,3.0,3,2,2).AsTextZM() as result from data as a
union all
select 'Original Line'   as test, c.geom.AsTextZM() from data as c
go
test            result
Angle Threshold LINESTRING (0 0, 1 0, 2 0, 2.1 0, 2.3 0, 3 0)
Original Line   LINESTRING (0 0, 1 0, 2 0, 2.1 0, 2.2 10, 2.3 0, 3 0)
```

DESCRIPTION

```    Calls STRemoveSpikesByWKT.
```

NOTES

```    The function only processes linestrings and multilinestrings not CircularStrings or CompoundCurves.
```

INPUTS

```    @p_linestring       (geometry) - Supplied Linestring geometry.
@p_angle_threshold     (float) - Smallest subtended angle allowed. If mid point angle is < @p_angle_threshold the mid-point is removed.
@p_round_xy              (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_z               (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.
@p_round_m               (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.
```

RESULT

```    Modified linestring (geometry) - Input linestring with any spikes removed
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2018 - Original TSQL Coding for SQL Spatial.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 80. EDITOR/STRemoveSpikesAsGeog (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STRemoveSpikesAsGeog -- Function that removes spikes and unnecessary points that lie on straight line between adjacent points.
```

SYNOPSIS

```    Function [\$(owner)].[STRemoveSpikesAsGeog] (
@p_linestring      geography,
@p_angle_threshold float = 0.5,
@p_round_xy        int   = 3,
@p_round_z         int   = 2,
@p_round_m         int   = 2
)
Returns geometry
```

USAGE

```    With WKT as (
select 'LINESTRING(148.60735 -35.157845 356 0, 148.60724 -35.157917 87 87, 148.60733 -35.157997 9 96, 148.60724 -35.157917 5 101)' as lWkt
)
select 'L' as id, [dbo].[STRemoveSpikesAsGeog] (geography::STGeomFromText(a.lWkt,4283),10.0,8,2,2).AsTextZM() as sLine from wkt as a
union all
select 'O' as id, geography::STGeomFromText(lWkt,4283).AsTextZM() as line from wkt as a
GO
id sLine
L  LINESTRING (148.60735 -35.157845 356 0, 148.60724 -35.157917 87 87)
O  LINESTRING (148.60735 -35.157845 356 0, 148.60724 -35.157917 87 87, 148.60733 -35.157997 9 96, 148.60724 -35.157917 5 101)
```

DESCRIPTION

```    Calls STRemoveSpikesByWKT.
```

NOTES

```    The function only processes linestrings and multilinestrings not CircularStrings or CompoundCurves.
```

INPUTS

```    @p_linestring       (geography) - Supplied Linestring geography.
@p_angle_threshold      (float) - Smallest subtended angle allowed. If mid point angle is < @p_angle_threshold the mid-point is removed.
@p_round_xy               (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_z                (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.
@p_round_m                (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.
```

RESULT

```    Modified linestring (geography) - Input linestring with any spikes removed
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2018 - Original TSQL Coding for SQL Spatial.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 81. EDITOR/STRemoveSpikesByWKT (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STRemoveSpikesByWKT -- Function that removes spikes and unnecessary points that lie on straight line between adjacent points.
```

SYNOPSIS

```    Function [\$(owner)].[STRemoveSpikesByWKT] (
@p_linestring          varchar(max),
@p_srid                int,
@p_angle_threshold     float = 0.5,
@p_round_xy            int   = 3,
@p_round_z             int   = 1,
@p_round_m             int   = 3
)
Returns varchar(max)
```

USAGE

```    with data as (
select 'LINESTRING(0 0,1 0,2 0,2.1 0,2.2 10.0,2.3 0,3 0)' as geomWKT
)
select 'Angle Threshold' as test, [\$(owner)].[STRemoveSpikesByWKT](a.geomWKT,0,3.0,3,2,2) as rsWKT from data as a
union all
select 'Original Line'   as test, c.geom from data as c
go

test              rsWKT
--------------- ---------------------------------------------
Angle Threshold LINESTRING (0 0, 1 0, 2 0, 2.1 0, 2.3 0, 3 0)
```

DESCRIPTION

```    This function tests rolling 3 point line segments to detect spikes.
Spikes are detected where the subtended angle between the segments is less
than a user provided threshold value (degrees), @p_angle_threshold, the mid point is removed.
The updated coordinate's XY ordinates are rounded to p_round_xy number of decimal digits of precision.
The updated coordinate's Z ordinate is rounded to p_round_Z number of decimal digits of precision.
The updated coordinate's M ordinate is rounded to p_round_M number of decimal digits of precision.
```

NOTES

```    The function only processes linestrings not CircularStrings or CompoundCurves.
The function additionally removes any duplicate points from the input linestring cf STRemoveDuplicatePoints
```

INPUTS

```    @p_linestring   (varchar max) - Supplied Linestring geography.
@p_srid                 (int) - Srid of @p_linetsring WKT
@p_angle_threshold    (float) - Smallest subtended angle allowed. If mid point angle is < @p_angle_threshold the mid-point is removed.
@p_round_xy             (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_z              (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.
@p_round_m              (int) - Decimal degrees of precision to which calculated ZM ordinates are rounded.
```

RESULT

```    Linestring WKT  (varchar max) - Input linestring with any spikes removed
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - February 2018 - Original TSQL Coding for SQL Spatial.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 82. EDITOR/STReverse (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STReverse -- Function which reverses the vertices of a linestring and parts/vertices of multilinestring.
```

SYNOPSIS

```    Function STReverse (
@p_geometry geometry,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
```

SYNOPSIS

```    select id, action, geom
from (select 'Before' as action, id, geom.STAsText() as geom
from (select 1 as id, geometry::STGeomFromText('LINESTRING(0 0, 10 0)',0) as geom
union all
select 2 as id, geometry::STGeomFromText('MULTILINESTRING((1 1,2 2), (3 3, 4 4))',0) as geom
union all
select 3 as id, geometry::STGeomFromText('MULTIPOINT((1 1),(2 2),(3 3),(4 4))',0) as geom
) as data
union all
select 'After' as action, id, STReverse(geom).STAsText() as geom
from (select 1 as id, geometry::STGeomFromText('LINESTRING(0 0, 10 0)',0) as geom
union all
select 2 as id, geometry::STGeomFromText('MULTILINESTRING((1 1,2 2), (3 3, 4 4))',0) as geom
union all
select 3 as id, geometry::STGeomFromText('MULTIPOINT((1 1),(2 2),(3 3),(4 4))',0) as geom
) as data
) as f
order by id, action desc;

id action geom
---- ------ --------------------------------------
1 Before LINESTRING(0 0,10 0)
1 After  LINESTRING(10 0,0 0)
2 Before MULTILINESTRING((1 1,2 2),(3 3,4 4))
2 After  MULTILINESTRING((4 4,3 3),(2 2,1 1))
3 Before MULTIPOINT((1 1),(2 2),(3 3),(4 4))
3 After  MULTIPOINT((4 4),(3 3),(2 2),(1 1))
```

DESCRIPTION

```    Function that reverses the coordinates of the following:
1. MultiPoint
2. LineString
3. CircularString (2012)
4. CompoundCurve  (2012)
5. MultiLineString
If the geometry is a MultiLineString, the parts, and then their vertices are reversed.
```

INPUTS

```    @p_geometry   (geometry) - Supplied geometry of supported type.
@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

```    reversed geom (geometry) - Input geometry with parts and vertices reversed.
```

NOTES

```    Function STGeomFromText if reversal processing invalidates the geometry.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 83. EDITOR/STRound (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STRound -- Function which rounds the ordinates of the supplied geometry.
```

SYNOPSIS

```    Function [dbo].[STRound] (
@p_geometry geometry,
@p_round_x int = 3,
@p_round_y int = 3,
@p_round_z int = 2,
@p_round_m int = 2
)
Returns geometry
```

DESCRIPTION

```    The result of many geoprocessing operations in any spatial type can be geometries
with ordinates (X, Y etc) that have far more decimal digits of precision than the initial geometry.

Additionally, some input GIS formats, such as shapefiles (which has no associated precision model),
when loaded, can show far more decimal digits of precision in the created ordinates misrepresenting
the actual accuracy of the data.

STRound takes a geometry object and some specifications of the precision of any X, Y, Z or M ordinates,
applies those specifications to the geometry and returns the corrected geometry.

The @p_round_* values are expressed as decimal digits of precision, which are used in TSQL's ROUND function
to round each ordinate value.
```

PARAMETERS

```    @p_geometry (geometry) - supplied geometry of any type.
@p_round_x       (int) - Decimal degrees of precision to which X ordinate is rounded.
@p_round_y       (int) - Decimal degrees of precision to which Y ordinate is rounded.
@p_round_z       (int) - Decimal degrees of precision to which Z ordinate is rounded.
@p_round_m       (int) - Decimal degrees of precision to which M ordinate is rounded.
```

RESULT

```    geometry -- Input geometry moved by supplied X and Y ordinate deltas.
```

EXAMPLE

```    -- Geometry
-- Point
SELECT [dbo].[STRound](geometry::STPointFromText('POINT(0.345 0.282)',0),1,1,0,0).STAsText() as RoundGeom
UNION ALL
-- MultiPoint
SELECT [dbo].[STRound](geometry::STGeomFromText('MULTIPOINT((100.12223 100.345456),(388.839 499.40400))',0),3,3,1,1).STAsText() as RoundGeom
UNION ALL
-- Linestring
SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),2,2,1,1).STAsText() as RoundGeom
UNION ALL
-- LinestringZ
SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2 0.312,1.4 45.2 1.5738)',0),2,2,1,1).AsTextZM() as RoundGeom
UNION ALL
-- Polygon
SELECT [dbo].[STRound](geometry::STGeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))',0),2,2,1,1).STAsText() as RoundGeom
UNION ALL
-- MultiPolygon
SELECT [dbo].[STRound](
geometry::STGeomFromText('MULTIPOLYGON (((160 400, 200.00000000000088 400.00000000000045, 200.00000000000088 480.00000000000017, 160 480, 160 400)), ((100 200, 180.00000000000119 300.0000000000008, 100 300, 100 200)))',0),
2,2,1,1).STAsText() as RoundGeom

RoundGeom
POINT (0.3 0.3)
MULTIPOINT ((100.122 100.345), (388.839 499.404))
LINESTRING (0.1 0.2, 1.4 45.2)
LINESTRING (0.1 0.2 0.3, 1.4 45.2 1.6)
POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
MULTIPOLYGON (((160 400, 200 400, 200 480, 160 480, 160 400)), ((100 200, 180 300, 100 300, 100 200)))

-- Geography
SELECT [dbo].STToGeography(
[dbo].[STRound](
[dbo].STToGeometry(
geography::STGeomFromText('LINESTRING(141.29384764892390 -43.93834736282 234.82756,
141.93488793487934 -44.02323872332 235.26384)',
4326),
4326
),
6,7,
3,1
),
4326
).AsTextZM() as rGeom;

rGeom
LINESTRING (141.293848 -43.9383474 234.828, 141.934888 -44.0232387 235.264)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original Coding for SQL Server.
Simon Greener - November 2019 - Modified to allow for 4 ordinate precision parameters (support Geography)
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 84. EDITOR/STSetZ (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STSetZ -- Function that adds or updates (replaces) Z value of supplied geometry.
```

SYNOPSIS

```    Function STSetZ (
@p_geometry geometry,
@p_z        float,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
```

USAGE

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

DESCRIPTION

```    Function that adds/updates Z ordinate of the supplied @p_geometry.
If single point, it is updated and returned.
If Linestring/MultiLinestring, all Z ordinates are set to the supplied value.
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 geometry.
@p_z         (float)    - Z 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 Z (geometry) - Input point geometry with Z set to @p_Z.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 85. EDITOR/STSwapOrdinates (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STSwapOrdinates -- Allows for swapping ordinate pairs in a geometry.
```

SYNOPSIS

```    Function STSwapOrdinates (
@p_geometry geometry,
@p_swap     varchar(2) = 'XY'
)
Returns geometry
```

DESCRIPTION

```    Sometimes the ordinates of a geometry can be swapped such as latitude for X
and Longitude for Y when it should be reversed. This function allows for the
swapping of pairs of ordinates controlled by the @p_swap parameter.
Also supports Z and M ordinate values.
```

INPUTS

```    @p_geometry (geometry)  - Supplied geometry.
@p_swap     (varchar 2) - The ordinate pair to swap: XY, XZ, XM, YZ, YM or ZM
```

RESULT

```    altered geom (geometry) - Changed Geometry;
```

EXAMPLE

```    SELECT [\$(owner)].[STSwapOrdinates] (
geometry::STPointFromText('POINT(143.282374 20.293874)',4326),
'XY'
).AsTextZM() as correctedOrdinates;
correctedOrdinates
POINT (20.293874 143.282374)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - August 2009 - Original TSQL Coding for SQL Spatial.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 86. EDITOR/STTo2D (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STTo2D -- Function that removes any Z or M ordinates from the @p_geometry
```

SYNOPSIS

```    Function [\$(owner)].[STTo2D] (
@p_geometry geometry
)
Returns geometry
```

USAGE

```    SELECT [\$(owner)].[STTo2D](
STGeomFromText ('POLYGON(0 0,10 0,10 10,0 10,0 0)',0)
).AsTextZM() as 2DGeom;
# 2DGeom
'POLYGON(1 1,10 0,10 10,0 10,1 1)'
```

DESCRIPTION

```    Function that removes all Z and M ordinates from the input @p_geometry.
```

INPUTS

```    @p_geometry (geometry) - supplied geometry of any type.
```

RESULT

```    geometry    (geometry) - 2D version of input geometry
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2019 - Original TSQL Coding for SQL Spatial.
```

```    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 87. EDITOR/STUpdate (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STUpdate -- Function that updates (replaces) all geometry points that are equal to the supplied point with the replacement point.
```

SYNOPSIS

```    Function STUpdate (
@p_geometry      geometry,
@p_point         geometry,
@p_replace_point geometry,
@p_round_xy      int = 3,
@p_round_zm      int = 2
)
Returns geometry
```

USAGE

```    SELECT STUpdate(
STGeomFromText ('POLYGON(0 0,10 0,10 10,0 10,0 0)',0),
STPointFromText('POINT(0 0)',0),
STPointFromText('POINT(1 1)',0),
1
).AsTextZM() as updatedGeom;
# updatedGeom
'POLYGON(1 1,10 0,10 10,0 10,1 1)'
```

DESCRIPTION

```    Function that updates all coordinates that equal @p_point with the supplied @p_replace_point.
The @p_round_xy value is used when comparing geometry point's XY ordinates to @p_point's.
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_geometry      (geometry) - supplied geometry of any type.
@p_point         (geometry) - Original coordinate to be replaced.
@p_replace_point (geometry) - Replacement coordinate
@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) - Input geometry with one or more coordinate replaced.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 88. EDITOR/STUpdateN (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STUpdateN -- Function which updates the coordinate at position @p_position in the supplied geometry.
```

SYNOPSIS

```    Function STUpdateN (
@p_geometry   geometry,
@p_replace_pt float,
@p_position   int,
@p_round_xy   int = 3,
@p_round_zm   int = 2
)
Returns geometry
```

USAGE

```    SELECT STUpdateN(
STGeomFromText ('LINESTRING(0 0,2 2)',0),
STPointFromText('POINT(1 1)',0),
2,
2, 1 ).STAsText() as updatedGeom;
# updatedGeom
'LINESTRING(0 0,1 1)'
```

DESCRIPTION

```    Function that updates coordinate identified by @p_position with the new point (@p_replace_point) in the supplied geometry.
@p_position Values:
1. null -> defaults to 1;
2. -1   -> maximum number of points ie STNumPoints(p_geometry)
3. Greater than STNumPoints(p_geometry) -> maximum number of points ie STNumPoints(p_geometry)
The updated coordinate's ordinates are rounded to @p_round_xy number of decimal digits of precision.
```

INPUTS

```    @p_geometry      (geometry) - supplied geometry of any type.
@p_replace_point (geoemtry) - Replacement coordinate
@p_position      (int)      - Coordinate position in @p_geometry: between 1 and STNumPoints.
@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

```    updated geom     (geometry) - Input geometry with coordinate replaced.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2017 - Original Coding for MySQL.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 89. GEOPROCESSING/ST_Average [ Functions ]

[ Top ] [ Functions ]

NAME

```    ST_Average -- Averages ordinates of 2 Points
```

SYNOPSIS

```    CREATE FUNCTION dbo.ST_Average(
@p_first_point  geometry,
@p_second_point geometry
)
RETURNS boolean
```

ARGUMENTS

```    @p_first_point  (geometry) -- point
@p_second_point (geometry) -- point
```

RESULT

```    point (geometry - Average of two points
```

DESCRIPTION

```    This function takes two points and averages the ordinates.
If points have different ordinate dimensions, 2D point is returned.
```

EXAMPLE

```     select ST_AsText(spdba.ST_Average('POINT(-1 -1)'::geometry,'POINT(1 1)'::geometry)) as aPoint;
aPoint
POINT(0 0)
select ST_AsText(spdba.ST_Average('POINTZ(-1 -1 1)'::geometry,'POINTZ(1 1 2)'::geometry)) as aPoint;
aPoint
POINT(0 0 1.5)
select ST_AsText(spdba.ST_Average('POINTM(-1 -1 1)'::geometry,'POINTM(1 1 2)'::geometry)) as aPoint;
aPoint
POINT(0 0 1.5)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - April 2019, Original Coding
```

```    (c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 90. GEOPROCESSING/STCentroid [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCentroid -- Wrapper that creates centroid geometry for any multipoint, (multi)line or (multi)Polygon object.
```

SYNOPSIS

```    Function ST_Centroid (
@p_geometry             geometry,
@p_multi_Mode           int   = 2,
@p_area_x_start         int   = 0,
@p_area_x_ordinate_seed Float = 0,
@p_line_position_ratio  Float = 0.5,
@p_round_xy             int   = 3,
@p_round_zm             int   = 2
)
Returns geometry
```

DESCRIPTION

```    This function creates a single centroid by calling the Centroid_P, Centroid_L or Centroid_A functions
according to @p_geometry.STGeometryType().
```

INPUTS

```    @p_geometry          (geometry) - Geometry object.
@p_multi_mode             (int) - Maps to STCentroid_L/@p_multiLineStringMode or STCentroid_P/@p_multiPolygonMode.
@p_area_x_start           (int) - Maps to STCentroid_A/@p_area_x_start.
@p_area_x_ordinate_seed (Float) - Maps to STCentroid_A/@p_seed_x.
@p_line_position_ratio  (Float) - Maps to STCentroid_L/@p_position_as_ratio.
@p_round_xy               (int) - Ordinate rounding precision for XY ordinates.
@p_round_zm               (int) - Ordinate rounding precision for ZM ordinates.
```

RESULT

```    centroid(s) (geometry) - Centroid of input object.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 91. GEOPROCESSING/STCentroid_A (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCentroid_A - Generates centroid for a polygon or multiPolygon geometry object.
```

SYNOPSIS

```    Function STCentroid_A (
@p_geometry          geometry,
@p_multiPolygonMode  int   = 2,
@p_area_x_start      int   = 0,
@p_seed_x            Float = NULL,
@p_round_xy          int   = 3,
@p_round_zm          int   = 2
)
Returns geometry
```

DESCRIPTION

```    This function creates a centroid for a Polygon or MultiPolygon geometry object.
The standard geometry.STCentroid() function does not guarantee that the centroid it generates falls inside a polygon.
This function ensures that the centroid of any arbitrary polygon falls within the polygon.
IF @p_geometry is MultiPolygon four modes are available that control the creation of the centroid(s).
0 = All      (A multiPoint object is created one for each part)
1 = First    (First Polygon @p_geometry.STGeometryN(1) is used).
2 = largest  (Largest Polygon part of MultiPolygon is used).
3 = smallest (Smallest Polygon part of MultiPolygon is used).
The function works by computing a X ordinate for which a Y ordinate will be found that falls within the polygon.
The X ordinate position can be controlled by the @p_area_x_start parameter as follows:
0 = Average (Use average of X ordinates of Area's vertices for starting X centroid calculation).
1 = MBR     (Compute and use the Centre X ordinate of the MBR of the geometry object).
2 = User    (Use the user supplied starting @p_seed_X).
```

INPUTS

```    @p_geometry    (geometry) - Point or Multipoint geometry object.
@p_multiPolygonMode (int) - Mode controlling centroid(s) generation when @p_geometry is MultiLineString.
@p_area_x_start     (int) - How to determine the starting X ordinate.
@p_seed_x         (Float) - If @p_area_x_start = 2 then user must supply a value.
@p_round_xy         (int) - Ordinate rounding precision for XY ordinates.
@p_round_zm         (int) - Ordinate rounding precision for ZM ordinates.
RETURN
centroid(s)    (geometry) - One or more centroid depending on input.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - July 2008 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 92. GEOPROCESSING/STCentroid_L (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCentroid_L - Generates centroid for a Linestring or multiLinestring geometry object.
```

SYNOPSIS

```    Function STCentroid_L (
@p_geometry            geometry,
@p_multiLineStringMode int   = 2,
@p_position_as_ratio   Float = 0.5,
@p_round_xy            int = 3,
@p_round_zm            int = 2
)
Returns geometry
```

DESCRIPTION

```    This function creates a centroid for a Linestring or MultiLineString geometry object.
IF @p_geometry is MultiLineString four modes are available that control the creation of the centroid(s).
0 = All      (A multiPoint object is created one for each part)
1 = First    (First linestring @p_geometry.STGeometryN(1) is used).
2 = largest  (Longest linestring part of MultiLineString is used).
3 = smallest (Shortest linestring part of MultiLineString is used).
The position of the centroid for a single linestring is computed at exactly 1/2 way along its length (0.5).
The position can be varied by supplying a @p_position_as_ratio value other than 0.5.
```

INPUTS

```    @p_geometry       (geometry) - LineString or MultiLineString geometry object.
@p_multiLineStringMode (int) - Mode controlling centroid(s) generation when @p_geometry is MultiLineString/GeometryCollection.
@p_position_as_ratio (float) - Position along linestring where centroid location is computed.
@p_round_xy            (int) - Ordinate rounding precision for XY ordinates.
@p_round_zm            (int) - Ordinate rounding precision for ZM ordinates.
RETURN
centroid(s)       (geometry) - One or more centroid depending on input.
TOBEDONE
Support for MultiLineStrings within GeometryCollections.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - July 2008   - Original coding.
Simon Greener - August 2018 - Support for GeometryCollection
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 93. GEOPROCESSING/STCentroid_P (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCentroid_P - Generates centroid for a point (itself) or multipoint.
```

SYNOPSIS

```    Function STCentroid_P (
@p_geometry geometry,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
```

DESCRIPTION

```    This function creates centroid of multipoint via averaging of ordinates.
```

INPUTS

```    @p_geometry (geometry) - Point or Multipoint geometry object.
@p_round_xy (int)      - Ordinate rounding precision for XY ordinates.
@p_round_zm (int)      - Ordinate rounding precision for ZM ordinates.
RETURN
centroid (geometry) - The centroid.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - July 2008 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 94. GEOPROCESSING/STCheckRadii (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCheckRadii -- Checks if radius of any three points in a linestring are less than the desired amount.
```

SYNOPSIS

```    Function [\$(owner)].[STCheckRadii] (
@p_geom       geometry,
@p_precision  int
)
Returns geometry
```

DESCRIPTION

```    Function that checks vertices in a linestring/multilinestring to see if
the circular arc they describe have radius less than the provided amount.
Each set of three vertices (which could be overlapping) that fail the test
are written to a single MultiPoint object. If no circular arcs in the linestring
describe a circle with radius less than the required amount a NULL geometry is returned.
If another other than a (Multi)linestring is provided it is returned as is.
```

NOTES

```    Supports Linestrings with CircularString elements.
Supplied geometry must not be geographic: function only guaranteed for projected data.
Does not honour dimensions over 2.
```

INPUTS

```    @p_linestring (geometry) - Projected Linestring geometry
@p_min_radius    (Float) - A not null value that describes the minimum radiue of any arc within the linestring.
@p_precision       (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
```

RESULT

```    points        (geometry) - For (m)linestrings the point triplets have a radius less than required;
For CircularString itself if radius less than required, otherwise null
```

EXAMPLE

```    SELECT [\$(owner)].[STCheckRadii](geometry::STGeomFromText('LINESTRING(0.0 0.0,10.0 0.0,10.0 10.0)',0),10.0,3).STAsText() as failingPoints;

failingPoints
MULTILINESTRING ((0 0, 10 0, 10 10))

SELECT [\$(owner)].[STCheckRadii](geometry::STGeomFromText('MULTILINESTRING((0.0 0.0,10.0 0.0,10.0 10.0),(20.0 0.0,30.0 0.0,30.0 10.0,35 15))',0), 15.0,3).STAsText()  as failingPoints;

failingPoints
MULTILINESTRING ((0 0, 10 0, 10 10), (20 0, 30 0, 30 10), (30 0, 30 10, 35 15))

with data as (
select geometry::STGeomFromText('CIRCULARSTRING(0.0 0.0,10.0 10.0,20.0 0.0)',0) as circulararc
)
circulararc,
gs.IntValue,
3).STAsText() as failingArc
from data as a
cross apply
[\$(owner)].[generate_series](5,15,5) as gs;

5                 10        NULL
10                10        NULL
15                10        CIRCULARSTRING (0 0, 10 10, 20 0)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2018 - Original Coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 95. GEOPROCESSING/STDump (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STDump -- Extracts the subelements that describe the input geometry.
```

SYNOPSIS

```    Function [\$(owner)].[STDump](
@p_geometry geometry
)
Returns @geometries TABLE
(
id   integer,
geom geometry
)
```

DESCRIPTION

```    This function allows a user to extract the subelements of the supplied geometry.
Some geometries have no subelements: eg Point, LineString
The subelements of a geometry change depending on the geometry type:
1. A MultiPoint only has one or more Point subelements;
2. A MultiLineString only more than one LineString subelements;
3. A Polygon has zero one or more inner rings and only one outer ring;
4. A MultiPolygon has zero one or more inner rings and one or more outer rings;
Some subelements can have subelements when they are Compound:
1. A CircularCurve can be described by one or more three point circular arcs.
If subelements exist they are extracted and returned.
```

NOTES

```    This version is for versions of SQL Server from 2012 onwards.

This version is a wrapper over STExtract to mirror the PostGIS function.
```

INPUTS

```    @p_geometry (geometry) - (Multi)geometry or geometryCollection object.
```

EXAMPLE

```    -- MultiPoint
SELECT d.id, d.geom.AsTextZM() as geom
FROM [\$(owner)].[STDump] (geometry::STGeomFromText('MULTIPOINT((0 0),(10 0),(10 10),(0 10),(0 0))',0)) as d;
GO

id   geom
1    POINT (0 0)
2    POINT (10 0)
3    POINT (10 10)
4    POINT (0 10)
5    POINT (0 0)
-- Polygon with hole
SELECT d.id, d.geom.AsTextZM() as geom
FROM [\$(owner)].[STDump] (geometry::STGeomFromText('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 9 1,9 9,1 9,1 1))',0)) as d;
GO

id   geom
1    POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
2    POLYGON ((1 1, 9 1, 9 9, 1 9, 1 1))

-- 2 Polygons, one with hole.
SELECT d.id, d.geom.AsTextZM() as geom
FROM [\$(owner)].[STDump] (geometry::STGeomFromText('MULTIPOLYGON(((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 9 1,9 9,1 9,1 1)),((100 100,110 100,110 110, 100 110,100 100)))',0)) as d;
GO

id   geom
1    POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
2    POLYGON ((1 1, 9 1, 9 9, 1 9, 1 1))
3    POLYGON ((100 100, 110 100, 110 110, 100 110, 100 100))

SELECT d.id, d.geom.AsTextZM() as geom
FROM [\$(owner)].[STDump] (geometry::STGeomFromText('GEOMETRYCOLLECTION (POLYGON ((100 200, 180 300, 100 300, 100 200)), LINESTRING (100 200, 100 75), POINT (100 0))',0)) as d;
GO

id   geom
1    POLYGON ((100 200, 180 300, 100 300, 100 200))
2    LINESTRING (100 200, 100 75)
3    POINT (100 0)

-- MultiLineString
SELECT d.id, d.geom.AsTextZM() as geom
FROM [\$(owner)].[STDump] (geometry::STGeomFromText('MULTILINESTRING((0 0,5 5,10 10,11 11,12 12),(100 100,200 200))',0)) as d;
GO

id   geom
1    LINESTRING (0 0, 5 5, 10 10, 11 11, 12 12)
2    LINESTRING (100 100, 200 200)

-- geometryCollection
SELECT d.id, d.geom.AsTextZM() as geom
FROM [\$(owner)].[STDump] (geometry::STGeomFromText('GEOMETRYCOLLECTION (COMPOUNDCURVE(CIRCULARSTRING (3 6.32, 0 7, -3 6.32),(-3 6.32, 0 0, 3 6.32)))',0)) as d;
GO

id   geom
1    CIRCULARSTRING (3 6.32, 0 7, -3 6.32)
2    LINESTRING (-3 6.32, 0 0)
3    LINESTRING (0 0, 3 6.32)
```

RESULT

```    Array of subelements:
id  - Unique identifier ordered from first element to las.
geom - Geometry representation of element.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - July 2019
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 96. GEOPROCESSING/STDumpPoints (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STDumpPoints - Dumps all vertices of supplied geometry object to ordered array.
```

SYNOPSIS

```   Function [\$(owner)].[STDumpPoints] (
@p_geometry  geometry
)
Returns @Points Table (
uid   int,
pid   int,
mid   int,
rid   int,
x     float,
y     float,
z     float,
m     float,
point geometry
)
```

EXAMPLE

```    -- Points from multipolygon
SELECT e.[uid], e.[mid], e.[rid], e.[pid], e.[x], e.[y], e.[z], e.[m]
FROM [\$(owner)].[STDumpPoints] (
geometry::STGeomFromText(
'MULTIPOLYGON( ((200 200, 400 200, 400 400, 200 400, 200 200)),
((0 0, 100 0, 100 100, 0 100, 0 0),(40 40,60 40,60 60,40 60,40 40)) )',0)
) as e
GO
uid mid rid pid   x   y    z    m
1   1   1   1 200 200 NULL NULL
2   1   1   2 400 200 NULL NULL
3   1   1   3 400 400 NULL NULL
4   1   1   4 200 400 NULL NULL
5   1   1   5 200 200 NULL NULL
6   2   1   1   0   0 NULL NULL
7   2   1   2 100   0 NULL NULL
8   2   1   3 100 100 NULL NULL
9   2   1   4   0 100 NULL NULL
10   2   1   5   0   0 NULL NULL
11   2   2   1  40  40 NULL NULL
12   2   2   2  60  40 NULL NULL
13   2   2   3  60  60 NULL NULL
14   2   2   4  40  60 NULL NULL
15   2   2   5  40  40 NULL NULL
```

DESCRIPTION

```    This function extracts the fundamental points that describe a geometry object.
```

NOTES

```    This is a wrapper function over STVertices
```

INPUTS

```    @p_geometry (geometry) - Any non-point geometry object
```

RESULT

```    Table (Array) of Points :
uid (int)   - Unique Point identifier across whole geometry
pid (int)   - Point identifier with element/subelement (1 to Number of Points in element).
mid (int)   - Unique identifier that describes the geometry object's elements (eg linestring in MultiLineString).
rid (int)   - SubElement or Ring identifier.
x   (float) - Start Point X Ordinate
y   (float) - Start Point Y Ordinate
z   (float) - Start Point Z Ordinate
m   (float) - Start Point M Ordinate
point (geometry) - x,y,z,m as geometry
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2008 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 97. GEOPROCESSING/STDumpRings (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STDumpRings -- Dumps the rings of a CurvePolygon, Polygon or MultiPolygon
```

SYNOPSIS

```    Function [dbo].[STDumpRings] (
@p_geometry geometry
)
Returns @rings TABLE
(
gid  integer,
sid  integer,
geom geometry
)
```

DESCRIPTION

```    This function allows a user to extract all the rings of the supplied (multi)polygon.
This function is a wrapper over STExtract.
```

INPUTS

```    @p_geometry (geometry) - CurvePolygon, Polygon or MultiPolygon geometry object.
```

RESULT

```    Array of subelements:
gid  - Unique ring identifier starting at first and ending at last in order exist within (multi)polygon
rid  - Ring id within polygon element (id)
geom - Geometry representation of subelement.
```

NOTES

```    Depends on STExtract.
```

EXAMPLE

```    -- Polygon with one exterior ring and two interior rings
SELECT t.gid, t.rid, t.geom.STAsText() as geom
FROM [dbo].[STDumpRings](geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)) as t
GO

gid rid geom
1   1   POLYGON ((0 0, 20 0, 20 20, 0 20, 0 0))
1   2   POLYGON ((10 10, 10 11, 11 11, 11 10, 10 10))
1   3   POLYGON ((5 5, 5 7, 7 7, 7 5, 5 5))

-- Multi Polygon with 3 exterior rings, with one with 2 interior rings
select d.gid,d.rid,d.geom.STAsText() as geom
from [dbo].[STDumpRings](geometry::STGeomFromText(
'MULTIPOLYGON (((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5)),
((80 80, 100 80, 100 100, 80 100, 80 80)),
((110 110, 150 110, 150 150, 110 150, 110 110)))',0)) as d

GO
gid rid geom
1   1   POLYGON ((0 0, 20 0, 20 20, 0 20, 0 0))
1   2   POLYGON ((10 10, 10 11, 11 11, 11 10, 10 10))
1   3   POLYGON ((5 5, 5 7, 7 7, 7 5, 5 5))
2   1   POLYGON ((80 80, 100 80, 100 100, 80 100, 80 80))
3   1   POLYGON ((110 110, 150 110, 150 150, 110 150, 110 110))

-- Single Polygon with exterior ring only
select d.gid,d.rid,d.geom.STAsText() as geom
from [dbo].[STDumpRings](geometry::STGeomFromText(
'CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778)))',0)) as d
GO

gid rid geom
1   1   CURVEPOLYGON (COMPOUNDCURVE ((0 -23.43778, 0 23.43778), CIRCULARSTRING (0 23.43778, -45 23.43778, -90 23.43778), (-90 23.43778, -90 -23.43778), CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan  2013 - Original coding.
Simon Greener - Jan  2015 - Port to TSQL SQL Server
Simon Greener - July 2019 - Modfied to return only id and geom and no subelements.
Simon Greener - October 2019 - Modfied to use STExplode; return more geom/ring identifiers.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 98. GEOPROCESSING/STExplode (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STExplode -- STExplode is a wrapper function over STExtract with sub_element parameter set to 1
```

SYNOPSIS

```    Function [\$(owner)].[STExplode] (
@p_geometry geometry,
)
Returns @geometries TABLE
(
gid  integer,
sid  integer,
geom geometry
)
```

DESCRIPTION

```    This function calls STExtract with @p_sub_geom set to 2.
This ensures all possible elements and subelements of a geometry are extracted.
```

NOTES

```    This version is for versions of SQL Server from 2012 onwards.
```

INPUTS

```    @p_geometry (geometry) - Polygon or Multipolygon geometry object.
```

EXAMPLE

```    SELECT t.gid, t.sid, t.geom.STAsText() as geom
FROM [\$(owner)].[STExplode] (
GEOMETRY::STGeomFromText (
'CURVEPOLYGON(
COMPOUNDCURVE(
(0 -23.43778, 0 23.43778),
CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),
(-90 23.43778, -90 -23.43778),
CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778)
)
)',0)) as t;
GO

gid sid geom
--- --- -------------------------------------------
1   1 LINESTRING (0 -23.43778, 0 23.43778)
1   2 CIRCULARSTRING (0 23.43778, -45 23.43778, -90 23.43778)
1   3 LINESTRING (-90 23.43778, -90 -23.43778)
1   4 CIRCULARSTRING (-90 -23.43778, -45 -23.43778, 0 -23.43778)
```

RESULT

```    Array of subelements:
gid  - Geometry Identifier (for multigeomemtry objects, the individual high level geometry objects it describes).
sid  - Sub Element Identifier
geom - Geometry representation of subelement.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding.
Simon Greener - Jan 2015 - Port to TSQL SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 99. GEOPROCESSING/STExtract (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STExtract -- Extracts the subelements that describe the input geometry.
```

SYNOPSIS

```    Function [\$(owner)].[STExtract] (
@p_geometry geometry,
@p_sub_geom int = 0
)
Returns @geometries TABLE
(
gid  integer,
sid  integer,
geom geometry
)
```

DESCRIPTION

```    This function allows a user to extract the subelements of the supplied geometry.
Some geometries have no subelements: eg Point, LineString
The subelements of a geometry change depending on the geometry type:
1. A MultiPoint only has one or more Point subelements;
2. A MultiLineString only more than one LineString subelements;
3. A Polygon has zero one or more inner rings and only one outer ring;
4. A MultiPolygon has zero one or more inner rings and one or more outer rings;
Some subelements can have subelements when they are Compound:
1. A CircularCurve can be described by one or more three point circular arcs.
If @p_sub_geom is set to 1, any subelements of a subelement are extracted.
```

NOTES

```    This version is for SQL Server 2008 only.
```

INPUTS

```    @p_geometry (geometry) - Polygon or Multipolygon geometry object.
@p_sub_geom    (float) - Extract elements (individual circular arcs) of a compound subelement.
```

EXAMPLE

```    SELECT t.*
FROM [\$(owner)].[STExtract](geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0),0).AsTextZM() as t
GO
```

RESULT

```    Array of subelements:
gid  - Geometry Identifier (for multigeomemtry objects, the individual high level geometry objects it describes).
sid  - Sub Element Identifier
geom - Geometry representation of subelement.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding.
Simon Greener - Jan 2015 - Port to TSQL SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 100. GEOPROCESSING/STExtract (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STExtract -- Extracts the subelements that describe the input geometry.
```

SYNOPSIS

```    Function [\$(owner)].[STExtract] (
@p_geometry geometry,
@p_sub_geom int = 0
)
Returns @geometries TABLE
(
gid  integer,
sid  integer,
geom geometry
)
```

DESCRIPTION

```    This function allows a user to extract the subelements of the supplied geometry.
Some geometries have no subelements: eg Point, LineString
The subelements of a geometry change depending on the geometry type:
1. A MultiPoint only has one or more Point subelements;
2. A MultiLineString only more than one LineString subelements;
3. A Polygon has zero one or more inner rings and only one outer ring;
4. A MultiPolygon has zero one or more inner rings and one or more outer rings;
Some subelements can have subelements when they are Compound:
1. A CircularCurve can be described by one or more three point circular arcs.
If @p_sub_geom is set to 0, only single geometry elements of a multi geometry are extracted.
If @p_sub_geom is set to 1, any subelements (eg ring) of a geometry (eg polygon) are extracted.
If @p_sub_geom is set to 2, any subelements (CIRCULARSTRING) of a subelement (COMPOUNDCURVE) are extracted.
```

NOTES

```    This version is for versions of SQL Server from 2012 onwards.
```

INPUTS

```    @p_geometry (geometry) - Polygon or Multipolygon geometry object.
@p_sub_geom    (float) - Extract elements (individual circular arcs) of a compound subelement.
```

EXAMPLE

```    SELECT e.gid, sid, geom.AsTextZM() as egeom
FROM [\$(owner)].[STExtract] (
geometry::STGeomFromText('GEOMETRYCOLLECTION (POLYGON ((100 200, 180 300, 100 300, 100 200)), LINESTRING (100 200, 100 75), POINT (100 0))',0),0) as e;
GO
gid sid egeom
--- --- ----------------------------------------------
1   0 POLYGON ((100 200, 180 300, 100 300, 100 200))
2   0 LINESTRING (100 200, 100 75)
3   0 POINT (100 0)

SELECT e.gid, sid, geom.AsTextZM() as egeom
FROM [\$(owner)].[STExtract] (geometry::STGeomFromText('MULTILINESTRING((0 0,5 5,10 10,11 11,12 12),(100 100,200 200))',0),1) as e;
GO
gid sid egeom
--- --- ------------------------------------------
1   0 LINESTRING (0 0, 5 5, 10 10, 11 11, 12 12)
2   0 LINESTRING (100 100, 200 200)

SELECT e.gid, sid, geom.AsTextZM() as egeom
FROM [\$(owner)].[STExtract] (
geometry::STGeomFromText('GEOMETRYCOLLECTION (COMPOUNDCURVE(CIRCULARSTRING (3 6.32, 0 7, -3 6.32),(-3 6.32, 0 0, 3 6.32)))',0),
1
) as e;
GO
gid sid egeom
--- --- -------------------------------------
1   1 CIRCULARSTRING (3 6.32, 0 7, -3 6.32)
1   2 LINESTRING (-3 6.32, 0 0)
1   3 LINESTRING (0 0, 3 6.32)
```

RESULT

```    Array of subelements:
gid  - Geometry Identifier (for multigeomemtry objects, the individual high level geometry objects it describes).
sid  - Sub Element Identifier
geom - Geometry representation of subelement.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding.
Simon Greener - Jan 2015 - Port to TSQL SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 101. GEOPROCESSING/STExtractPolygon (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STExtractPolygon -- Extracts polygons from GeometryCollection
```

SYNOPSIS

```    Function [\$(owner)].[STExtractPolygon] (
@p_geometry geometry
)
Returns geometry
```

DESCRIPTION

```    This function allows a user to extract polygons from a GeometryCollection.
Useful where result of an STIntersection between two polygons results in points, lines and polygons.
If input is already a polygon it is returned unchanged.
```

INPUTS

```    @p_geometry (geometry) - CurvePolygon, Polygon, MultiPolygon or GeometryCollection geometry objects.
```

NOTES

```    Depends on STExtract.
```

EXAMPLE

```    -- Result of STIntersection() between two overlapping polygons can result in points, lines and polygons.
-- Extract only polygons...
WITH data As (
SELECT geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0) as geoma,
geometry::STGeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0) as geomb
)
SELECT CAST('POLY A' as varchar(12)) as source, d.geoma.AsTextZM() as geoma from data as d
union all
SELECT 'POLY B' as source, d.geomb.AsTextZM() as geomb from data as d
union all
SELECT 'Intersection' as source, d.geoma.STIntersection(d.geomb).AsTextZM() as geom FROM data as d
union all
SELECT 'RESULT' as source, [\$(owner)].[STExtractPolygon](d.geoma.STIntersection(d.geomb)).AsTextZM() as geom FROM data as d;
GO
source       geoma
------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POLY A       POLYGON ((100 0, 400 0, 400 480, 160 480, 160 400, 240 400, 240 300, 100 300, 100 0))
POLY B       POLYGON ((-175 0, 100 0, 0 75, 100 75, 100 200, 200 325, 200 525, -175 525, -175 0))
Intersection GEOMETRYCOLLECTION (POLYGON ((160 400, 200 400, 200 480, 160 480, 160 400)), POLYGON ((100 200, 180 300, 100 300, 100 200)), LINESTRING (100 200, 100 75), POINT (100 0))
RESULT       MULTIPOLYGON (((160 400, 200 400, 200 480, 160 480, 160 400)), ((100 200, 180 300, 100 300, 100 200)))
```

RESULT

```    (multi)polygon - Polygon or MultiPolygon object including CUrvePolygons..
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding.
Simon Greener - Jan 2015 - Port to TSQL SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 102. GEOPROCESSING/STFilterRings (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STFilterRings -- Removes rings from polygon/multipolygon below supplied area.
```

SYNOPSIS

```    Function [\$(owner)].[STFilterRings] (
@p_geometry geometry,
@p_area     float
)
Returns geometry
```

DESCRIPTION

```    This function allows a user to remove the inner rings of a polygon/multipolygon based on an area value.
Will remove both outer and inner rings.
```

INPUTS

```    @p_geometry (geometry) - Polygon or Multipolygon geometry object.
@p_area        (float) - Area in square SRID units below which an inner ring is removed.
```

EXAMPLE

```    SELECT [\$(owner)].[STFilterRings](geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0),2).AsTextZM() as geom
GO
geom
------------------------------------------------------------------
POLYGON ((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 5 7, 7 7, 7 5, 5 5))
```

RESULT

```    (multi)polygon (geometry) -- Input geometry with rings possibly filtered out.
```

NOTES

```    Depends on STExtract function.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding.
Simon Greener - Jan 2015 - Port to TSQL SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 103. GEOPROCESSING/STFlipVectors (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STFlipVectors - Turns linestring and polygon rings into vectors and then flips each vector until all point in the same direction.
```

SYNOPSIS

```    Function STFlipVectors (
@p_geometry geometry
)
Returns geometry
```

EXAMPLE

```    With gc As (
select geometry::STGeomFromText(
'GEOMETRYCOLLECTION(
POLYGON((10 0,20 0,20 20,10 20,10 0)),
POLYGON((20 0,30 0,30 20,20 20,20 0)),
POINT(0 0))',0) as geom
)
select v.sx,v.sy,v.ex,v.ey,count(*)
from gc as a
cross apply
[\$(owner)].[STVectorize] (
[\$(owner)].[STFlipVectors] ( a.geom )
) as v
group by v.sx,v.sy,v.ex,v.ey
go
```

DESCRIPTION

```    This function extracts all vectors from supplied linestring/polygon rings, and then flips each vector until all point in the same direction.
This function is useful for such operations as finding "slivers" between two polygons that are supposed to share a boundary.
Once the function has flipped the vectors the calling function can analyse the vectors to do things like find duplicate segment
which are part of a shared boundaries that are exactly the same (no sliver).
```

INPUTS

```    @p_geometry (geometry) - Any geometry containing linestrings.
RETURN
geometry (GeometryCollection) - The set of flipped vectors.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - August 2018 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 104. GEOPROCESSING/STForceCollection (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STForceCollection -- Creates a square buffer to left or right of a linestring.
```

SYNOPSIS

```    Function [\$(owner)].[STForceCollection] (
@p_polygon         geometry,
@p_linestrings     int = 0, -- 0 means polygons will be GC elements, otherwise LINESTRINGS
@p_multilinestring int = 0
)
Returns geometry
```

DESCRIPTION

```    This function extracts the rings of a polygon and returns them within a GeometryCollection or a MultLineString.
Polygons rings as polygons can only be returned in a GeometryCollection.
Polygon rings can be converted to LineStrings and returned in a GeometryCollection with no checking of validity by SQL Server Spatial.
Polygon rings can be converted to LineStrings and returned in a MultiLineStrings but are subject to validition by SQL Server Spatial.
If @p_linestrings = 0 and @p_multilinestring is 1 the function changes to @p_multilinestring 0 as polygons cannot be returned in a MultiLineString.
```

NOTES

```    1. Supports CompoundCurves in polygon rings
2. See PostGIS's ST_ForceCollection
```

INPUTS

```    @p_polygon    (geometry) - Must be a Polygon geometry.
@p_linestrings     (int) - Rings are to be converted to LineStrings if 1, otherwise Polygons.
@p_multilinestring (int) - Return rings using GeometryCollection (0) or MultiLineString (1)
```

RESULT

```    collection    (geometry) - Either MultiLineString or GeometryCollection.
```

EXAMPLE

```    select [\$(owner)].[STForceCollection](
geometry::STGeomFromText('POLYGON ((98.4 883.585, 115.729 ... 101.533 902.06))',0),
0,
0
).AsTextZM() as gCollection;
GO

gCollection
GEOMETRYCOLLECTION (POLYGON ((97.705 885.823, 93.766 886.819,....()

select [\$(owner)].[STForceCollection](
geometry::STGeomFromText('POLYGON ((98.4 883.585, 115.729 ... 101.533 902.06))',0),
1,
0
).AsTextZM() as gCollection;
GO

gCollection
GEOMETRYCOLLECTION (LINESTRING (97.705 885.823, 93.766 886.819, ... 101.533 902.06))

select [\$(owner)].[STForceCollection](
geometry::STGeomFromText('POLYGON ((97.705 885.823, 93.766 886.819, 109.224 898.931, 97.705 885.823))',0),
1,
1
).STAsText() as gCollection;

gCollection
MULTILINESTRING ((97.705 885.823, 93.766 886.819, 109.224 898.931, 97.705 885.823))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Oct 2019 - Original coding (Oracle).
```

```    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 105. GEOPROCESSING/STOffsetLine (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STOffsetLine -- Creates a line at a fixed offset from the input line.
```

SYNOPSIS

```    Function [\$(owner)].[STOffsetLine] (
@p_linestring geometry,
@p_distance   float,
@p_round_xy   int = 3,
@p_round_zm   int = 2
)
Returns geometry
```

DESCRIPTION

```    This function creates a parallel line at a fixed offset to the supplied line.
Supports simple linestrings and multilinestrings.
To create a line on the LEFT of the linestring (direction start to end) supply a negative p_distance;
a +ve value will create a line on the right side of the linestring.
Where the linestring either crosses itself or starts and ends at the same point, the result may not be as expected.
The final geometry will have its XY ordinates rounded to @p_round_xy of precision.
Support M ordinates is experimental: where supported the final geometry has its M ordinates rounded to @p_round_zm of precision.
```

NOTES

```    Does not currently support circular strings or compoundCurves.
Uses STOneSidedBuffer.
Z and M ordinates are not supported and where exist will be removed.
```

INPUTS

```    @p_linestring (geometry) - Must be a (Multi)linestring geometry.
@p_distance   (float)    - if < 0 then linestring is created on left side of original; if > 0 then offset linestring it to right side of original.
@p_round_xy   (int)      - Rounding factor for XY ordinates.
@p_round_zm   (int)      - Rounding factor for ZM ordinates.
```

RESULT

```    linestring    (geometry) - On left or right side of supplied line at required distance.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding (Oracle).
Simon Greener - Nov 2017 - Original coding for SQL Server.
Simon Greener - Oct 2019 - Large scale rewrite. Rename from STParallel to STOffsetLine.
```

```    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 106. GEOPROCESSING/STOffsetSegment (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STOffsetSegment -- Creates a line at a fixed offset from the input 2 point LineString or 3 point CircularString.
```

SYNOPSIS

```    Function STOffsetSegment (
@p_linestring geometry,
@p_offset     float,
@p_round_xy   int = 3,
@p_round_zm   int = 2

Returns geometry
```

EXAMPLE

```    WITH data AS (
SELECT geometry::STGeomFromText('CIRCULARSTRING (3 6.3 1.1 0, 0 7 1.1 3.1, -3 6.3 1.1 9.3)',0) as segment
UNION ALL
SELECT geometry::STGeomFromText('LINESTRING (-3 6.3 1.1 9.3, 0 0 1.4 16.3)',0) as segment
)
SELECT 'Before' as text, d.segment.AsTextZM() as rGeom from data as d
UNION ALL
SELECT 'After' as text, [\$(owner)].STOffsetSegment(d.segment,1,3,2).AsTextZM() as rGeom from data as d;
GO
```

DESCRIPTION

```    This function creates a parallel line at a fixed offset to the supplied 2 point LineString or 3 point CircularString.
To create a line on the LEFT of the segment (direction start to end) supply a negative @p_distance;
a +ve value will create a line on the right side of the segment.
The final geometry will have its XY ordinates rounded to @p_round_xy of precision, and its ZM ordinates rounded to @p_round_zm of precision.
```

NOTES

```    A Segment is defined as a simple two point LineString geometry or three point CircularString geometry.
```

INPUTS

```    @p_linestring  (geometry) - Must be a simple LineString or CircularString.
@p_offset         (float) - if < 0 then linestring is created on left side of original; if > 0 then offset linestring it to right side of original.
@p_round_xy         (int) - Rounding factor for XY ordinates.
@p_round_zm         (int) - Rounding factor for ZM ordinates.
```

RESULT

```    offset segment (geometry) - On left or right side of supplied segment at required distance.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding (Oracle).
Simon Greener - Nov 2017 - Original coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 107. GEOPROCESSING/STOneSidedBuffer (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STOneSidedBuffer -- Creates a square buffer to left or right of a linestring.
```

SYNOPSIS

```    Function STOneSidedBuffer (
@p_linestring      geometry,
@p_buffer_distance Float,
@p_square          int = 1,
@p_round_xy        int = 3,
@p_round_zm        int = 2
)
Returns geometry
```

DESCRIPTION

```    This function creates a square buffer to left or right of a linestring.
To create a buffer to the LEFT of the linestring (direction start to end) supply a negative p_buffer_distance;
a +ve value will create a buffer on the right side of the linestring.
Square ends can be created by supplying a positive value to @p_square parameter.
A value of 0 will create a rounded end at the start or end point.
Where the linestring either crosses itself or starts and ends at the same point, the result may not be as expected.
The final geometry will have its XY ordinates rounded to @p_round_xy of precision.
Support for Z and M ordinates is experimental: where supported the final geometry has its ZM ordinates rounded to @p_round_zm of precision.
```

NOTES

```    Supports circular strings and compoundCurves.
Is a 2D function, so any Z and M ordinates are lost.
Only supports single linestrings like LineString, CircularString and CompoundCurve.
Is an input linestring is not Simple (geomSTIsSimple()=0) the results will most likely be wrong.
Complex linestrings and large offset distances will most likely not return a clean result due to the nature of the algorithm
TOBEDONE
Better handling of non Simple linestrings.
Create implementation based on linear offsets.
```

INPUTS

```    @p_linestring (geometry) - Must be a linestring geometry.
@p_distance   (float)    - if < 0 then left side buffer; if > 0 then right sided buffer.
@p_square     (int)      - 0 = no (round mitre); 1 = yes (square mitre)
@p_round_xy   (int)      - Rounding factor for XY ordinates.
@p_round_zm   (int)      - Rounding factor for ZM ordinates.
```

RESULT

```    polygon       (geometry) - Result of one sided buffering of a linestring.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding (Oracle).
Simon Greener - Nov 2017 - Original coding for SQL Server.
Simon Greener - Oct 2019 - Improvements to handle disppearing segments.
```

```    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 108. GEOPROCESSING/STSegmentize (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STSegmentize - Dumps all segments of supplied geometry object to SQL rowset with optional filtering
```

SYNOPSIS

```   Function [\$(owner)].[STSegmentize] (
@p_geometry     geometry,
@p_filter    varchar(20), -- ALL, X, Y, Z, CLOSEST, FURTHEST, ID, LENGTH, MEASURE, LENGTH_RANGE, MEASURE_RANGE, or Z_RANGE.';
@p_point        geometry,
@p_filter_value    float,
@p_start_value     float,
@p_end_value       float
)
Returns Table
```

DESCRIPTION

```    This function segments the supplied geometry into 2-point linestrings or 3 point CircularStrings.
- Segment identifiers (ie from 1 through n);
- Start/Mid/End Coordinates as ordinates;
- Length of segment.
- Geometry representation of segment.
The function can also filter the (@p_filter) generated segments as follows:
- MISSPELL/NULL/ALL -- The default ie returns all segments unfiltered,
-                 X -- Returns segments whose X range (min/max) contains the supplied value,
-                 Y -- Returns segments whose Y range (min/max) contains the supplied value,
-                 Z -- Returns segments whose Z range (min/max) contains the supplied value,
-           CLOSEST -- Returns segment(s) closest to supplied @p_point
-          FURTHEST -- Returns segment(s) furtherest away from supplied @p_point
-                ID -- Returns segment with nominated ID (segment from start)
-            LENGTH -- Returns segment whose length straddles the supplied value (starting from 0)
-           MEASURE -- Returns segment whose m range (sm/em) straddles the supplied value
-      LENGTH_RANGE -- Returns segments that cover the supplied @p_start_value/@p_end_value length values
-     MEASURE_RANGE -- Returns segments that cover the supplied @p_start_value/@p_end_value measure values
-           Z_RANGE -- Returns segments that cover the supplied @p_start_value/@p_end_value Z values
```

NOTES

```    - Function supports COMPOUNDCURVES and CIRCULARSTRINGs
- COMPOUNDCURVES are broken in to the component elements, with each processed accordingly
- CIRCULARSTRINGs are broken into individual CIRCULARSTRING sub-elements (segments).
- If measure of supplied linestring is descending, the @p_start_value/@p_end_values must also be decreasing.
```

INPUTS

```    @p_geometry (geometry) -- Any non-point geometry object
@p_filter  varchar(20) -- ALL, X, Y, CLOSEST, FURTHEST, ID, LENGTH, MEASURE, LENGTH_RANGE, or MEASURE_RANGE.
@p_point      geometry -- Point for use with CLOSEST/FURTHEST
@p_filter_value  float -- For X, Y, CLOSEST, FURTHEST, ID (CAST TO integer), LENGTH, MEASURE
@p_start_value   float -- Min range value for use with LENGTH_RANGE, or MEASURE_RANGE.
@p_end_value     float -- Max range value for use with LENGTH_RANGE, or MEASURE_RANGE.
```

RESULT

```    SQL Select statement with the following.
id                  (int) - Unique identifier starting at segment 1.
max_id              (int) - Id of last segment.
hierarchy  (varchar(max)) - Hierarchically organised STGeometryTypes from start of @p_geometry
element_id          (int) - Top level element identifier eg 1 for first polygon in multiPolygon.
sub_element_id      (int) - SubElement identifier of subelement of element with parts eg OuterRing of Polygon
segment_id          (int) - Unique identifier for all segments of a specific 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
measure_range     (float) - Measure range for each segment (if -ve descending measures)
z_range           (float) - Z range for each segment (if -ve descending measures)
cumulative_measure(float) - Cumulative measure (could descend or ascend in value)
segment_length    (float) - Length of this segment in SRID units
start_length      (float) - Length at start vertex of segment
cumulative_length (float) - Sum Length from start of @p_geometry
closest_distance  (float) - Distance from supplied @p_point to segment
min_distance      (float) - Distance from supplied @p_point to closest segment
max_distance      (float) - Distance from supplied @p_point to furthest segment
segment        (geometry) - Geometry representation of segment.
next_segment   (geometry) - Segment following on from current (ie id 2 when id 1)
```

EXAMPLE

```    SELECT v.*
FROM [\$(owner)].[STSegmentize] (
geometry::STGeomFromText('CIRCULARSTRING (0 0, 0 4,3 6.3246, 5 5, 6 3, 5 0,0 0)',0),
'ALL',
NULL,
NULL,
NULL,
NULL

) as v;
GO
id element_id sub_element_id segment_id sx sy     sz   sm   mx my mz   mm   ex ey     ez   em   segment_length   cumulative_length segment
1  1          1              1           0  0      NULL NULL 0  4  NULL NULL 3  6.3246 NULL NULL 8.07248268970323 8.07248268970323  0x....
2  1          1              2           3  6.3246 NULL NULL 5  5  NULL NULL 6  3      NULL NULL 4.68822179023796 12.7607044799412  0x....
3  1          1              3           6  3      NULL NULL 5  0  NULL NULL 0  0      NULL NULL 8.83208735675195 21.5927918366931  0x....
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - November 2019, Complete re-write of original
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 109. GEOPROCESSING/STSegmentLine (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STSegmentLine - Segments the supplied linestring into 2-point linestrings or 3 point CircularStrings.
```

SYNOPSIS

```    Function STSegmentLine(
@p_geometry geometry
)
Returns @geometries 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

```    This function segments the supplied linestring into 2-point linestrings or 3 point CircularStrings.
* WKT tags;
* Segment identifiers (ie from 1 through n);
* Start/Mid/End Coordinates as ordinates;
* Segment length and cumulative length from start;
* Measure range for segment (endM - startM)
* Geometry representation of segment.
```

NOTES

```    Supports LineString (2008), MultiLineString (2008), CircularString (2012) and CompoundCurve (2012) geometry types.
This version supports CircularString/CompoundCurve geometry types available from  SQL Server 2012 onwards.
```

INPUTS

```    @p_geometry (geometry) - Linear geometry types.
```

EXAMPLE

```    SELECT t.*
FROM [\$(owner)].[STSegmentLine](geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)) as t
GO
```

RESULT

```    Table (Array) of 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 - Dec 2017 - TSQL SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 110. GEOPROCESSING/STSquareBuffer (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STSquareBuffer -- Creates a square buffer to left or right of a linestring.
```

SYNOPSIS

```    Function STSquareBuffer (
@p_linestring      geometry,
@p_buffer_distance Float,
@p_round_xy        int = 3,
@p_round_zm        int = 2
)
Returns geometry
```

DESCRIPTION

```    This function buffers a linestring creating a square mitre at the end where a normal buffer creates a round mitre.
A value of 0 will create a rounded end at the start or end point.
Where the linestring either crosses itself or starts and ends at the same point, the result may not be as expected.
The final geometry will have its XY ordinates rounded to @p_round_xy of precision.
```

NOTES

```    Supports circular strings and compoundCurves.
```

INPUTS

```    p_linestring (geometry) - Must be a linestring geometry.
p_distance   (float)    - Buffer distance.
p_round_xy   (int)      - Rounding factor for XY ordinates.
p_round_zm   (int)      - Rounding factor for ZM ordinates.
```

RESULT

```    polygon      (geometry) - Result of square buffering a linestring.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Jan 2013 - Original coding (Oracle).
Simon Greener - Nov 2017 - Original coding for SQL Server.
```

```    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 111. GEOPROCESSING/STVectorize (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STVectorize - Dumps all vertices of supplied geometry object to ordered array.
```

SYNOPSIS

```   Function STVectorize (
@p_geometry  geometry
)
Returns @Vector Table (
id             int,
element_id     int,
subelement_id  int,
vector_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,
geom           geometry
)
```

EXAMPLE

```    SELECT e.[id], e.[element_id], e.[subelement_id], e.[vector_id],
e.[sx], e.[sy], e.[ex], e.[ey],
e.length, geom.STAsText() as geomWKT
FROM [\$(owner)].[STVectorize] (geometry::STGeomFromText(
'MULTIPOLYGON( ((200 200, 400 200, 400 400, 200 400, 200 200)),
((0 0, 100 0, 100 100, 0 100, 0 0),(40 40,60 40,60 60,40 60,40 40)) )',0)) as e
GO
id element_id subelement_id vector_id sx  sy  ex  ey  length geomWKT
-- ---------- ------------- --------- --  --  --  --  ------ -------
1 1          1             1         200 200 400 200 200    LINESTRING (200 200, 400 200)
2 1          1             2         400 200 400 400 200    LINESTRING (400 200, 400 400)
3 1          1             3         400 400 200 400 200    LINESTRING (400 400, 200 400)
4 1          1             4         200 400 200 200 200    LINESTRING (200 400, 200 200)
5 2          1             1           0   0 100   0 100    LINESTRING (0 0, 100 0)
6 2          1             2         100   0 100 100 100    LINESTRING (100 0, 100 100)
7 2          1             3         100 100   0 100 100    LINESTRING (100 100, 0 100)
8 2          1             4           0 100   0   0 100    LINESTRING (0 100, 0 0)
9 2          2             1          40  40  60  40  20    LINESTRING (40 40, 60 40)
10 2          2             2          60  40  60  60  20    LINESTRING (60 40, 60 60)
11 2          2             3          60  60  40  60  20    LINESTRING (60 60, 40 60)
12 2          2             4          40  60  40  40  20    LINESTRING (40 60, 40 40)
```

DESCRIPTION

```    This function segments the supplied geometry into 2-point linestrings or 3 point CircularStrings.
- Segment identifiers (ie from 1 through n);
- Start/Mid/End Coordinates as ordinates;
- Length of vector.
- Geometry representation of segment.
```

INPUTS

```    @p_geometry (geometry) - Any non-point geometry object
```

RESULT

```    Table (Array) of Vectors:
id             (int)      - Unique identifier starting at segment 1.
element_id     (int)      - Top level element identifier eg 1 for first polygon in multiPolygon.
subelement_id  (int)      - SubElement identifier of subelement of element with parts eg OuterRing of Polygon
vector_id      (int)      - Unique identifier for all segments of a specific 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
geom           (geometry) - Geometry representation of segment.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2008, Original Coding; January 2017 - Support for Circular Curve objects and subobjects.
```

```    (c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener
```

# 112. GEOPROCESSING/STVertices (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STVertices - Dumps all vertices of supplied geometry object to ordered array.
```

SYNOPSIS

```   Function [\$(owner)].[STVertices] (
@p_geometry  geometry
)
Returns @Points Table (
uid   int,
pid   int,
mid   int,
x     float,
y     float,
z     float,
m     float,
point geometry
)
```

EXAMPLE

```    SELECT e.[uid],e.[mid],e.[rid],e.[pid],
e.[x],e.[y],e.[z],e.[m],e.[point].STAsText() as point
FROM [\$(owner)].[STVertices] (
geometry::STGeomFromText(
'MULTIPOLYGON( ((200 200, 400 200, 400 400, 200 400, 200 200)),
((0 0, 100 0, 100 100, 0 100, 0 0),(40 40,60 40,60 60,40 60,40 40)) )',0)) as e
GO

uid mid rid pid   x   y    z    m point
--- --- --- --- --- --- ---- ---- ---------------
1   1   1   1   200 200 NULL NULL POINT (200 200)
2   1   1   2   400 200 NULL NULL POINT (400 200)
3   1   1   3   400 400 NULL NULL POINT (400 400)
4   1   1   4   200 400 NULL NULL POINT (200 400)
5   1   1   5   200 200 NULL NULL POINT (200 200)
6   2   1   1     0   0 NULL NULL POINT (0 0)
7   2   1   2   100   0 NULL NULL POINT (100 0)
8   2   1   3   100 100 NULL NULL POINT (100 100)
9   2   1   4     0 100 NULL NULL POINT (0 100)
10  2   1   5     0   0 NULL NULL POINT (0 0)
11  2   2   1    40  40 NULL NULL POINT (40 40)
12  2   2   2    60  40 NULL NULL POINT (60 40)
12  2   2   3    60  60 NULL NULL POINT (60 60)
14  2   2   4    40  60 NULL NULL POINT (40 60)
15  2   2   5    40  40 NULL NULL POINT (40 40)
```

DESCRIPTION

```    This function extracts the fundamental points that describe a geometry object.
The points are returning in the order they appear in the geometry object.
```

INPUTS

```    @p_geometry (geometry) - Any non-point geometry object
```

RESULT

```    Table (Array) of Points :
uid        (int) - Point identifier unique across the whole geometry object.
pid        (int) - Point identifier with element/subelement (1 to Number of Points in element).
mid        (int) - Unique identifier that describes the geometry object's multipart elements (eg linestring in MultiLineString).
rid        (int) - SubElement or Ring identifier.
x        (float) - Point X Ordinate
y        (float) - Point Y Ordinate
z        (float) - Point Z Ordinate
m        (float) - Point M Ordinate
point (geometry) - Point as geometry
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2008 - Original coding.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 113. INSPECT/STCoordDim (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCoordDim -- Function that returns the coordinate dimension of a geometry point.
```

SYNOPSIS

```    Function STCoordDim (
@p_point geometry,
)
Returns int
```

USAGE

```    SELECT STCoordDim (
STPointFromText('POINT(0 0)',0)
).AsTextZM() as coordDim;
# coordDim
2
```

DESCRIPTION

```    This function returns the coordinate dimension of a geometry point.
If only XY ordinates, 2 is returned.
If only XYZ or XYM ordinates, 3 is returned.
If XYZM ordinates, 4 is returned.
```

NOTES

```    Whether an ordinate exists is determined by whether it has a non-null value.
```

INPUTS

```    @p_point (geometry) - Supplied point geometry.
```

RESULT

```    dimensionality (int) - 2,3 or 4.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 114. INSPECT/STCoordDim (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCoordDim -- Function that returns the coordinate dimension of a  geometry point.
```

SYNOPSIS

```    Function STCoordDim (
@p_point geometry,
)
Returns int
```

USAGE

```    SELECT STCoordDim (
STPointFromText('POINT(0 0)',0)
) as coordDim;
# coordDim
2
```

DESCRIPTION

```    This function returns the coordinate dimension of a geometry point.
If only XY ordinates, 2 is returned.
If only XYZ or XYM ordinates, 3 is returned.
If XYZM ordinates, 4 is returned.
```

NOTES

```    Uses HasZ and HasM extended methods to determine whether an ordinate exists.
```

INPUTS

```    @p_point (geometry) - Supplied point geometry.
```

RESULT

```    dimensionality (int) - 2,3 or 4.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 115. INSPECT/STDetermine (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```   STDetermine - Determines all possible spatial relations between two geometry instances.
```

SYNOPSIS

```    Function [\$(owner)].[STDetermine]
(
@p_geometry1 geometry,
@p_geometry2 geometry
)
Return varchar(500)
```

DESCRIPTION

```    Compares the first geometry against the second using all the instance comparison methods,
returning a comma separated string containing tokens representing each method: STContains -> CONTAINS.
Methods and returned Strings are:
STDisjoint   -> DISJOINT
STEquals     -> EQUALS
STContains   -> CONTAINS
STCrosses    -> CROSSES
STOverlaps   -> OVERLAPS
STTouches    -> TOUCHES
STIntersects -> INTERSECTS
So if two polygons overlap each othre, a string containing "OVERLAPS" will be returned (see Example).
Or is a point lies inside a polygon the "CONTAINS" relationship would be returned (see Example);
Two lines that meet at a start/end point will return "TOUCHES".
Note that in all the cases above, "INTERSECTS" is always returned as a catch-all relationship: it always appears at the end of the returned string.
If two geometry objects are equal or disjoint a string containing only "EQUALS" or "DISJOINT" is returned.
```

INPUTS

```    @p_geometry1 (geometry) - Non-null geometry instance.
@p_geometry2 (geometry) - Non-null geometry instance.
```

RESULT

```    relations found (varchar) - Comma separated string containing tokens representing each method: STContains -> CONTAINS.
```

EXAMPLE

```    Select [\$(owner)].[STDetermine] (
geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0),
geometry::STGeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0)
) as relations;
go
relations
-------------------
OVERLAPS,INTERSECTS

Select [\$(owner)].[STDetermine] (
geometry::STGeomFromText('LINESTRING (100.0 0.0, 400.0 0.0)',0),
geometry::STGeomFromText('LINESTRING (90.0 0.0, 100.0 0.0)',0)
) as relations;
go
relations
-------------------
TOUCHES,INTERSECTS

Select [\$(owner)].[STDetermine] (
geometry::STGeomFromText('POLYGON ((100.0 0.0, 400.0 0.0, 400.0 480.0, 160.0 480.0, 160.0 400.0, 240.0 400.0,240.0 300.0, 100.0 300.0, 100.0 0.0))',0) ,
geometry::STPointFromText('POINT (250 150)',0)
) as relations;
go
relations
-------------------
CONTAINS,INTERSECTS

Select [\$(owner)].[STDetermine] (
geometry::STPointFromText('POINT (250 150)',0),
geometry::STPointFromText('POINT (250 150)',0)
) as relations;
go
relations
-------------------
EQUALS
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2018 - Original coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 116. INSPECT/STEndPoint (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STEndPoint - Function which returns last point in supplied geometry.
```

SYNOPSIS

```    Function STEndPoint (
@p_geometry geometry
)
Returns geometry
```

USAGE

```    SELECT STEndPoint (
ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0)
).STAsText() as endPoint;
# endPoint
'POINT(1.4 45.2)'
```

DESCRIPTION

```    Function that returns last point in the supplied geometry.
```

INPUTS

```    @p_geometry (geometry) - supplied geometry of any type.
```

RESULT

```    point      (geometry) - Last point in Geometry
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 117. INSPECT/STEquals (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STEquals -- Checks if all XYZM ordinates of two points are equal.
```

SYNOPSIS

```    Function [\$(owner)].[STEquals] (
@p_point1   geometry,
@p_point2   geometry,
@p_round_xy int   = 3,
@p_round_z  int   = 2,
@p_round_m  int   = 2
)
Returns geometry
```

DESCRIPTION

```    Standqard STIsEquals() function only processes XY ordinates of a point.
This function checks XY but also Z and M.
Decimal digits of precision are used in the comparison.
The input geometry objects must conform to the following:
1. Both must be of geometry type point
2. Both must have the same SRID
3. Both must have the same Coordinate Dimension ie XYZ=XYZ, XYM=XYM or XYZM=XYZM.
It is up to the caller to ensure these conditions are met.
```

ARGUMENTS

```    @p_point1   (geometry) - Point geometry possibly with elevation (Z) and measures (M).
@p_point2   (geometry) - Point geometry possibly with elevation (Z) and measures (M).
@p_round_xy      (int) - Decimal degrees of precision to which calculated XY ordinates are rounded.
@p_round_z       (int) - Decimal degrees of precision to which Z ordinates are compared.
@p_round_m       (int) - Decimal degrees of precision to which M ordinates are compared.
```

RESULT

```    1/0              (bit) - True is 1 and False is 0
```

NOTES

```    Supports Linestrings with CircularString elements.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2018 - Original Coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 118. INSPECT/STGeometryTypes (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STGeometryTypes -- Extracts all geometry type keywords from a geometry (and its sub-elements)
```

SYNOPSIS

```    Function [\$(owner)].[STGeometryTypes](
@p_geometry geometry
)
Returns geometry
```

DESCRIPTION

```    Returns list of geometry types (from OGC STGeometryType function)
that describe the contents of the passed in geometry.
All complex geometries are "exploded" to extract sub element geometry types
Geography objects can be processed by converting to geometry using
dbo.STToGeometry() function.
```

ARGUMENTS

```    @p_geometry (geometry) - Any valid geomtery
```

RESULT

```    string -- list of geometry types are appear (in order) in geometry.
```

EXAMPLE

```    -- Simple geometry
select dbo.[STGeometryTypes](geometry::STGeomFromText('POINT(0 1 2)',0)) as gtypes;
go
gtypes
POINT

-- Single CurvePolygon with one interior ring
select [\$(owner)].[STGeometryTypes](geometry::STGeomFromText('CURVEPOLYGON(CIRCULARSTRING(0 5, 5 0, 0 -5, -5 0, 0 5), (-2 2, 2 2, 2 -2, -2 -2, -2 2))',0)) as gtypes;
GO
gtypes
CURVEPOLYGON,CIRCULARSTRING

-- GeometryCollection
select  [\$(owner)].[STGeometryTypes](
geometry::STGeomFromText(
'GEOMETRYCOLLECTION(
LINESTRING(0 0,20 0,20 20,0 20,0 0),
CURVEPOLYGON(
COMPOUNDCURVE(
(0 -23.43778, 0 23.43778),
CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),
(-90 23.43778, -90 -23.43778),
CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))),
COMPOUNDCURVE(
CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778),
(0 -23.43778, 0 23.43778)))',0)) as gTypes;
GO
gTypes
GEOMETRYCOLLECTION,LINESTRING,CURVEPOLYGON,COMPOUNDCURVE,CIRCULARSTRING,CIRCULARSTRING,COMPOUNDCURVE,CIRCULARSTRING
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - April 2019 - SQL Server Spatial
```

```    (c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 119. INSPECT/STIsPseudoMultiCurve (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

```    Function [\$(owner)].[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 [\$(owner)].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
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 120. INSPECT/STNumDims (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCoordDim -- Function that returns the coordinate dimension of a geometry object.
```

SYNOPSIS

```    Function STNumDims (
@p_point geometry,
)
Returns int
```

USAGE

```    SELECT [\$(owner)].[STNumDims] (
) as coordDim;
coordDim
3
```

DESCRIPTION

```    This function processes geometry types other than a point (STCoordDim)
If only XY ordinates, 2 is returned.
If only XYZ or XYM ordinates, 3 is returned.
If XYZM ordinates, 4 is returned.
```

INPUTS

```    @p_geometry (geometry) - Supplied geometry.
```

RESULT

```    dimensionality (int) - 2,3 or 4.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 121. INSPECT/STNumRings (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STNumRings -- Function that returns a count of the number of rings of the supplied polygon object.
```

SYNOPSIS

```    Function STNumRings (
@p_geometry geometry,
)
Returns int
```

USAGE

```    SELECT [\$(owner)].[STNumRings](geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0)) as numRings
NumRings
3
```

DESCRIPTION

```    This function returns the number of rings describing the supplied polygon geometry object.
Supports Polygon, MultiPolygon and CurvePolygon objects.
```

INPUTS

```    @p_geometry (geometry) - Supplied polygon geometry.
```

RESULT

```    Number of Rings (int) - N where N = 1 or more.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2012 - Original TSQL Coding for SQL Spatial.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 122. INSPECT/STNumTiles (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STNumTiles -- Calculates the number of grids that would cover the supplied MBR (LL/UR) given the size of a grid cell.
```

SYNOPSIS

```    Function STNumTiles (
@p_ll_x        float,
@p_ll_y        float,
@p_ur_x        float,
@p_ur_y        float,
@p_GridSize_X  float,
@p_GridSize_Y  float
)
Returns int
```

USAGE

```    SELECT [\$(owner)].[STNumTiles] (149.911044572819, -27.0987879643185, 153.205876564311, -24.0798390343147, 0.00225, 0.00225) / 4 as numGridCells;
GO
numGridCells
491507
```

DESCRIPTION

```    This function calculates the number of grids that would cover the supplied MBR (LL/UR) given the size of a tile (grid cell).
```

NOTES

```    See also STGeometry2MBR
```

INPUTS

```    @p_ll_x       (float) - X ordinate of lower left corner of envelope/MBR to be gridded.
@p_ll_y       (float) - Y ordinate of lower left corner of envelope/MBR to be gridded.
@p_ur_x       (float) - X ordinate of upper right corner of envelope/MBR to be gridded.
@p_ur_y       (float) - Y ordinate of upper right corner of envelope/MBR to be gridded.
@p_GridSize_X (float) - Size of grid cell in X ordinate dimension
@p_GridSize_Y (float) - Size of grid cell in Y ordinate dimension
```

RESULT

```    Number of grid cells (int) - N where N = 1 or more.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2012 - Original TSQL Coding for SQL Spatial.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 123. INSPECT/STStartPoint (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    ST_Start_Point -- Function which returns first point in supplied geometry.
```

SYNOPSIS

```    Function STStartPoint (
@p_geometry geometry
)
Returns geometry
```

USAGE

```    SELECT STStartPoint(
ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0)
).STAsText() as startPoint;

# startPoint
'POINT(0.1 0.2)'
```

DESCRIPTION

```    Function that returns first point in supplied geometry.
```

INPUTS

```    @p_geometry (geometry) - supplied geometry of any type.
```

RESULT

```    point       (geometry) - First point in Geometry
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 124. INSPECTION/STCircularStringN (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STCircularStringN-- Extracts CircularString from input CircularString that has more than one CircularArc in it.
```

SYNOPSIS

```    Function [\$(owner)].[STCircularStringN] (
@p_geometry geometry,
@p_stringN
)
Returns geometry
```

DESCRIPTION

```    A CircularString can have more than one string encoded within it.
For example if a circularString has 3 points it only has one circularString in it.
If a circularString has 5 points then it has two CircularStrings in it (Point 3 if end of first and start of second).
This function extracts each string but it is checked for validity before being returned.
If the string is invalid (collinear) null is returned by [\$(owner)].[STNumCircularStrings]
```

INPUTS

```    @p_geometry   (geometry) -- CircularString
@p_stringN     (integer) -- CircularString element within @p_geometry 1..NumCircularStrings
```

RESULT

```    circularSting (geometry) -- Circular String described by 3 points.
NOTE
Uses [\$(owner)].[STNumCircularStrings]
```

EXAMPLE

```    with data as (
select geometry::STGeomFromText('CIRCULARSTRING (0 0, 0 4,3 6.3246, 5 5, 6 3, 5 0,0 0)',0) as p_geometry
)
SELECT NumStrings.IntValue as curveN,
[\$(owner)].[STCircularStringN](a.p_geometry, NumStrings.IntValue).AsTextZM() as cString
FROM data as a
cross apply
[\$(owner)].[generate_series](1,[\$(owner)].[STNumCircularStrings](p_geometry),1) as NumStrings;
GO

CurveN cString
1      CIRCULARSTRING (0 0, 0 4, 3 6.3246)
2      CIRCULARSTRING (3 6.3246, 5 5, 6 3)
3      CIRCULARSTRING (6 3, 5 0, 0 0)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - November 2019 - Ported to SQL Server TSQL from PostgreSQL
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 125. INSPECTION/STIsCCW (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STIsCCW -- Computes whether a LinearRing is oriented counter-clockwise.
```

SYNOPSIS

```    Function STIsCCW (
@p_ring geometry
)
Returns geometry
```

DESCRIPTION

```    The linestring is assumed to have the first and last points equal ie is a LinearRing.
This will handle coordinate lists which contain repeated points.
This algorithm is only guaranteed to work with valid rings.
If the ring is invalid (e.g. self-crosses or touches), the computed result may not be correct.
```

INPUTS

```    @p_ring (geometry) -- LineString whose start/end points are the same (LinearRing)
```

RESULT

```    true/false (bit) -- True (1) if the ring is oriented counter-clockwise false (0) otherwise.
NOTE
This is a port of the algorithm in JTS.
```

EXAMPLE

```    with data as (
SELECT geometry::STGeomFromText('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (1 1, 1 9, 9 9, 9 1, 1 1))',0) as polygon
)
select 'Exterior' as Ring, [\$(owner)].[STisCCW](d.polygon.STExteriorRing()) as isCCW from data as d
union all
select 'Interior' as Ring, [\$(owner)].[STisCCW](d.polygon.STInteriorRingN(1)) as isCCW from data as d;
GO

Ring     isCCW
Exterior 1
Interior 0
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Martin Davis  - Original Java coding for Java Topology Suite
Simon Greener - October 2019 - Ported to SQL Server TSQL.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 126. INSPECTION/STNumCircularStrings (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STNumCircularStrings-- Returns number of CircularString elements in provided CircularString
```

SYNOPSIS

```    Function [\$(owner)].[STNumCircularStrings] (
@p_geometry geometry
)
Returns geometry
```

DESCRIPTION

```    A CircularString can have more than one 3-point string encoded within it.
For example if a circularString has 3 points it only has one circularString in it.
If a circularString has 5 points then it has two CircularStrings in it (Point 3 if end of first and start of second).
This function counts the number of individual CircularStrings in @p_geometry .
```

INPUTS

```    @p_geometry       (geometry) -- CircularString
```

RESULT

```    NumCircularStrings (integer) -- Number of 3-point CircularStrings within @p_geometry.
```

EXAMPLE

```    with data as (
select geometry::STGeomFromText('CIRCULARSTRING (0 0, 0 4,3 6.3246, 5 5, 6 3, 5 0,0 0)',0) as cGeom
)
SELECT [\$(owner)].[STNumCircularStrings](a.cGeom) as numStrings
FROM data as a
GO

numStrings
3
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - November 2019 - Ported to SQL Server TSQL from PostgreSQL
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 127. INTERCHANGE/STAsEWKT (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STAsEWKT -- Implements an method to create Extended Well Known Text (EWKT) strings from the input @p_geometry.
```

SYNOPSIS

```    Function [\$(owner)].[STAsEWKT] (
@p_geometry geometry
)
Returns varchar(max)
```

DESCRIPTION

```    Implements an export method that writes Extended Well Known Text (EWKT) from @p_geometry.
Writes SRID=nnn; prefix and ZM suffixes to geometry type..
Export WKT or PostGIS-style EWKT.
```

NOTES

```    A description of the EWKT structure is available in the PostGIS documentation.
```

PARAMETERS

```    @p_geometry (geometry) -- Any valid or empty geometry object.
```

RESULT

```    EWKT (varchar(max) -- EWKT string describing @p_geometry.
```

EXAMPLE

```    select [\$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2)',0));
GO

----------
POINT(1 2)

(1 row affected)

select [\$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2)',2274));
GO

--------------------
SRID=2274;POINT(1 2)

(1 row affected)

select [\$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2 3)',0));
GO

-------------
POINTZ(1 2 3)

(1 row affected)

select [\$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2 3)',2274));
GO

-----------------------
SRID=2274;POINTZ(1 2 3)

(1 row affected)

select [\$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2 NULL 3)',2274));
GO

----------------------------
SRID=2274;POINTM(1 2 3)

(1 row affected)

select [\$(owner)].[STAsEWKT](geometry::STGeomFromText('POINT (1 2 1.2 3)',2274));
GO

----------------------------
SRID=2274;POINTZM(1 2 1.2 3)

(1 row affected)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2020 - Original coding.
```

```    (c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 128. INTERCHANGE/STGeomFromEWKT (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STGeomFromEWKT -- Implements an import method for Extended Well Known Text including EWKT with SRID, Z and M ordinates..
```

SYNOPSIS

```    Function [dbo].[STGeomFromEWKT] (
@p_ewkt varchar(max)
)
Returns geometry
```

DESCRIPTION

```    Implements an import method for Extended Well Known Text including EWKT with SRID=, Z and M ordinates..
Returns valid geometry object if input is valid.
Imports any WKT or PostGIS-style EWKT.
Supports EWKT like "POINT EMPTY".
```

NOTES

```    A description of the EWKT structure is available in the PostGIS documentation.
```

RESULT

```    geometry (geometry) -- geometry containing a valid geometry with SRID, 2, 3 or 4 dimensions.
```

EXAMPLE

```    select [dbo].[STGeomFromEWKT]('POINT EMPTY').AsTextZM() as geom;
geom
-----------
POINT EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('MULTIPOINT EMPTY').AsTextZM() as geom;
geom
----------------
MULTIPOINT EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('LINESTRING EMPTY').AsTextZM() as geom;
geom
----------------
LINESTRING EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('CIRCULARSTRING EMPTY').AsTextZM() as geom;
geom
--------------------
CIRCULARSTRING EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('MULTILINESTRING EMPTY').AsTextZM() as geom;
geom
---------------------
MULTILINESTRING EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('POLYGON EMPTY').AsTextZM() as geom;
geom
-------------
POLYGON EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('MULTIPOLYGON EMPTY').AsTextZM() as geom;
geom
------------------
MULTIPOLYGON EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('COMPOUNDCURVE EMPTY').AsTextZM() as geom;
geom
-------------------
COMPOUNDCURVE EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('GEOMETRYCOLLECTION EMPTY').AsTextZM() as geom;
geom
------------------------
GEOMETRYCOLLECTION EMPTY

(1 row affected)

select [dbo].[STGeomFromEWKT]('POINT(1 2)').AsTextZM() as geom;
geom
-----------
POINT (1 2)

(1 row affected)

select [dbo].[STGeomFromEWKT]('POINTZ(1 2 3)').AsTextZM() as geom;
geom
-------------
POINT (1 2 3)

(1 row affected)

select [dbo].[STGeomFromEWKT]('POINTM(1 2 3)').AsTextZM() as geom;
geom
------------------
POINT (1 2 NULL 3)

(1 row affected)

select [dbo].[STGeomFromEWKT]('POINTZM(1 2 3 4)').AsTextZM() as geom;
geom
---------------
POINT (1 2 3 4)

(1 row affected)

select [dbo].[STGeomFromEWKT]('LineString (1 2,4 5,3 4,4 6,5 7,6 7)').AsTextZM() as geom;
geom
-----------------------------------------
LINESTRING (1 2, 4 5, 3 4, 4 6, 5 7, 6 7)

(1 row affected)

select [dbo].[STGeomFromEWKT]('SRID=2274;LINESTRING (1 2,4 5,3 4,4 6,5 7,6 7)').AsTextZM() as geom;
geom
-----------------------------------------
LINESTRING (1 2, 4 5, 3 4, 4 6, 5 7, 6 7)

(1 row affected)

select [dbo].[STGeomFromEWKT]('SRID=2274;LINESTRINGZ (1 2 3,3 4 5,4 6 6,5 7 7,6 7 8)').AsTextZM() as geom;
geom
----------------------------------------------
LINESTRING (1 2 3, 3 4 5, 4 6 6, 5 7 7, 6 7 8)

(1 row affected)

select [dbo].[STGeomFromEWKT]('SRID=2274;LINESTRINGM (1 2 3,3 4 5,4 6 6,5 7 7,6 7 8)').AsTextZM() as geom;
geom
-----------------------------------------------------------------------
LINESTRING (1 2 NULL 3, 3 4 NULL 5, 4 6 NULL 6, 5 7 NULL 7, 6 7 NULL 8)

(1 row affected)

select [dbo].[STGeomFromEWKT]('MULTILINESTRING ((1 2 3,4 5 6,3 4 5),(4 5 6,5 6 7, 5 6 7))').AsTextZM() as geom;
geom
--------------------------------------------------------------
MULTILINESTRING ((1 2 3, 4 5 6, 3 4 5), (4 5 6, 5 6 7, 5 6 7))

(1 row affected)

select [dbo].[STGeomFromEWKT]('MULTILINESTRING Z ((1 2 3,4 5 6,3 4 5),(4 5 6,5 6 7, 5 6 7))').AsTextZM() as geom;
geom
--------------------------------------------------------------
MULTILINESTRING ((1 2 3, 4 5 6, 3 4 5), (4 5 6, 5 6 7, 5 6 7))

(1 row affected)

select [dbo].[STGeomFromEWKT]('MULTILINESTRING M ((1 2 3,4 5 6,3 4 5),(4 5 6,5 6 7, 5 6 7))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------------------------
MULTILINESTRING ((1 2 NULL 3, 4 5 NULL 6, 3 4 NULL 5), (4 5 NULL 6, 5 6 NULL 7, 5 6 NULL 7))

(1 row affected)

select [dbo].[STGeomFromEWKT]('SRID=2287;MULTILINESTRING ZM ((1 2 3,4 5 6,3 4 5),(4 5 6,5 6 7, 5 6 7))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------------------------
MULTILINESTRING ((1 2 NULL 3, 4 5 NULL 6, 3 4 NULL 5), (4 5 NULL 6, 5 6 NULL 7, 5 6 NULL 7))

(1 row affected)

select [dbo].[STGeomFromEWKT]('POLYGON((0 0 1,10 0 1,10 10 1,0 10 1,0 0 1))').AsTextZM() as geom;
geom
-------------------------------------------------
POLYGON ((0 0 1, 10 0 1, 10 10 1, 0 10 1, 0 0 1))

(1 row affected)

select [dbo].[STGeomFromEWKT]('POLYGONZ((0 0 1,10 0 1,10 10 1,0 10 1,0 0 1))').AsTextZM() as geom;
geom
-------------------------------------------------
POLYGON ((0 0 1, 10 0 1, 10 10 1, 0 10 1, 0 0 1))

(1 row affected)

select [dbo].[STGeomFromEWKT]('POLYGONM((0 0 1,10 0 1,10 10 1,0 10 1,0 0 1))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------
POLYGON ((0 0 NULL 1, 10 0 NULL 1, 10 10 NULL 1, 0 10 NULL 1, 0 0 NULL 1))

(1 row affected)

select [dbo].[STGeomFromEWKT]('POLYGONZM((0 0 NULL 1, 10 0 NULL 1, 10 10 NULL 1, 0 10 NULL 1, 0 0 NULL 1))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------
POLYGON ((0 0 NULL 1, 10 0 NULL 1, 10 10 NULL 1, 0 10 NULL 1, 0 0 NULL 1))

(1 row affected)

select [dbo].[STGeomFromEWKT]('GEOMETRYCOLLECTION (POINT(0 0 1), LINESTRING(10 0 1, 10 10 1, 0 10 1))').AsTextZM() as geom;
geom
------------------------------------------------------------------------
GEOMETRYCOLLECTION (POINT (0 0 1), LINESTRING (10 0 1, 10 10 1, 0 10 1))

(1 row affected)

select [dbo].[STGeomFromEWKT]('GEOMETRYCOLLECTION Z(POINT(0 0 1), LINESTRING(10 0 1, 10 10 1, 0 10 1))').AsTextZM() as geom;
geom
------------------------------------------------------------------------
GEOMETRYCOLLECTION (POINT (0 0 1), LINESTRING (10 0 1, 10 10 1, 0 10 1))

(1 row affected)

select [dbo].[STGeomFromEWKT]('GEOMETRYCOLLECTION M(POINT(0 0 1), LINESTRING(10 0 1, 10 10 1, 0 10 1))').AsTextZM() as geom;
geom
--------------------------------------------------------------------------------------------
GEOMETRYCOLLECTION (POINT (0 0 NULL 1), LINESTRING (10 0 NULL 1, 10 10 NULL 1, 0 10 NULL 1))

(1 row affected)

select [dbo].[STGeomFromEWKT]('COMPOUNDCURVE M((2173369.79254475 259887.575230554 2600,2173381.122467 259911.320734575 2626.3106),CIRCULARSTRING (2173381.122467 259911.320734575 2626.3106,2173433.84355779 259955.557426129 0,2173501.82006501 259944.806018785 2768.24))').AsTextZM() as geom
geom
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMPOUNDCURVE ((2173369.79254475 259887.575230554 NULL 2600, 2173381.122467 259911.320734575 NULL 2626.3106), CIRCULARSTRING (2173381.122467 259911.320734575 NULL 2626.3106, 2173433.84355779 259955.557426129 NULL 0, 2173501.82006501 259944.806018785 NULL 2768.24))

(1 row affected)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2020 - Original coding.
```

```    (c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 129. MBR/STBoundingDiagonal (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STBoundingDiagonal -- Returns the diagonal of the supplied geometry's bounding box as a linestring.
```

SYNOPSIS

```    Function [dbo].[STBoundingDiagonal] (
@p_geom     geometry,
@p_round_xy int = 3,
@p_round_zm int = 2
)
Returns geometry
```

DESCRIPTION

```    This function creates a linestring diagonal for the input geometry.
```

NOTES

```    Does not support Points
```

INPUTS

```    @p_geom   (geometry) - Must not be a Point geometry.
@p_round_xy    (int) - Rounding factor for XY ordinates.
@p_round_zm    (int) - Rounding factor for ZM ordinates.
```

RESULT

```    linstring (geometry) - Result is diagonal of envelope around input geometry.
```

EXAMPLE

```    with data as (
select geometry::STGeomFromText('POLYGON ((0 0,100 0,100 10,0 10,0 0))',0) as geom
)
select [\$(owner)].[STBoundingDiagonal] (b.geom,3,2).STAsText() as bLine
from data as b;

bLine
LINESTRING (0 0, 100 10)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Oct 2019 - Original coding.
```

```    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 130. MBR/STGeography2MBR (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STGeography2MBR - Returns lower left and upper right coordinates of supplied geography's Envelope.
```

SYNOPSIS

```    Function STGeography2MBR (
@p_geography geography
)
Returns @table TABLE
(
minx Float,
miny Float,
maxx Float,
maxy Float
)
```

EXAMPLE

```    SELECT t.minx, t.miny, t.maxx, t.maxy
FROM [\$(owner)].[STGeography2MBR](geography::STGeogFromText('LINESTRING(0 0,0.1 0.1,0.5 0.5,0.8 0.8,1 1)',4326)) as t
GO
minx miny maxx maxy
---- ---- ---- ----
0    0    1    1
```

DESCRIPTION

```    Supplied with a non-NULL geometry, this function returns the ordinates of the lower left and upper right corners of the geography's STEnvelope/MBR.
```

INPUTS

```    @p_geography (ge-graphy) - Any geography object type.
```

RESULT

```    Table (Array) of Floats
minx (float) - X Ordinate of Lower Left Corner of Geography's MBR.
miny (float) - Y Ordinate of Lower Left Corner of Geography's MBR.
maxx (float) - X Ordinate of Upper Right Corner of Geography's MBR.
maxy (float) - Y Ordinate of Upper Right Corner of Geography's MBR.
```

NOTES

```    Uses [\$(owner)].[STToGeometry]
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Aug 2008 - Converted to SQL Server 2008
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 131. MBR/STGeometry2MBR (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STGeometry2MBR - Returns lower left and upper right coordinates of supplied geoemtry's Envelope.
```

SYNOPSIS

```    Function STGeometry2MBR (
@p_geometry geometry
)
Returns @table TABLE
(
minx Float,
miny Float,
maxx Float,
maxy Float
)
```

EXAMPLE

```    SELECT t.minx, t.miny, t.maxx, t.maxy
FROM [\$(owner)].[STGeometry2MBR](geometry::STGeomFromText('LINESTRING(0 0,0.1 0.1,0.5 0.5,0.8 0.8,1 1)',0)) as t
GO
minx miny maxx maxy
---- ---- ---- ----
0    0    1    1
```

DESCRIPTION

```    Supplied with a non-NULL geometry, this function returns the ordinates of the lower left and upper right corners of the geometries STEnvelope/MBR.
```

INPUTS

```    @p_geometry (geometry) - Any geometry object type.
```

RESULT

```    Table (Array) of Floats
minx (float) - X Ordinate of Lower Left Corner of Geometry MBR.
miny (float) - Y Ordinate of Lower Left Corner of Geometry MBR.
maxx (float) - X Ordinate of Upper Right Corner of Geometry MBR.
maxy (float) - Y Ordinate of Upper Right Corner of Geometry MBR.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Aug 2008 - Converted to SQL Server 2008
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 132. MBR/STMakeEnvelopeFromText (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMakeEnvelopeFromText -- Function that constructs a 5 point polygon from supplied string.
```

SYNOPSIS

```    Function [\$(owner)].[STMakeEnvelopeFromText] (
@p_mbr_coords varchar,
@p_delim      varchar(1) = ' ',
@p_srid       integer    = 0
)
Returns geometry
```

DESCRIPTION

NOTES

```    2D only.
```

INPUTS

```    @p_mbr_coords (varchar) - 2 coordinates, 4 ordinates
@p_delim      (varchar) - delimiter separating ordinates
@p_srid       (integer) - geometry srid
```

RESULT

```    geometry     (geometry) - Input coordinates converted to 5 point polygon.
```

EXAMPLE

```    SELECT [dbo].[STMakeEnvelopeFromText]('0,0,1,1',',',0).STAsText() as mbr;
GO
mbr
POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - August 2019 - Original TSQL Coding.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 133. MBR/STMBR2Geography (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMBR2Geography - Given lower left and upper right coordinates of geometry's envelope/mbr this function returns a 5 point polygon geometry.
```

SYNOPSIS

```    Function STMBR2Geography (
@p_minx     Float,
@p_miny     Float,
@p_maxx     Float,
@p_maxy     Float
@p_srid     Int,
@p_round_ll int = 3
)
Returns geometry
```

EXAMPLE

```    SELECT [\$(owner)].[STMBR2Geography](0,0,1,1,0,3)',0)).STAsText() as polygon
GO
polygon
POLYGON((0 0,1 0,1 1,0 1,0 0))
```

DESCRIPTION

```    Given lower left and upper right coordinates of geometry's envelope/mbr this function returns a 5 point polygon geometry.
The resultant polygons XY ordinates are rounded to the supplied value.
The SRID should be a valid projected SRID.
```

INPUTS

```    @p_minx   (float) - X Ordinate of Lower Left Corner of Geography MBR.
@p_miny   (float) - Y Ordinate of Lower Left Corner of Geography MBR.
@p_maxx   (float) - X Ordinate of Upper Right Corner of Geography MBR.
@p_maxy   (float) - Y Ordinate of Upper Right Corner of Geography MBR.
@p_srid     (int) - Valid projected SRID.
@p_round_ll (int) - Value used to round Latitude/Longitude ordinates to fixed decimal digits of precision.
```

RESULT

```    @p_geometry (geometry) - Polygon geometry with single exterior ring.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Aug 2008 - Converted to SQL Server 2008
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 134. MBR/STMBR2Geometry (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMBR2Geometry - Given lower left and upper right coordinates of geometry's envelope/mbr this function returns a 5 point polygon geometry.
```

SYNOPSIS

```    Function STMBR2Geometry (
@p_minx    Float,
@p_miny    Float,
@p_maxx    Float,
@p_maxy    Float
@p_srid     Int,
@p_round_xy int = 3
)
Returns geometry
```

EXAMPLE

```    SELECT [\$(owner)].[STMBR2Geometry](0,0,1,1,0,3)',0)).STAsText() as polygon
GO
polygon
POLYGON((0 0,1 0,1 1,0 1,0 0))
```

DESCRIPTION

```    Given lower left and upper right coordinates of geometry's envelope/mbr this function returns a 5 point polygon geometry.
The resultant polygons XY ordinates are rounded to the supplied value.
The SRID should be a valid projected SRID.
```

INPUTS

```    @p_minx   (float) - X Ordinate of Lower Left Corner of Geometry MBR.
@p_miny   (float) - Y Ordinate of Lower Left Corner of Geometry MBR.
@p_maxx   (float) - X Ordinate of Upper Right Corner of Geometry MBR.
@p_maxy   (float) - Y Ordinate of Upper Right Corner of Geometry MBR.
@p_srid     (int) - Valid projected SRID.
@p_round_xy (int) - Value used to round XY ordinates to fixed decimal digits of precision.
```

RESULT

```    @p_geometry (geometry) - Polygon geometry with single exterior ring.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Aug 2008 - Converted to SQL Server 2008
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 135. MBR/STMBRLongestSide (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMBRLongestSide - Returns length of shortest side of Envelope/MBR of supplied geometry.
```

SYNOPSIS

```    Function [\$(owner)].[STMBRLongestSide] (
@p_geometry geometry
)
Returns Float
```

EXAMPLE

```    with data as (
select geometry::STGeomFromText('POLYGON ((0 0,100 0,100 10,0 10,0 0))',0) as geom
)
SELECT [\$(owner)].[STMBRLongestSide] (a.geom) as length
FROM data as a;
GO

length
------
10.0
```

DESCRIPTION

```    Supplied with a non-NULL geometry, this function returns the length of the longest side of its Envelope.
```

INPUTS

```    @p_geometry (geometry) - Any geometry object type.
```

RESULT

```    length (float) - length of shortest side of envelope
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Oct 2019 - Original coding.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 136. MBR/STMBRShortestSide (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMBRShortestSide - Returns length of shortest side of Envelope/MBR of supplied geometry.
```

SYNOPSIS

```    Function [\$(owner)].[STMBRShortestSide] (
@p_geometry geometry
)
Returns Float
```

EXAMPLE

```    with data as (
select geometry::STGeomFromText('POLYGON ((0 0,100 0,100 10,0 10,0 0))',0) as geom
)
SELECT [\$(owner)].[STMBRShortestSide] (a.geom) as length
FROM data as a;
GO

length
------
100.0
```

DESCRIPTION

```    Supplied with a non-NULL geometry, this function returns the length of the shortest side of its Envelope.
```

INPUTS

```    @p_geometry (geometry) - Any geometry object type.
```

RESULT

```    length (float) - length of shortest side of envelope
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Oct 2019 - Original coding.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 137. SORT/ST_Morton (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    ST_Morton -- Function which creates a Morton (Space) Key from a supplied point object.
```

SYNOPSIS

```    Function ST_Morton (
@p_point geography
)
Returns int
```

USAGE

```    SELECT [\$(owner)].[ST_Morton](geography::Point(-34.53561,147.2320392,4326)) as mKey;
mKey
390
```

DESCRIPTION

```    Function that creates a Morton Key from a point's XY real world ordinates
Implementation within a specific site is normally a constant based on a standard row/column division
of the MBR of all the data within an organisation.
```

NOTES

```    Could be rewritten with geometry @p_point and not geography.
```

INPUTS

```    @p_point (geometry) - Real world point whose XY ordinates are converted to Row/Col references.
```

RESULT

```    morton_key  (float) - Single integer morton key value.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 138. SORT/STMorton (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STMorton -- Function which creates a Morton (Space) Key from the supplied row and column reference.
```

SYNOPSIS

```    Function STMorton (
@p_col int,
@p_row int
)
Returns int
```

USAGE

```    SELECT STMorton (10, 10) as mKey;
# mKey
828
```

DESCRIPTION

```    Function that creates a Morton Key from a row/col (grid) reference.
The generated value can be used to order/sort geometry objects.
```

INPUTS

```    @p_col      (int) - Grid Column Reference.
@p_row      (int) - Grid Row Reference.
```

RESULT

```    morton_key  (int) - single integer morton key.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Professor David M. Mark - January  1984 - C;
Simon Greener           - December 2011 - Original Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 139. TILING/STTileByNumGrids (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STTileByNumGrids -- Covers supplied geometry object with a mesh of a specific number of times in X and Y.
```

SYNOPSIS

```    Function [\$(owner)].[STTileByNumGrids] (
@p_geometry  geometry,
@p_NumGridsX integer,
@p_NumGridsY integer,
@p_rPoint    geometry, -- Point(rx,ry)
@p_rAngle    float,
@p_AsPoint   bit
)
Returns @table table
(
col  Int,
row  Int,
geom geometry
)
```

DESCRIPTION

```    Computes Envelope/MBR of supplied geometry object. Then computes size of individual tile
by dividing the XY extents of the computed MBR by the supplied number of tiles in X (columns) and Y (rows).
All rows and columns are visited, with polygons being created that represent each tile using
the compute size in X and Y.
If @p_rPoint (Geometry Point only) and @p_rAngle (whole circle bearing) are supplied, the resultant grid is rotated around the @p_rPoint and @p_rAngle angle.
```

INPUTS

```    @p_geometry (geometry) - Any geometry type (except Point) over which a grid of tiles is produced.
@p_NumGridsX (integer) - The number of grids in the X direction (columns)
@p_NumGridsY (integer) - The number of grids in the Y direction (rows)
@p_rPoint   (geometry) - Rotation Point.
@p_rAngle      (float) - Rotation angle expressed in decimal degrees between 0 and 360.
@p_AsPoint       (bit) - Return tile as point or polygon
```

RESULT

```    A Table of the following is returned
(
col  Int      -- The column reference for a tile
row  Int      -- The row reference for a tile
geom geometry -- The polygon geometry covering the area of the Tile.
)
```

EXAMPLE

```    SELECT row_number() over (order by col, row) as tileId,
col,
row,
geom.STAsText() as Tile
FROM [\$(owner)].[STTileByNumGrids](
geometry::STGeomFromText('LINESTRING(12.160367016481 55.474850814352,12.171397605408 55.478619145167)',0),
2, 2,
geometry::STGeomFromText('POINT(12.160367016481 55.474850814352)',0),
45,0
) as t;
GO

tileId col  row   Tile
1      2204 29442 POLYGON ((12.1557 55.4736, 12.1612 55.4736, 12.1612 55.4755, 12.1557 55.4755, 12.1557 55.4736))
2      2204 29443 POLYGON ((12.1557 55.4755, 12.1612 55.4755, 12.1612 55.4774, 12.1557 55.4774, 12.1557 55.4755))
3      2205 29442 POLYGON ((12.1612 55.4736, 12.1667 55.4736, 12.1667 55.4755, 12.1612 55.4755, 12.1612 55.4736))
4      2205 29443 POLYGON ((12.1612 55.4755, 12.1667 55.4755, 12.1667 55.4774, 12.1612 55.4774, 12.1612 55.4755))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - October 2011 - Ported from Oracle to TSQL.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 140. TILING/STTileGeogByPoint (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

```    STTileGeogByPoint -- Creates grid of tiles in geographic space.
```

SYNOPSIS

```    Function STTileGeogByPoint (
@p_point    geography,
@p_origin   varchar(2),
@p_numTileX integer,
@p_numTileY integer,
@p_TileX    float,
@p_TileX    float,
@p_rAngle   float,
@p_AsPoint  bit
)
Returns @table table
(
col  Int,
row  Int,
geom geometry
)
```

INPUTS

```    @p_point  (geography) -- Starting Point for grid (Upper Left)
@p_origin   (varchar) -- Position of point wrt grid: LL,UL,LR,UR
@p_numTileX (integer) -- Number of tiles in X (longitude) direction
@p_numTileY (integer) -- Number of tiles in Y (latitude) direction
@v_TileX      (float) -- Size of a Tile's X dimension in real world units along parallel of Latitude (ie X distance)
@v_TileY      (float) -- Size of a Tile's Y dimension in real world units along meridian of Longitude (ie Y distance)
@p_rAngle     (float) -- Optional rotation angle from North.
@p_AsPoint      (bit) -- Return Tile as point or polygon
```

RESULT

```    A Table of the following is returned
(
col  Int      -- The column reference for a tile
row  Int      -- The row reference for a tile
geom geometry -- The polygon geometry covering the area of the Tile.
)
```

EXAMPLE

```    select col,row, geom.STAsText() as tileGeog
from [\$(Owner)].[STTileGeogByPoint] (
geography::Point(55.634269978244,12.051864414446,4326),
'LL',
2,2,
10.0, 15.0,
22.5
) as t;
GO

col row tileGeog
0   0   POLYGON ((12.052084452911 55.634218419749, 12.052304491086 55.634166861254, 12.052365253079 55.634249843067, 12.052145214983 55.634301401561, 12.052084452911 55.634218419749))
0   1   POLYGON ((12.052145214983 55.634301401561, 12.052365253624 55.634249843067, 12.052426015745 55.634332824878, 12.052205977184 55.634384383372, 12.052145214983 55.634301401561))
1   0   POLYGON ((12.052304490797 55.63416686086, 12.052524528684 55.634115302364, 12.052585290597 55.634198284177, 12.05236525279 55.634249842672, 12.052304490797 55.63416686086))
1   1   POLYGON ((12.05236525279 55.634249842672, 12.052585291142 55.634198284177, 12.052646053184 55.63428126599, 12.052426014912 55.634332824484, 12.05236525279 55.634249842672))
```

NOTES

```    Depends on [\$(CogoOwner)].[STDirectVincenty]
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - October 2019 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 141. TILING/STTileGeom (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STTileGeom -- Covers envelope of supplied goemetry with a mesh of tiles of size TileX and TileY.
```

SYNOPSIS

```    Function [\$(owner)].[STTileGeom] (
@p_geometry geometry,
@p_TileX    float,
@p_TileY    float,
@p_rx       float,
@p_ry       float,
@p_rangle   float,
@p_AsPoint    bit
)
Returns table
```

DESCRIPTION

```    Function that takes a non-ppoint geometry type, determines its spatial extent (LL/UR),
computes the number of tiles given the tile size @p_TileX/@p_TileY (real world units),
creates each tile as a polygon, and outputs it in the table array with its col/row reference.
The lower left and upper right coordinates are calculated as follows:
LL.X = @p_geometry.STEnvelope().STPointN(1).STX;
LL.Y = @p_geometry.STEnvelope().STPointN(1).STY;
UR.X = @p_geometry.STEnvelope().STPointN(3).STX;
UR.Y = @p_geometry.STEnvelope().STPointN(3).STY;
The number of columns and rows that cover this area is calculated.
All rows and columns are visited, with polygons being created that represent each tile.
If @p_rx/@p_ry/@p_rangle are supplied, the resultant grid is rotated around @p_rx and @p_ry angle @p_rangle.
```

INPUTS

```    @p_geometry (geometry) -- Column reference
@p_TileX       (float) -- Size of a Tile's X dimension in real world units.
@p_TileY       (float) -- Size of a Tile's Y dimension in real world units.
@p_rX          (float) - X ordinate of rotation point.
@p_rY          (float) - Y ordinate of rotation point.
@p_rangle      (float) - Rotation angle expressed in decimal degrees between 0 and 360.
@p_AsPoint       (bit) - Return tile as ppoint (middle) or polygon
```

RESULT

```    A Table of the following is returned
colN  Int     -- The column reference for a tile
rowN  Int     -- The row reference for a tile
tile geometry -- The polygon geometry covering the area of the Tile.
```

EXAMPLE

```    SELECT t.colN, t.rowN, t.tile.STAsText() as geom
FROM [\$(owner)].[STTileGeom] (
geometry::STGeomFromText('POLYGON((100 100, 900 100, 900 900, 100 900, 100 100))',0),
400,200,0,0,0,0
) as t;
GO

col row geom
--- --- ------------------------------------------------------------
0   0   POLYGON ((0 0, 400 0, 400 200, 0 200, 0 0))
0   1   POLYGON ((0 200, 400 200, 400 400, 0 400, 0 200))
0   2   POLYGON ((0 400, 400 400, 400 600, 0 600, 0 400))
0   3   POLYGON ((0 600, 400 600, 400 800, 0 800, 0 600))
0   4   POLYGON ((0 800, 400 800, 400 1000, 0 1000, 0 800))
1   0   POLYGON ((400 0, 800 0, 800 200, 400 200, 400 0))
1   1   POLYGON ((400 200, 800 200, 800 400, 400 400, 400 200))
1   2   POLYGON ((400 400, 800 400, 800 600, 400 600, 400 400))
1   3   POLYGON ((400 600, 800 600, 800 800, 400 800, 400 600))
1   4   POLYGON ((400 800, 800 800, 800 1000, 400 1000, 400 800))
2   0   POLYGON ((800 0, 1200 0, 1200 200, 800 200, 800 0))
2   1   POLYGON ((800 200, 1200 200, 1200 400, 800 400, 800 200))
2   2   POLYGON ((800 400, 1200 400, 1200 600, 800 600, 800 400))
2   3   POLYGON ((800 600, 1200 600, 1200 800, 800 800, 800 600))
2   4   POLYGON ((800 800, 1200 800, 1200 1000, 800 1000, 800 800))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
Simon Greener - October  2019 - Added rotation capability
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 142. TILING/STTileGeomByPoint (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STTileGeomByPoint -- Creates mesh of tiles anchored to supplied point.
```

SYNOPSIS

```    Function STTileGeomByPoint (
@p_point   geometry,
@p_numTileX integer,
@p_numTileY integer,
@p_TileX      float,
@p_TileY      float,
@p_rAngle     float,
@p_AsPoint      bit
)
Returns @table table
(
col  Int,
row  Int,
geom geometry
)
```

DESCRIPTION

```    This function generates a mesh (grid) of tiles anchored to the supplied origin point.
The mesh of tiles is controlled by three parameters:
1  XY tile size in meters;
2  The number of tiles in X and Y direction;
3 Optional rotation angle (around origin/achor point)
```

INPUTS

```    @p_point  (geometry) -- Origin/Anchor point of mesh
@p_numTileX integer) -- Number of tiles in X direction
@p_numTileY integer) -- Number of tiles in Y direction
@p_TileX     (float) -- Size of a Tile's X dimension in real world units.
@p_TileY     (float) -- Size of a Tile's Y dimension in real world units.
@p_rAngle    (float) -- Rotation angle around anchor point.
@p_AsPoint     (bit) -- Return tile as point or polygon
```

RESULT

```    A Table of the following is returned
(
col  Int      -- The column reference for a tile
row  Int      -- The row reference for a tile
geom geometry -- The polygon geometry covering the area of the Tile.
)
```

EXAMPLE

```    select col,row,[\$(Owner)].[STRound](geom,3,3,1,1).STAsText() as tile
from [\$(Owner)].[STTileGeomByPoint] (
geometry::Point(55,12,0),
4,   4,
10, 10,
5.2,0
) as t;
GO

col row tile
0   0   POLYGON ((55 12, 64.962 12.872, 64.09 22.834, 54.128 21.962, 55 12))
0   1   POLYGON ((54.128 21.962, 64.09 22.834, 63.219 32.795, 53.257 31.924, 54.128 21.962))
0   2   POLYGON ((53.257 31.924, 63.219 32.795, 62.347 42.757, 52.385 41.886, 53.257 31.924))
0   3   POLYGON ((52.385 41.886, 62.347 42.757, 61.476 52.719, 51.514 51.848, 52.385 41.886))
1   0   POLYGON ((64.962 12.872, 74.924 13.743, 74.052 23.705, 64.09 22.834, 64.962 12.872))
1   1   POLYGON ((64.09 22.834, 74.052 23.705, 73.181 33.667, 63.219 32.795, 64.09 22.834))
1   2   POLYGON ((63.219 32.795, 73.181 33.667, 72.309 43.629, 62.347 42.757, 63.219 32.795))
1   3   POLYGON ((62.347 42.757, 72.309 43.629, 71.438 53.591, 61.476 52.719, 62.347 42.757))
2   0   POLYGON ((74.924 13.743, 84.886 14.615, 84.014 24.577, 74.052 23.705, 74.924 13.743))
2   1   POLYGON ((74.052 23.705, 84.014 24.577, 83.143 34.539, 73.181 33.667, 74.052 23.705))
2   2   POLYGON ((73.181 33.667, 83.143 34.539, 82.271 44.501, 72.309 43.629, 73.181 33.667))
2   3   POLYGON ((72.309 43.629, 82.271 44.501, 81.4 54.462, 71.438 53.591, 72.309 43.629))
3   0   POLYGON ((84.886 14.615, 94.848 15.486, 93.976 25.448, 84.014 24.577, 84.886 14.615))
3   1   POLYGON ((84.014 24.577, 93.976 25.448, 93.105 35.41, 83.143 34.539, 84.014 24.577))
3   2   POLYGON ((83.143 34.539, 93.105 35.41, 92.233 45.372, 82.271 44.501, 83.143 34.539))
3   3   POLYGON ((82.271 44.501, 92.233 45.372, 91.362 55.334, 81.4 54.462, 82.271 44.501))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - October 2019 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 143. TILING/STTiler (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STTiler -- Covers supplied envelope (LL/UR) with a mesh of tiles of size TileX and TileY,
and writes them to a new table created with the supplied name.
```

SYNOPSIS

```    Procedure STTiler (
@p_ll_x      float,
@p_ll_y      float,
@p_ur_x      float,
@p_ur_y      float,
@p_TileX     float,
@p_TileY     float,
@p_rx        float,
@p_ry        float,
@p_rangle    float
@p_srid      int,
@p_out_table nvarchar(128),
@p_geography Int = 1,
@p_AsPoint   bit = 0
)
```

DESCRIPTION

```    Procedure that takes a spatial extent (LL/UR), computes the number of tiles that cover it and
The number of columns and rows that cover this area is calculated using @p_TileX/@p_TileY which
are in @p_SRID units.
All rows and columns are visited, with polygons being created that represent each tile.
If @p_rx/@p_ry/@p_rangle are supplied, the resultant grid is rotated around @p_rx and @p_ry angle @p_rAgle.
```

INPUTS

```    @p_ll_x         (float) - Spatial Extent's lower left X/Longitude ordinate.
@p_ll_y         (float) - Spatial Extent's lower left Y/Latitude  ordinate.
@p_ur_x         (float) - Spatial Extent's upper right X/Longitude ordinate.
@p_ur_y         (float) - Spatial Extent's upper right Y/Latitude  ordinate.
@p_TileX        (float) - Size of a Tile's X dimension in decimal degrees.
@p_TileY        (float) - Size of a Tile's Y dimension in decimal degrees.
@p_rX           (float) - X ordinate of rotation point.
@p_rY           (float) - Y ordinate of rotation point.
@p_rAngle       (float) - Rotation angle expressed in decimal degrees between 0 and 360.
@p_srid           (int) - Geographic SRID (default is 4326)
@p_out_table (nvarchar) - Name of table to hold tiles. Can be expressed as DB.OWNER.OBJECT.
@p_geography      (int) - If 1 (True) column in table will be geography; if 0, geometry.
@p_AsPoint        (bit) - Rturn tile as point or polygon
```

RESULT

```    A Table with the name @p_out_table is created with this structure:
Create Table + @p_out_table +
(
gid  Int Identity(1,1) not null,
geom geometry   -- If @p_geography = 0
geog geography  -- If @p_geography = 1
);
```

EXAMPLE

```    EXEC [\$(owner)].[STTiler] 0, 0, 1000, 1000, 250, 250, 0, 0, 0, 0, '[\$(owner)].GridLL', 0;
GO
SELECT COUNT(*) as tableCount FROM [\$(owner)].[GridLL];
GO

tableCount
----------
16
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
Simon Greener - October  2019 - Added rotation capability
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 144. TILING/STTileXY (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STTileXY -- Covers supplied envelope (LL/UR) with a mesh of tiles of size TileX and TileY.
```

SYNOPSIS

```    Function STTileXY (
@p_ll_x   float,
@p_ll_y   float,
@p_ur_x   float,
@p_ur_y    float,
@p_TileX  float,
@p_TileY  float,
@p_rx     float,
@p_ry     float,
@p_rangle float,
@p_srid   int = 0
)
Returns @table table
(
col  Int,
row  Int,
geom geometry
)
```

DESCRIPTION

```    Function that takes a spatial extent (LL/UR), computes the number of tiles that cover it and
the table array with its col/row reference.
The number of columns and rows that cover this area is calculated using @p_TileX/@p_TileY which
are in @p_SRID units.
All rows and columns are visited, with polygons being created that represent each tile.
If @p_rx/@p_ry/@p_rangle are supplied, the resultant grid is rotated around @p_rx and @p_ry angle @p_rangle.
```

INPUTS

```    @p_ll_x  (float) - Spatial Extent's lower left X ordinate.
@p_ll_y  (float) - Spatial Extent's lower left Y ordinate.
@p_ur_x  (float) - Spatial Extent's uppre righ X ordinate.
@p_ur_y  (float) - Spatial Extent's uppre righ Y ordinate.
@p_TileX (float) - Size of a Tile's X dimension in real world units.
@p_TileY (float) - Size of a Tile's Y dimension in real world units.
@p_rX    (float) - X ordinate of rotation point.
@p_rY    (float) - Y ordinate of rotation point.
@p_rangle (float) - Rotation angle expressed in decimal degrees between 0 and 360.
@p_srid    (int) - Geometric SRID.
```

RESULT

```    A Table of the following is returned
(
col  Int      -- The column reference for a tile
row  Int      -- The row reference for a tile
geom geometry -- The polygon geometry covering the area of the Tile.
)
```

EXAMPLE

```    SELECT row_number() over (order by t.col, t.row) as rid,
t.col, t.row, t.geom.STAsText() as geom
FROM [\$(owner)].[STTileXY](0,0,1000,1000,250,250,NULL,NULL,NULL,0) as t;
GO

rid col row geom
--- --- --- -----------------------------------------------------------
1  0   0   POLYGON ((0 0, 250 0, 250 250, 0 250, 0 0))
2  0   1   POLYGON ((0 250, 250 250, 250 500, 0 500, 0 250))
3  0   2   POLYGON ((0 500, 250 500, 250 750, 0 750, 0 500))
4  0   3   POLYGON ((0 750, 250 750, 250 1000, 0 1000, 0 750))
5  1   0   POLYGON ((250 0, 500 0, 500 250, 250 250, 250 0))
6  1   1   POLYGON ((250 250, 500 250, 500 500, 250 500, 250 250))
7  1   2   POLYGON ((250 500, 500 500, 500 750, 250 750, 250 500))
8  1   3   POLYGON ((250 750, 500 750, 500 1000, 250 1000, 250 750))
9  2   0   POLYGON ((500 0, 750 0, 750 250, 500 250, 500 0))
10  2   1   POLYGON ((500 250, 750 250, 750 500, 500 500, 500 250))
11  2   2   POLYGON ((500 500, 750 500, 750 750, 500 750, 500 500))
12  2   3   POLYGON ((500 750, 750 750, 750 1000, 500 1000, 500 750))
13  3   0   POLYGON ((750 0, 1000 0, 1000 250, 750 250, 750 0))
14  3   1   POLYGON ((750 250, 1000 250, 1000 500, 750 500, 750 250))
15  3   2   POLYGON ((750 500, 1000 500, 1000 750, 750 750, 750 500))
16  3   3   POLYGON ((750 750, 1000 750, 1000 1000, 750 1000, 750 750))
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - December 2011 - Original TSQL Coding for SQL Server.
Simon Greener - October  2019 - Added rotation capability
```

```    (c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener
```

# 145. TOOLS/_STArray (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    _STArray -- Base function for implementing arrays of geometry and geography.
```

SYNOPSIS

```    Function _STArray
(
@p_array  xml,
@p_i      int            = 1,
@p_action varchar(10)    = 'select',
@p_wkb    varbinary(max) = null, -- Ensures can save geometry and geography
@p_srid   int            = 0
)
Returns xml
```

USAGE

```    SELECT [\$(owner)].[_STArray] (........) as result;
Result
<Depends on @p_action>
```

DESCRIPTION

```    This function implements all the basic operations of an array of geometry/geography objects.
XML is used for the implementation.
The function is "hidden" due to the various methods returning different results eg count -> integer; array[i] -> srid,wkb.
The @p_action methods are:
insert : inserts geometry/geography into provided position @p_i in array.If @p_i exists, the position is updated.
update : updates geometry/geography at @p_i position in array.
delete : deleted geometry/geography at @p_i position in array.
select : returns geometry/geography WKB and SRID at @p_i position in array.
```

INPUTS

```    @p_array  (xml)           : Array itself
@p_i      (int)           : 1,
@p_action (varchar 10)    : select, update, delete, insert
@p_wkb    (varbinary max) : geometry/geography as WKB so can hold both object types.
@p_srid   (int)           : SRID of @p_wkb. It is possible for the array to hold heterogeneous SRIDed geom/geog objects.
```

RESULT

```    Depends on @p_action.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - May 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 146. TOOLS/dhms (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    dhms -- Function that takes a duration/time in seconds and returns a string that include the number of elapsed days.
```

SYNOPSIS

```    Function dhms(@p_seconds float)
Returns varchar(100)
```

USAGE

```     SELECT [\$(owner)].dhms((2.0 * 24.0 * 60.0 * 60.0) + 923.3) as dhms;
dhms
2:00:15:23:300
SELECT [\$(owner)].dhms((2.0 * 24.0 * 60.0 * 60.0) + 923) as dhms;
dhms
2:00:15:23
```

DESCRIPTION

```    Function that takes a duration expressed in seconds, and returns a string that included days, hours, minutes and seconds.
ie  DAYS:HOURS:MINUTES:SECONDS{:MICROSECONDS}
If the input seconds has decimal places, they are returned, if not they aren't.
```

INPUTS

```    @p_seconds      (float) : Non-NULL duration expressed in seconds.
```

RESULT

```    formated date (varchar) : Date formatted as DAYS:HOURS:MINUTES:SECONDS{:MICROSECONDS}
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - June 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 147. TOOLS/Generate_Series (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    Generate_Series - Creates a series of integers.
```

SYNOPSIS

```    Function Generate_Series(
@p_start int,
@p_end   int,
@p_step  int
)
Returns @Integers TABLE
(
IntValue int
)
```

DESCRIPTION

```    This function creates an array or series of integers starting at @p_start and finishing at @p_end.
The increment between the integer values is supplied by @p_step.
To generate 2, 4, 6, 8, 10 one calls the function as follows Generate_Series(2,10,2).
Negative values are supported.
```

INPUTS

```    @p_start (int) - Starting integer.
@p_end   (int) - Finishing integer.
@p_step  (int) - Step or increment.
```

EXAMPLE

```    SELECT t.IntValue
FROM [\$(owner)].[Generate_Series](2,10,2) as t;
GO
IntValue
--------
2
5
6
8
10
```

RESULT

```    Table (Array) of Integers
IntValue (int) - Generates integer value
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Dec 2017 - TSQL SQL Server
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 148. TOOLS/STFormatNumber (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STFormatNumber -- Formats a number into a string.
```

SYNOPSIS

```    FUNCTION [\$(owner)].[STFORMATNUMBER]
(
@number        decimal(38,15),
@decimalplaces int=0,
@format        varchar(115) = '',
@ifzero        varchar(115) = '0',
@ifNull        varchar(115) ='NULL'
)
Returns varchar(256)
```

DESCRIPTION

```    Creates a formatted string for the supplied number using supplied parameters.
```

ARGUMENTS

```    @number        (decimal 38 15)  -- number to format
@decimalplaces (int)            -- Number decimal places
@format        (varchar 115)    -- Format string.
@ifzero        (varchar 115)    -- String for zeros eg '0'
@ifNull        (varchar 115)    -- String for null values eg 'NULL'
```

RESULT

```    formatted number (varchar 256)  -- Formatted number
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2018 - Original Coding.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 149. TOOLS/STIsGeographicSrid (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STIsGeographicSrid -- Checks @p_srid to see if exists in sys.spatial_reference_systems table (which holds geodetic SRIDS)
```

SYNOPSIS

```    Function STIsGeographicSrid (
@p_srid int
)
Returns bit
```

USAGE

```    SELECT [\$(owner)].[STIsGeographicSrid](4283) as isGeographicSrid
GO
isGeographicSrid
----------------
1
```

DESCRIPTION

```    All geographic/geodetic SRIDs are stored in the sys.spatial_reference_systems table.
This function checks to see if the supplied SRID is in that table.
If it is, 1 is returned otherwise 0.
```

INPUTS

```    @p_srid (int) - Srid value.
```

RESULT

```    Y/N     (bit) - 1 if True; 0 if False
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - June 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 150. TOOLS/STNumArray (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STNumArray -- Return number of elements in the array (size).
```

SYNOPSIS

```    Function STNumArray
(
@p_array xml
)
Returns int
```

USAGE

```    SELECT [\$(owner)].[STNumArray](@p_array) as count;
Count
45
```

DESCRIPTION

```    This function returns the number of element in the supplied array (count)
Returns 0 is @p_array is NULL
```

INPUTS

```    @p_array  (xml) : Array itself
```

RESULT

```    Size/Count (int)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - May 2018 - Original TSQL Coding for SQL Server.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 151. TOOLS/STPointAsText (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STPointAsText -- Function that returns a formatted string representation of a coordinate.
```

SYNOPSIS

```    Function STPointAsText (
@p_dimensions varchar(4),  - XY, XYZ, XYM, XYZM or NULL (XY)
@p_X          float,
@p_Y          float,
@p_Z          float,
@p_M          float,
@p_round_x    int = 3,
@p_round_y    int = 3,
@p_round_z    int = 2,
@p_round_m    int = 2
)
Returns varchar(max)
```

EXAMPLE

```    With Data As (
select CAST('XY' as varchar(4)) as ords, CAST([dbo].[STPointAsText]('XY',0.1,0.2,0.3,0.41,3,3,2,1) as varchar(40)) as coords
union all
select 'XYZ'                    as ords, [dbo].[STPointAsText]('XYZ',0.1,0.2,0.3,0.41,3,3,2,1) as coords
union all
select 'XYM'                    as ords, [dbo].[STPointAsText]('XYM',0.1,0.2,0.3,0.41,3,3,2,1) as coords
union all
select 'XYZM'                   as ords, [dbo].[STPointAsText]('XYZM',0.1,0.2,0.3,0.41,3,3,2,1) as coords
)
select a.ords,
geometry::STGeomFromText (
'POINT (' + a.coords + ')',0).AsTextZM() as point
from data as a;
GO

ords point
XY   POINT (0.1 0.2)
XYZ  POINT (0.1 0.2 0.3)
XYM  POINT (0.1 0.2 NULL 0.4)
XYZM POINT (0.1 0.2 0.3 0.4)
```

DESCRIPTION

```    This function returns a formatted string representation of a coordinate with up to 4 ordinates.
Because ordinates can be NULL, the @p_dimensions instructs the function which ordinates are to be used.
The function is suitable for use in WKT text constructors as shown in the USAGE element of this documentation.
The function correctly rounds each ordinate using the supplied rounding factor.
```

INPUTS

```    @p_dimensions (varchar 4) - Ordinates to process. Valid values are XY, XYZ, XYM, XYZM or NULL (XY)
@p_X          (float)     - X Ordinate
@p_Y          (float)     - Y Ordinate
@p_Z          (float)     - Z Ordinate
@p_M          (float)     - M Ordinate
@p_round_x    (int)       - X Ordinate rounding factor.
@p_round_y    (int)       - Y Ordinate rounding factor.
@p_round_z    (int)       - Z Ordinate rounding factor.
@p_round_m    (int)       - M Ordinate rounding factor.
```

RESULT

```    formatted string (varchar max) - Formatted string.
```

AUTHOR

```    Simon Greener
```

HISTORY

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

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 152. TOOLS/STSnapPointToGeom (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    STSnapPointToGeom -- Function that snaps @p_point to @p_geom returning result of ShortestLineTo if within supplied distance.
```

SYNOPSIS

```    Function [dbo].[STSnapPointToGeom] (
@p_point       geometry,
@p_geom        geometry,
@p_snap_within float,
@p_round_xy    int = 3,
)
Returns varchar(max)
```

DESCRIPTION

```    This function is a wrapper over ShortestLineTo.
Given a point and a geometry the function computes the shortest distance from the point to the distance.
If that distance is < a user supplied @p_snap_within distance, the snap point is returned.
If the distance is > the user supplied @p_snap_within distance the original point is returned.
The function rounds each ordinate using the supplied rounding factor.
```

PARAMETERS

```    @p_point    (geometry) - The point the caller wants snapped to @p_geom.
@p_geom     (geometry) - A geometry the caller wants @p_point snapped to
@p_snap_within (float) - If the distance from @p_point to the snapped point is less than this value, the snapped point is returned.
@p_round_xy      (int) - X Ordinate rounding factor.
```

RESULT

```    Point (geometry) - @p_point is it is not within @p_snap_distance, otherwise the snap point geometry is returned.
```

EXAMPLE

```     select [dbo].[STSnapPointToGeom](
geometry::STGeomFromText('POINT (2172251.39758337 257358.817891138)',2274),
geometry::STGeomFromText('CIRCULARSTRING (2171796.8166267127 257562.7279690057, 2171785.1539784111 257183.20449278614, 2172044.2970194966 256905.68157368898)', 2274),
NULL,
3
).AsTextZM();
sPoint
POINT (2171795.01 257158.984)

select snap_within.IntValue as snap_within_distance,
[dbo].[STSnapPointToGeom](
geometry::STGeomFromText('POINT (2172251.39758337 257358.817891138)',2274),
geometry::STGeomFromText('CIRCULARSTRING (2171796.8166267127 257562.7279690057, 2171785.1539784111 257183.20449278614, 2172044.2970194966 256905.68157368898)', 2274),
snap_Within.IntValue,
3
).AsTextZM() as sPoint
from [dbo].[Generate_Series](100,600,100) as snap_within;

snap_within_distance sPoint
100                  POINT (2172251.398 257358.818)
200                  POINT (2172251.398 257358.818)
300                  POINT (2172251.398 257358.818)
400                  POINT (2172251.398 257358.818)
500                  POINT (2171795.01 257158.984)
600                  POINT (2171795.01 257158.984)
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - January 2020 - Original Coding for SQL Server.
```

```    (c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener
```

# 153. TOOLS/Tokenizer (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

```    Tokenizer - Splits any string into tokens and separators.
```

SYNOPSIS

```    Function Tokenizer (
@p_string     varchar(max),
@p_separators varchar(254)
)
Returns @varchar_table TABLE
(
id        int,
token     varchar(MAX),
separator varchar(MAX)
)
```

EXAMPLE

```    SELECT t.id, t.token, t.separator
FROM [\$(owner)].[TOKENIZER]('LINESTRING(0 0,1 1)',' ,()') as t
GO
id token       separator
-- ---------- ---------
1 LINESTRING (
2 0          NULL
3 0          ,
4 1          NULL
5 1          )
```

DESCRIPTION

```    Supplied a string and a list of separators this function returns resultant tokens as a table collection.
Function returns both the token and the separator.
Returned table collection contains a unique identifier to ensure tokens and separators are always correctly ordered.
```

INPUTS

```    @p_string     (varchar max) - Any non-null string.
@p_separators (varchar 254) - List of separators eg '(),'
```

RESULT

```    Table (Array) of Integers
id        (int)         - Unique identifier for each row starting with first token/separator found.
token     (varchar MAX) - Token between separators
separator (varchar MAX) - Separator between tokens.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Pawel Barut, http://pbarut.blogspot.com/2007/03/yet-another-tokenizer-in-oracle.html
Simon Greener - Aug 2008 - Converted to SQL Server 2008
Simon Greener - Aug 2012 - Added extra return variables.
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
```

# 154. TOOLS/Tokenizer (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

```    Tokenizer - Splits any string into tokens and separators.
```

SYNOPSIS

```    Function Tokenizer (
@p_string     varchar(max),
@p_separators varchar(254)
)
Returns @varchar_table TABLE
(
id        int,
token     varchar(MAX),
separator varchar(MAX)
)
```

EXAMPLE

```    SELECT t.id, t.token, t.separator
FROM [\$(owner)].[TOKENIZER]('LINESTRING(0 0,1 1)',' ,()') as t
GO
id token       separator
-- ---------- ---------
1 LINESTRING (
2 0          NULL
3 0          ,
4 1          NULL
5 1          )
```

DESCRIPTION

```    Supplied a string and a list of separators this function returns resultant tokens as a table collection.
Function returns both the token and the separator.
Returned table collection contains a unique identifier to ensure tokens and separators are always correctly ordered.
```

INPUTS

```    @p_string     (varchar max) - Any non-null string.
@p_separators (varchar 254) - List of separators eg '(),'
```

RESULT

```    Table (Array) of Integers
id        (int)         - Unique identifier for each row starting with first token/separator found.
token     (varchar MAX) - Token between separators
separator (varchar MAX) - Separator between tokens.
```

AUTHOR

```    Simon Greener
```

HISTORY

```    Simon Greener - Aug 2012 - Converted to SQL Server 2012 (Uses new Lag/Lead function and returns separators)
```

```    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener