TABLE OF CONTENTS

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.

COPYRIGHT

    (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
    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

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.

COPYRIGHT

    (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
     4530'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.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
      Creative Commons Attribution-Share Alike 2.5 Australia License.
      http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
      Creative Commons Attribution-Share Alike 2.5 Australia License.
      http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
      Creative Commons Attribution-Share Alike 2.5 Australia License.
      http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

    (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_dRadius Float,
               @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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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_dRadius   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_dRadius   (float) : Radius 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

COPYRIGHT

    (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> 34521''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> 18634''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.

COPYRIGHT

    (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_dRadius Float,
                @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

COPYRIGHT

    (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_dRadius Float,
                @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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

    (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,
               @dRadius  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
    @dRadius   (float) : Radius 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.

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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.
                       Z radius 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.

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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)
     Adding the equations gives:
       (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.

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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_equatorial_radius (float) - Radius at equator: default is WGS-84 of 6378.137.
    @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.

COPYRIGHT

    (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 
    devised by Thaddeus Vincenty.

SEE ALSO

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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> 34521'48.75"</DegMinSec>
          <Distance>345.364</Distance>
        </Segment>
        <Segment>
          <DegMinSec>  83 2'4.652"</DegMinSec>
          <Distance>83.0346</Distance>
        </Segment>
        <Segment>
          <DegMinSec> 18634'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.

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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_dRadius               Float,
                @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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
    Creative Commons Attribution-Share Alike 2.5 Australia License.
    http://creativecommons.org/licenses/by-sa/2.5/au/

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
             )
     Returns float (angle in radians)

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.
    Result is radians.

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.

COPYRIGHT

    (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
             )
     Returns float (angle in radians)

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.
    Result is radians.

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.

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
      Creative Commons Attribution-Share Alike 2.5 Australia License.
      http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
    Creative Commons Attribution-Share Alike 2.5 Australia License.
    http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
    Creative Commons Attribution-Share Alike 2.5 Australia License.
    http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
    Creative Commons Attribution-Share Alike 2.5 Australia License.
    http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

    (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_min_radius Float, 
      @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
    )
    select gs.IntValue as requiredMinRadius,
           [$(cogoowner)].[STFindCircleFromArc](circularArc).Z as ArcRadius,
           [$(owner)].[STCheckRadii](
                    circulararc,
                    gs.IntValue,
                    3).STAsText() as failingArc
      from data as a
           cross apply
           [$(owner)].[generate_series](5,15,5) as gs;
    
    requiredMinRadius ArcRadius failingArc
    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.

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

    (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

COPYRIGHT

    (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

COPYRIGHT

    (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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
      Creative Commons Attribution-Share Alike 2.5 Australia License.
      http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
      Creative Commons Attribution-Share Alike 2.5 Australia License.
      http://creativecommons.org/licenses/by-sa/2.5/au/

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.

COPYRIGHT

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

COPYRIGHT

    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
      Creative Commons Attribution-Share Alike 2.5 Australia License.
      http://creativecommons.org/licenses/by-sa/2.5/au/

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.
    The returned data includes all the metadata about the segmented linestring:
    - 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

COPYRIGHT

    (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. 
    The returned data includes all the metadata about the segmented linestring:
    * 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

COPYRIGHT

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

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
    Creative Commons Attribution-Share Alike 2.5 Australia License.
    http://creativecommons.org/licenses/by-sa/2.5/au/

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. 
    The returned data includes all the metadata about the segmented linestring:
    - 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.

COPYRIGHT

    (c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener
    (c) 2008-2018 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.

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (c) 2012-2019 by TheSpatialDBAdvisor/Simon Greener
  LICENSE
    Creative Commons Attribution-Share Alike 2.5 Australia License.
    http://creativecommons.org/licenses/by-sa/2.5/au/

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

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

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener