TABLE OF CONTENTS


TOOLS/Tokenizer (2012) [ Methods ]

[ Top ] [ Methods ]

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

TOOLS/Tokenizer (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

    Tokenizer - Splits any string into tokens based on provided separators.

SYNOPSIS

    Function Tokenizer (
       @p_string     varchar(max),
       @p_separators varchar(254)
     )
     Returns @varchar_table TABLE 
     (
       token varchar(MAX),
     ) 

EXAMPLE

    SELECT t.token
      FROM [$(owner)].[Tokenizer]('LINESTRING(0 0,1 1)',' ,()') as t
    GO
    token
    ----------
    LINESTRING
    0
    0
    1
    1

DESCRIPTION

    Supplied a string and a list of separators this function returns resultant tokens as a table collection.

INPUTS

    @p_string     (varchar max) - Any non-null string.
    @p_separators (varchar 254) - List of separators eg '(),'

RESULT

    Table (Array) of Tokens
      token (varchar MAX) - Token defined by separators

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

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

TOOLS/Tokenizer (2008) [ Methods ]

[ Top ] [ Methods ]

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

EDITOR/STUpdateN (2012) [ Methods ]

[ Top ] [ Methods ]

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

EDITOR/STUpdate (2012) [ Methods ]

[ Top ] [ Methods ]

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

CONVERSION/STToGeography (2008) [ Methods ]

[ Top ] [ Methods ]

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

CONVERSION/STToGeometry (2012) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STTileGeom (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

    STTileGeom -- Covers envelope of supplied goemetry with a mesh of tiles of size TileX and TileY.

SYNOPSIS

    Function STTileGeom (
               @p_geometry geometry,
               @p_TileX float,
               @p_TileY float,
             )
     Returns @table table
    (
      col  Int,
      row  Int,
      geom geometry
    )

USAGE

    SELECT t.col, t.row, t.geom.STAsText() as geom
      FROM [$(owner)].[STTileGeom] (
             geometry::STGeomFromText('POLYGON((100 100, 900 100, 900 900, 100 900, 100 100))',0),
             400,200) 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))

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.

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.

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

AUTHOR

    Simon Greener

HISTORY

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

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

COGO/STTiler (2012) [ Methods ]

[ Top ] [ Methods ]

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_srid      int,
               @p_out_table nvarchar(128),
               @p_geography Int = 1
             )

USAGE

    EXEC [$(owner)].[STTiler] 0, 0, 1000, 1000, 250, 250, 0, 'dbo.GridLL', 0;
    GO
    SELECT COUNT(*) as tableCount FROM [$(owner)].[GridLL];
    GO

    tableCount
    ----------
    16

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.

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 uppre righ X/Longitude ordinate.
    @p_ur_y         (float) -- Spatial Extent's uppre righ 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_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.

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

AUTHOR

    Simon Greener

HISTORY

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

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

COGO/STTileXY (2008) [ Methods ]

[ Top ] [ Methods ]

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_srid  int = 0
             )
     Returns @table table
    (
      col  Int,
      row  Int,
      geom geometry
    )

USAGE

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

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.

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

AUTHOR

    Simon Greener

HISTORY

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

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

EDITOR/STSwapOrdinates (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

    STSwapOrdinates -- Allows for swapping ordinate pairs in a geometry.

SYNOPSIS

    Function STSwapOrdinates (
               @p_geometry geometry,
               @p_swap     varchar(2) = 'XY'
             )
     Returns geometry 

USAGE

    SELECT [$(owner)].[STSwapOrdinates] (
             geometry::STPointFromText('POINT(143.282374 20.293874)',4326),
             'XY'
           ).AsTextZM() as correctedOrdinates;
    correctedOrdinates
    POINT (20.293874 143.282374)

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;

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - August 2009 - Original TSQL Coding for SQL Spatial.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

INSPECT/STStartPoint (2008) [ Methods ]

[ Top ] [ Methods ]

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

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

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

EDITOR/STSetZ (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

    STSetZ -- Function that adds or updates (replaces) Z value of supplied geometry point.

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_point.
    The updated coordinate's XY ordinates are rounded to @p_round_xy number of decimal digits of precision.
    The updated coordinate's ZM ordinates are rounded to @p_round_ZM number of decimal digits of precision.

INPUTS

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

AFFINE/STScale (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

    STScale -- Function which moves a shape a supplied delta X and delta Y.

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

    SELECT 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.32,-7 4.48)'

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

AFFINE/STRotate (2008) [ Methods ]

[ Top ] [ Methods ]

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, [dbo].[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, [dbo].[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
                   [dbo].[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

EDITOR/STReverse (2012) [ Methods ]

[ Top ] [ Methods ]

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

EDITOR/STRemoveCollinearPoints (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

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

SYNOPSIS

    Function [dbo].[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

CONVERSION/STPointGeomAsText (2008) [ Methods ]

[ Top ] [ Methods ]

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

TOOLS/STPointAsText (2008) [ Methods ]

[ Top ] [ Methods ]

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)

USAGE

    SELECT geometry::STGeomFromText (
             'POINT (' 
             +
             STPointAsText (
               0.1,0.2,0.3,0.41,
                 3,  3,  2,   1)
             )
             +
             ')',
             0).AsTextZM() as point
    # point
    '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

GEOPROCESSING/STParallelSegment (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

    STParallelSegment -- Creates a line at a fixed offset from the input 2 point LineString or 3 point CircularString.

SYNOPSIS

    Function STParallelSegment (
               @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)].STParallelSegment(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

GEOPROCESSING/STParallel (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

    STParallel -- Creates a line at a fixed offset from the input line.

SYNOPSIS

    Function STParallel (
               @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.
    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 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 simple linestrings, circular strings and compoundCurves (not multilinestrings).

INPUTS

    @p_linestring (geometry) - Must be a 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.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

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.

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.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

INSPECT/STNumRings (2012) [ Methods ]

[ Top ] [ Methods ]

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

INSPECT/STNumDims (2012) [ Methods ]

[ Top ] [ Methods ]

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

AFFINE/STMove (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

    STMove -- Function which moves a shape using the supplied delta X, Y, Z amd M.

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

SORT/ST_Morton (2008) [ Methods ]

[ Top ] [ Methods ]

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

SORT/STMorton (2008) [ Methods ]

[ Top ] [ Methods ]

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

MBR/STGeography2MBR (2008) [ Methods ]

[ Top ] [ Methods ]

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

MBR/STGeometry2MBR (2008) [ Methods ]

[ Top ] [ Methods ]

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

MBR/STMBR2Geography (2008) [ Methods ]

[ Top ] [ Methods ]

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

MBR/STMBR2Geometry (2008) [ Methods ]

[ Top ] [ Methods ]

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

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

EDITOR/STMakeLine (2008) [ Methods ]

[ Top ] [ Methods ]

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

EDITOR/STMakeLineFromGeometryCollection (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

    STMakeLineFromGeometryCollection -- Creates a linestring from supplied GeometryCollection geometry.

SYNOPSIS

    Function STMakeLineFromGeometryCollection (
               @p_points geometry
             )
     Returns geometry 

USAGE

    SELECT [$(owner)].STMakeLineFromGeometryCollection(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 -- 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

EDITOR/STMakeLineFromMultiPoint (2008) [ Methods ]

[ Top ] [ Methods ]

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/

EDITOR/STMakePoint (2008) [ Methods ]

[ Top ] [ Methods ]

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/

EDITOR/STIsCompound (2012) [ Methods ]

[ Top ] [ Methods ]

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

EDITOR/STInsertN (2012) [ Methods ]

[ Top ] [ Methods ]

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 

USAGE

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

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.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

EDITOR/STInsertN (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STGreatCircleBearing (2008) [ Methods ]

[ Top ] [ Methods ]

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].[dbo].[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

COGO/STGreatCircleDistance (2008) [ Methods ]

[ Top ] [ Methods ]

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].[dbo].[STGreatCircleDistance](0,0,45,45,6378137,298.257223563) as Great_Circle_Distance
    union all
    select [GISDB].[dbo].[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

COGO/STFindLineIntersection (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STFindLineIntersectionBySegment (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STFindLineIntersectionDetails (2008) [ Methods ]

[ Top ] [ Methods ]

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,1).STAsText() as geom,
           [$(cogoowner)].[STFindLineIntersectionDetails](first_segment,second_segment) as reason
      from (select b.offset,
                   [$(owner)].[STParallelSegment](                                                   a.geom,b.offset,8,8) as first_segment,
                   [$(owner)].[STParallelSegment](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

GEOPROCESSING/STFilterRings (2008) [ Methods ]

[ Top ] [ Methods ]

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

GEOPROCESSING/STDumpRings (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

    STDumpRings -- Dumps the rings of a CurvePolygon, Polygon or MultiPolygon

SYNOPSIS

    Function STDumpRings(
               @p_geometry geometry
             )
     Returns @rings TABLE
     (
       pid  integer,
       rid  integer,
       geom geometry
     )  

DESCRIPTION

    This function allows a user to extract the subelements of the supplied polygon including all compound element descriptions.
    This function is a wrapper over STExtract.

INPUTS

    @p_geometry (geometry) - CurvePolygon, Polygon or MultiPolygon geometry object.

NOTES

    Depends on STExtract.

EXAMPLE

    SELECT t.*
     FROM [$(owner)].[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

RESULT

    Array of subelements:
    pid  - Polygon Identifier (for multigeomemtry objects, the individual high level geometry objects it describes).
    rid  - Ring 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

GEOPROCESSING/STExtract (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

    STExtract -- Extracts the subelements that describe the input geometry.

SYNOPSIS

    Function 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 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 [dbo].[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 [dbo].[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 [dbo].[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

GEOPROCESSING/STExtractPolygon (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

    STExtractPolygon -- Extracts polygons from GeometryCollection

SYNOPSIS

    Function 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, [dbo].[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

GEOPROCESSING/STExtract (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

    STExtract -- Extracts the subelements that describe the input geometry.

SYNOPSIS

    Function 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

EDITOR/STExtend (2008) [ Methods ]

[ Top ] [ Methods ]

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

EDITOR/STReduce (2008) [ Methods ]

[ Top ] [ Methods ]

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

GEOPROCESSING/STExplode (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

    STExplode -- STExplode is a wrapper function over STExtract with sub_element parameter set to 1 

SYNOPSIS

    Function STExplode(
               @p_geometry geometry,
             )
     Returns @geometries TABLE 
     (
       gid  integer,
       sid  integer,
       geom geometry
     )  

DESCRIPTION

    This function calls STExtract with @p_sub_geom set to 1.
    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 [dbo].[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

INSPECT/STEquals (2012) [ Methods ]

[ Top ] [ Methods ]

NAME

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

NOTES

    Supports Linestrings with CircularString elements.

INPUTS

    @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

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - January 2018 - Original Coding.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

INSPECT/STEndPoint (2008) [ Methods ]

[ Top ] [ Methods ]

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

GEOPROCESSING/STDumpPoints (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

   STDumpPoints - Dumps all vertices of supplied geometry object to ordered array.

SYNOPSIS

   Function STDumpPoints (
       @p_geometry  geometry 
    )
    Returns @Points Table (
      pid  int,
      sid  int,
      rid  int,
      x    float,  
      y    float,
      z    float,
      m    float
    )  

EXAMPLE

    SELECT e.[sid], 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
    sid rid pid    x    y    z    m
    --- --- --- ---- ---- ---- ----
      1   1   1  200  200 NULL NULL
      1   1   2  400  200 NULL NULL
      1   1   3  400  400 NULL NULL
      1   1   4  200  400 NULL NULL
      1   1   5  200  200 NULL NULL
      2   1   1    0    0 NULL NULL
      2   1   2  100    0 NULL NULL
      2   1   3  100  100 NULL NULL
      2   1   4    0  100 NULL NULL
      2   1   5    0    0 NULL NULL
      2   2   1   40   40 NULL NULL
      2   2   2   60   40 NULL NULL
      2   2   3   60   60 NULL NULL
      2   2   4   40   60 NULL NULL
      2   2   5   40   40 NULL NULL

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 :
     pid (int)   - Point identifier with element/subelement (1 to Number of Points in element).
     sid (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

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - January 2008 - Original coding.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

GEOPROCESSING/STVertices (2008) [ Methods ]

[ Top ] [ Methods ]

NAME

   STVertices - Dumps all vertices of supplied geometry object to ordered array.

SYNOPSIS

   Function STVertices (
       @p_geometry  geometry 
    )
    Returns @Points Table (
      pid  int,
      sid  int,
      rid  int,
      x    float,  
      y    float,
      z    float,
      m    float
    )  

EXAMPLE

    SELECT e.[sid], e.[rid], e.[pid], e.[x], e.[y], e.[z], e.[m]
      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
    sid rid pid    x    y    z    m
    --- --- --- ---- ---- ---- ----
      1   1   1  200  200 NULL NULL
      1   1   2  400  200 NULL NULL
      1   1   3  400  400 NULL NULL
      1   1   4  200  400 NULL NULL
      1   1   5  200  200 NULL NULL
      2   1   1    0    0 NULL NULL
      2   1   2  100    0 NULL NULL
      2   1   3  100  100 NULL NULL
      2   1   4    0  100 NULL NULL
      2   1   5    0    0 NULL NULL
      2   2   1   40   40 NULL NULL
      2   2   2   60   40 NULL NULL
      2   2   3   60   60 NULL NULL
      2   2   4   40   60 NULL NULL
      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 STDumpPoints

INPUTS

    @p_geometry (geometry) - Any non-point geometry object

RESULT

    Table (Array) of Points :
     pid (int)   - Point identifier with element/subelement (1 to Number of Points in element).
     sid (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

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - January 2008 - Original coding.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

INSPECT/STDetermine (2008) [ Methods ]

[ Top ] [ Methods ]

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 [dbo].[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 [dbo].[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 [dbo].[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 [dbo].[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
  LICENSE
    Creative Commons Attribution-Share Alike 2.5 Australia License.
    http://creativecommons.org/licenses/by-sa/2.5/au/

EDITOR/STDeleteN (2008) [ Methods ]

[ Top ] [ Methods ]

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

EDITOR/STDelete (2008) [ Methods ]

[ Top ] [ Methods ]

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

INSPECT/STCoordDim (2012) [ Methods ]

[ Top ] [ Methods ]

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

INSPECT/STCoordDim (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STArcToChordSeparation (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STCircle2Polygon (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STComputeArcLength (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STComputeChordLength (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STComputeLengthToMidPoint (2012) [ Methods ]

[ Top ] [ Methods ]

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

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

COGO/STCreateCircle (2012) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STCrossProductLength (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STDotProduct (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STFindAngleBetween (2012) [ Methods ]

[ Top ] [ Methods ]

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

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.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

COGO/STFindCircleFromArc (2012) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STFindDeflectionAngle (2012) [ Methods ]

[ Top ] [ Methods ]

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

    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 deflection angle from the first line to the second
    in the direction of the first 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

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.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

COGO/STisClockwiseAngle (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STisClockwiseArc (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STOptimalCircleSegments (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STSubtendedAngle (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STSubtendedAngleByPoint (2008) [ Methods ]

[ Top ] [ Methods ]

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

GEOPROCESSING/STCentroid [ Methods ]

[ Top ] [ Methods ]

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

GEOPROCESSING/STCentroid_A (2008) [ Methods ]

[ Top ] [ Methods ]

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

GEOPROCESSING/STCentroid_L (2008) [ Methods ]

[ Top ] [ Methods ]

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) - Point or Multipoint geometry object.
    @p_multiLineStringMode (int) - Mode controlling centroid(s) generation when @p_geometry is MultiLineString.
    @p_position_as_ratio (float) - Position along individual 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.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - July 2008 - Original coding.

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

GEOPROCESSING/STCentroid_P (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STBearing (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STBearingBetweenPoints (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STPointFromBearingAndDistance (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/STPointFromCOGO (2008) [ Methods ]

[ Top ] [ Methods ]

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 float 

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.
    @p_SRID is the SRID of the 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

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

COGO/STAddSegmentByCOGO (2008) [ Methods ]

[ Top ] [ Methods ]

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

TOOLS/Generate_Series (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/DD2DMS (2008) [ Methods ]

[ Top ] [ Methods ]

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

COGO/DMS2DD (2008) [ Methods ]

[ Top ] [ Methods ]

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/

COGO/DMSS2DD (2008) [ Methods ]

[ Top ] [ Methods ]

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/

COGO/STNormalizeBearing (2008) [ Methods ]

[ Top ] [ Methods ]

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