- AFFINE/STMove (2008)
- AFFINE/STRotate (2008)
- AFFINE/STScale (2008)
- COGO/DD2DMS (2008)
- COGO/DMS2DD (2008)
- COGO/DMSS2DD (2008)
- COGO/STAddSegmentByCOGO (2008)
- COGO/STArcToChordSeparation (2008)
- COGO/STBearing (2008)
- COGO/STBearingBetweenPoints (2008)
- COGO/STCircle2Polygon (2008)
- COGO/STComputeArcLength (2008)
- COGO/STComputeChordLength (2008)
- COGO/STComputeLengthToMidPoint (2012)
- COGO/STComputeTangentPoint (2012)
- COGO/STCreateCircle (2012)
- COGO/STCrossProductLength (2008)
- COGO/STDotProduct (2008)
- COGO/STFindCircle (2008)
- COGO/STFindCircleFromArc (2012)
- COGO/STFindLineIntersection (2008)
- COGO/STFindPointBisector (2012)
- COGO/STFindSegmentIntersection (2008)
- COGO/STGreatCircleBearing (2008)
- COGO/STGreatCircleDistance (2008)
- COGO/STisClockwiseAngle (2008)
- COGO/STNormalizeBearing (2008)
- COGO/STOptimalCircleSegments (2008)
- COGO/STPointFromBearingAndDistance (2008)
- COGO/STPointFromCOGO (2008)
- COGO/STSubtendedAngle (2008)
- COGO/STSubtendedAngleDegrees (2008)
- CONVERSION/STToGeography (2008)
- CONVERSION/STToGeometry (2012)
- EDITOR/STAppend (2012)
- EDITOR/STDelete (2008)
- EDITOR/STDeleteN (2008)
- EDITOR/STExtend (2008)
- EDITOR/STInsertN (2008)
- EDITOR/STInsertN (2012)
- EDITOR/STIsCompound (2012)
- EDITOR/STMakeLine (2008)
- EDITOR/STMakeLineFromMultiPoint (2008)
- EDITOR/STMakePoint (2008)
- EDITOR/STReduce (2008)
- EDITOR/STReverse (2012)
- EDITOR/STSetZ (2012)
- EDITOR/STSwapOrdinates (2008)
- EDITOR/STUpdate (2012)
- EDITOR/STUpdateN (2012)
- GEOPROCESSING/STCentroid
- GEOPROCESSING/STCentroid_A (2008)
- GEOPROCESSING/STCentroid_L (2008)
- GEOPROCESSING/STCentroid_P (2008)
- GEOPROCESSING/STDumpPoints (2008)
- GEOPROCESSING/STDumpRings (2012)
- GEOPROCESSING/STExplode (2012)
- GEOPROCESSING/STExtract (2008)
- GEOPROCESSING/STExtract (2012)
- GEOPROCESSING/STExtractPolygon (2012)
- GEOPROCESSING/STFilterRings (2008)
- GEOPROCESSING/STOneSidedBuffer (2012)
- GEOPROCESSING/STParallel (2012)
- GEOPROCESSING/STParallelSegment (2012)
- GEOPROCESSING/STSquareBuffer (2012)
- GEOPROCESSING/STVertices (2008)
- INSPECT/STCoordDim (2008)
- INSPECT/STCoordDim (2012)
- INSPECT/STEndPoint (2008)
- INSPECT/STEquals (2012)
- INSPECT/STNumDims (2012)
- INSPECT/STNumRings (2012)
- INSPECT/STStartPoint (2008)
- MBR/STGeography2MBR (2008)
- MBR/STGeometry2MBR (2008)
- MBR/STMBR2Geography (2008)
- MBR/STMBR2Geometry (2008)
- SORT/ST_Morton (2008)
- SORT/STMorton (2008)
- TOOLS/Generate_Series (2008)
- TOOLS/STPointAsText (2008)
- TOOLS/STPointGeomAsText (2008)
- TOOLS/Tokenizer (2008)
- TOOLS/Tokenizer (2008)
- TOOLS/Tokenizer (2012)

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener LICENSE Creative Commons Attribution-Share Alike 2.5 Australia License. http://creativecommons.org/licenses/by-sa/2.5/au/

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener LICENSE Creative Commons Attribution-Share Alike 2.5 Australia License. http://creativecommons.org/licenses/by-sa/2.5/au/

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener LICENSE Creative Commons Attribution-Share Alike 2.5 Australia License. http://creativecommons.org/licenses/by-sa/2.5/au/

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener LICENSE Creative Commons Attribution-Share Alike 2.5 Australia License. http://creativecommons.org/licenses/by-sa/2.5/au/

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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

COPYRIGHT

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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 - 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

STMakeLine -- Creates a two point linestring.

SYNOPSIS

Function STMakeLine ( @p_start_point geometry, @p_end_point geometry, @p_round_xy int = 3, @p_round_zm int = 2 ) 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

[ 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

[ 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_round_xy int = 3, @p_round_zm int = 2, @p_srid int = 0 ) Returns geometry

USAGE

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

DESCRIPTION

Function creates a point from the supplied ordinates. 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

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. @p_round_xy (int) - XY ordinate precision. @p_round_zm (int) - ZM ordinate precision.

RESULT

Point - Geometry point.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

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

[ 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

[ 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

[ 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

[ 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

[ 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 @table table ( inter_x float, inter_y float, inter_x1 float, inter_y1 float, inter_x2 float, inter_y2 float )

USAGE

SELECT * FROM [$(cogoowner)].[STFindLineIntersection](0,0,10,10,0,10,10,0) as intersection Intersection

DESCRIPTION

Finds intersection point between two lines: 1. (inter_x, inter_y) is the point where the lines defined by the segments intersect. 2. (inter_x1, inter_y1) is the point on segment 1 that is closest to segment 2. 3. (inter_x2, inter_y2) is the point on segment 2 that is closest to segment 1. 4. If the lines are parallel, all returned coordinates are -1.79E+308 5. 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

inter_x (float) - X Ordinate of Intersection Point. inter_y (float) - Y Ordinate of Intersection Point. inter_x1 (float) - X Ordinate of Intersection Point on first line. inter_y1 (float) - Y Ordinate of Intersection Point on first line. inter_x2 (float) - X Ordinate of Intersection Point on second line. inter_y2 (float) - Y Ordinate of Intersection Point on second line.

NOTES

Assumes planar projection eg UTM.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

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

SYNOPSIS

Function STFindSegmentIntersection ( @p_line_segment_1 geometry, @p_line_segment_2 geometry ) Returns @table table ( iPoint geometry, iPoint1 geometry, iPoint2 geometry )

USAGE

SELECT * FROM [$(cogoowner)].[STFindSegmentIntersection] ( geometry::STLineFromText('LINESTRING(0 0,10 10)',0), geometry::STLineFromText('LINESTRING(0 10,10 0)',0) ) as Intersection GO Intersection

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

iPoint (geometry) - Intersection point. iPoint1 (geometry) - Intersection point on linestring 1. iPoint2 (geometry) - 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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

[ 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

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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. 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

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

SYNOPSIS

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

DESCRIPTION

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

NOTES

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

TODO

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

INPUTS

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

RESULT

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

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ 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

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Methods ]

NAME

FindPointBisector - Computes offset point on the bisector between two vertices.

SYNOPSIS

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

DESCRIPTION

Supplied with a second vector (p_next_line), this function computes the bisector between the two vectors and then creates a new vertex at a distance of p_offset from the intersection point. If an 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 vector. The returned vertex has its ordinate values rounded using the relevant decimal place values.

NOTES

Only supports SQL Server Spatial 2012 onwards as 2008 does not support CircularString

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_zm (int) - Number of decimal digits of precision for an Z or 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

[ 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 geometry ) 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

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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

[ Top ] [ Methods ]

NAME

STSubtendedAngleDegrees - Returns the angle (0-360) between three points.

SYNOPSIS

Function STSubtendedAngleDegrees ( @p_startX float, @p_startY float, @p_centreX float, @p_centreY float, @p_endX float, @p_endY float ) Returns float (angle in degrees)

DESCRIPTION

Supplied with three points, this function computes the angle from the first to the third subtended by the seconds. Result is degrees between 0 and 360.

NOTES

Assumes planar projection eg UTM.

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 between 0 and 360 degrees.

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original TSQL coding.

COPYRIGHT

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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. MultiLineStrings are supported but only when supplied as @p_linestring1. 4. Two MultiLineString geometries are not currently supported. 5. Both must have the same SRID 6. Both must have the same Coordinate Dimension ie XYZ=XYZ, XYM=XYM or XYZM=XYZM. 7. Both 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

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

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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-2017 by TheSpatialDBAdvisor/Simon Greener

[ 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 45°30'30.00"

DESCRIPTION

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

NOTES

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

INPUTS

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

RESULT

DMS (string) : Decimal degrees string equivalent.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

[ 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

[ 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

[ 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