- 1. COGO/DD2DMS
- 2. COGO/DMS2DD
- 3. COGO/DMSS2DD
- 4. COGO/ST_BearingBetweenPoints
- 5. COGO/ST_NormalizeBearing
- 6. COGO/ST_PointFromBearingAndDistance
- 7. COGO/ST_PointFromCOGO
- 8. CONVERSION/ST_Line ( lseg, pSrid )
- 9. CONVERSION/ST_Line ( path, pSrid )
- 10. CONVERSION/ST_Point ( point, pSrid )
- 11. CONVERSION/ST_Polygon ( box, pSrid )
- 12. CONVERSION/ST_Polygon ( circle, iSegment, pSrid, iRound )
- 13. CONVERSION/ST_Polygon ( polygon, pSrid )
- 14. CONVERSION/ST_ToGeography
- 15. CONVERSION/ST_ToGeometry
- 16. EDITOR/ST_Append
- 17. EDITOR/ST_Append (p_node_distance)
- 18. EDITOR/ST_Extend (2008)
- 19. EDITOR/ST_Reduce
- 20. EDITOR/STMakeCircularString
- 21. GEOPROCESSING/ST_Average
- 22. GEOPROCESSING/ST_Chop
- 23. GEOPROCESSING/ST_InsideLine
- 24. GEOPROCESSING/ST_isCollinear
- 25. GEOPROCESSING/ST_LineInsidePolygon
- 26. GEOPROCESSING/ST_OneSidedBuffer
- 27. GEOPROCESSING/ST_Segmentize
- 28. INSPECT/ST_CircularStringN
- 29. INSPECT/ST_Determine
- 30. INSPECT/ST_NumCircularStrings
- 31. INSPECT/ST_NumCurves
- 32. PROCESSING/Random_Between
- 33. PROCESSING/ST_ConnectLineStrings
- 34. PROCESSING/ST_Densify
- 35. PROCESSING/ST_Explode
- 36. PROCESSING/ST_Hilbert
- 37. PROCESSING/ST_Hilbert2Point
- 38. PROCESSING/ST_SmoothTile
- 39. PROPERTIES/ST_Centroid
- 40. PROPERTIES/ST_CurveN
- 41. PROPERTIES/ST_HasM
- 42. PROPERTIES/ST_HasZ
- 43. SORT/ST_Morton
- 44. T_GRID/ATTRIBUTES(T_MBR)
- 45. T_SEGMENT/ATTRIBUTES(T_Segment)
- 46. TESSELATION/ST_QuadTree
- 47. TILING/ST_GridFromPoint
- 48. TILING/ST_GridFromXY
- 49. TOOLS/ST_Equals
- 50. TOOLS/ST_Equals (p_distance)
- 51. TOOLS/ST_IsGeographicSrid
- 52. WRAPPER/ST_AddMeasure (geography float8 float8)
- 53. WRAPPER/ST_AddPoint (geography geography integer)
- 54. WRAPPER/ST_AddPoint (geography geography)
- 55. WRAPPER/ST_CoordDim (geography geography)
- 56. WRAPPER/ST_Dimension (geography)
- 57. WRAPPER/ST_EndPoint (geography)
- 58. WRAPPER/ST_ExteriorRing (geography)
- 59. WRAPPER/ST_GeometryN (geography integer)
- 60. WRAPPER/ST_GeometryType (geography)
- 61. WRAPPER/ST_InteriorRingN (geography integer)
- 62. WRAPPER/ST_IsClosed (geography)
- 63. WRAPPER/ST_IsEmpty (geography)
- 64. WRAPPER/ST_IsValid (geography integer)
- 65. WRAPPER/ST_IsValid (geography)
- 66. WRAPPER/ST_IsValidDetail (geography integer)
- 67. WRAPPER/ST_IsValidDetail (geography)
- 68. WRAPPER/ST_IsValidReason (geography integer)
- 69. WRAPPER/ST_IsValidReason (geography)
- 70. WRAPPER/ST_M (geography)
- 71. WRAPPER/ST_NPoints (geography)
- 72. WRAPPER/ST_NumGeometries (geography)
- 73. WRAPPER/ST_NumInteriorRings (geography)
- 74. WRAPPER/ST_NumPoints (geography)
- 75. WRAPPER/ST_PointN (geography integer)
- 76. WRAPPER/ST_Points (geography)
- 77. WRAPPER/ST_RemovePoint (geography integer)
- 78. WRAPPER/ST_SetPoint (geography integer geography)
- 79. WRAPPER/ST_SetPoint (geography)
- 80. WRAPPER/ST_StartPoint (geography)
- 81. WRAPPER/ST_X (geography)
- 82. WRAPPER/ST_Y (geography)
- 83. WRAPPER/ST_Z (geography)

[ Top ] [ Functions ]

NAME

DD2DMS-- Returns string equivalent of decimal degree numeric value.

SYNOPSIS

FunctionDD2DMS( dDecDeg in Number, pDegree in NChar default CHR(176), pMinute in NChar default '''', pSecond in NChar default '"' ) Return varchar2 Deterministic;

INPUTS

dDecDeg (Number) - Decimal degrees. pDegree (NChar) - Superscript degree value identifier eg ^ pMinute (NChar) - Superscript minute value identifier eg ' pSecond (NChar) - Superscript second value identifier eg "

RESULT

Decimal Degrees (NUMBER) - eg 22.16972222.

DESCRIPTION

This function converts a numeric decimal degree value into its textual whole-circle bearing equivalent.

EXAMPLE

select spdba.DD2DMS(15.8515065952945,'^','''','"') as dms; DMS 15^51'5.424" select spdba.DD2DMS(415.67845) as dms; dms 55°40'42.420"

AUTHOR

Simon Greener

HISTORY

Simon Greener - Jan 2013 - Original coding.

COPYRIGHT

(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

FunctionDMS2DD( p_dDeg in Integer, p_dMin in Integer, p_dSec in Float ) Returns Float

USAGE

SELECT spdba.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 ST_NormalizeBearing function.

INPUTS

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

RESULT

DecimalDegrees (float) : Decimal degrees equivalent value.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

FunctionDMSS2DD( p_strDegMinSec varchar ) Returns Float

USAGE

SELECT spdba.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 (varchar) : DD MM SS.SS description eg 43° 0'' 50.00"S

RESULT

DecimalDegrees (float) : Decimal degrees equivalent value.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

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

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

Function spdba.ST_BearingBetweenPoints( p_dE1 numeric, p_dN1 numeric, p_dE2 numeric, p_dN2 numeric ) Returns numeric

USAGE

SELECT spdba.ST_Bearing(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 (numeric) - X ordinate of start point. p_dN1 (numeric) - Y ordinate of start point. p_dE2 (numeric) - Z ordinate of start point. p_dN2 (numeric) - M ordinate of start point.

RESULT

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

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_NormalizeBearing-- Function ensures supplied bearing is between 0 and 360.

SYNOPSIS

Function spdba.ST_NormalizeBearing( p_bearing float ) Returns Float

USAGE

SELECT spdba.ST_NormalizeBearing(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 pl/pgSQL Coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

Function spdba.ST_PointFromBearingAndDistance( p_dStartE in float, p_dStartN in float, p_dBearing in float, p_dDistance in float, p_iSrid in integer ) Returns float

USAGE

SELECT ST_AsEWKT(spdba.ST_PointFromBearingAndDistance(0,0,45,100,0)) 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). p_Srid is the SRID of the supplied start point.

NOTES

Supports planar data only.

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_iSrid (int) - SRID associated with p_dStartE/p_dStartN.

RESULT

point (geometry) - Point

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original pl/pgSQL Coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

FunctionST_PointFromCOGO( @p_gStartPoint geometry, @p_dBearing float, @p_dDistance float ) Returns geometry

USAGE

SELECT ST_AsEWKT(ST_PointFromCOGO( ST_GeomFromEWKT('SRID=28355; POINT(0,0)'), 45.0, 100.0 ) ) 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). SRID of the returned geometry is the SRID supplied start point.

INPUTS

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

RESULT

point (geometry) - Point

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original pl/pgSQL coding.

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Line -- Converts the input Geometric lseg into a PostGIS linestring geometry.

SYNOPSIS

Function spdba.ST_Line ( pGeom lseg, pSrid integer ) Returns geometry

DESCRIPTION

This function takes a PostgreSQL native geometric lseg and converts it to a PostGIS linestring.

PARAMETERS

pGeom (lseg) - A geometric lseg pSrid (integer) - Spatial Reference Identifier

RESULT

A geometry linestring object.

EXAMPLE

SELECT ST_AsEWKT(spdba.ST_Line('(-43.1,147.1),(-43.2,147.2)'::lseg,4326)) as geom; geom SRID=4326;LINESTRING(147.1 -43.1,147.2 -43.2)

AUTHOR

Simon Greener

HISTORY

Simon Greener - May 2024 - Original coding.

COPYRIGHT

(c) 2008-2024 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Line -- Converts the input Geometric path into a PostGIS linestring geometry.

SYNOPSIS

Function spdba.ST_Line ( pGeom path, pSrid integer ) Returns geometry

DESCRIPTION

This function takes a PostgreSQL native geometric path and converts it to a PostGIS linestring.

PARAMETERS

pGeom (path) - A geometric path pSrid (integer) - Spatial Reference Identifier

RESULT

A geometry linestring object.

EXAMPLE

SELECT ST_AsEWKT(spdba.ST_Line('(147.1,-43.2),(147.2,-43.2),(147.2,-43.1),(147.1,-43.1),(147.1,-43.2)'::path,4326)) as geom; geom SRID=4326;LINESTRING(147.1 -43.2,147.2 -43.2,147.2 -43.1,147.1 -43.1,147.1 -43.2)

AUTHOR

Simon Greener

HISTORY

Simon Greener - May 2024 - Original coding.

COPYRIGHT

(c) 2008-2024 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Point -- Converts the input Geometric point into a PostGIS point

SYNOPSIS

Function spdba.ST_Point ( pGeom point, pSrid integer ) Returns geometry

DESCRIPTION

This function takes a PostgreSQL native geometric point and converts it to a PostGIS geometry.

PARAMETERS

pGeom (point) - A geometric point pSrid (integer) - Spatial Reference Identifier

RESULT

A geometry point object.

EXAMPLE

SELECT ST_AsEWKT(spdba.ST_Point('(147.5,-43.1)'::point,4326)) as geom; geom POINT(147.5 -43.1)

AUTHOR

Simon Greener

HISTORY

Simon Greener - May 2024 - Original coding.

COPYRIGHT

(c) 2008-2024 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Polygon -- Converts the input Geometric box into a PostGIS polygon geometry.

SYNOPSIS

Function spdba.ST_Polygon ( pGeom box, pSrid integer ) Returns geometry

DESCRIPTION

This function takes a PostgreSQL native geometric box and converts it to a PostGIS polygon.

PARAMETERS

pGeom (box) - A geometric box pSrid (integer) - Spatial Reference Identifier

RESULT

A geometry polygon object.

EXAMPLE

SELECT ST_AsEWKT(spdba.ST_Polygon('(-43.1,147.1),(-43.2,147.2)'::box,4226)) as geom; geom POLYGON((147.1 -43.2,147.1 -43.1,147.2 -43.1,147.2 -43.2,147.1 -43.2))

AUTHOR

Simon Greener

HISTORY

Simon Greener - May 2024 - Original coding.

COPYRIGHT

(c) 2008-2024 by TheSpatialDBAdvisor/Simon Greener

pSrid, iRound ) [ Functions ]

[ Top ] [ Functions ]

NAME

ST_Polygon -- Converts the input Geometric circle into a PostGIS polygon geometry.

SYNOPSIS

Function spdba.ST_Polygon ( pGeom circle, iSegments integer,pSridinteger, iRound integer ) Returns geometry

DESCRIPTION

This function takes a PostgreSQL native geometric circle and converts it to a PostGIS polygon.

PARAMETERS

pGeom (circle) - A geometric circle iSegments (integer) - The number of stroked segments tracing the circumference of the circlepSrid(integer) - Spatial Reference Identifier iRound (integer) - Decimal digits of precision for computed vertices.

RESULT

A geometry polygon object.

EXAMPLE

SELECT ST_AsEWKT(spdba.ST_Polygon('<(100,200),10>'::circle, 8, 0, 3)) as geom UNION ALL SELECT ST_AsEWKT(spdba.ST_Polygon('<(100,100),100>'::circle, 8, 0, 3)) as geom; geom POLYGON((110 200,107.071 207.071,100 210,92.929 207.071,90 200,92.929 192.929,100 190,107.071 192.929,110 200)) POLYGON((200 100,170.711 170.711,100 200,29.289 170.711,0 100,29.289 29.289,100 0,170.711 29.289,200 100))

AUTHOR

Simon Greener

HISTORY

Simon Greener - May 2024 - Original coding.

COPYRIGHT

(c) 2008-2024 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Polygon -- Converts the input Geometric polygon into a PostGIS polygon geometry.

SYNOPSIS

Function spdba.ST_Polygon ( pGeom polygon, pSrid integer ) Returns geometry

DESCRIPTION

This function takes a PostgreSQL native geometric path and converts it to a PostGIS polygon.

PARAMETERS

pGeom (polygon) - A geometric polygon pSrid (integer) - Spatial Reference Identifier

RESULT

A geometry polygon object.

EXAMPLE

SELECT 'Closed' as isClosed, ST_AsEWKT(spdba.ST_Polygon('(147.1,-43.2),(147.2,-43.2),(147.2,-43.1),(147.1,-43.1),(147.1,-43.2)'::polygon,4326)) as geom UNION ALL SELECT 'Open' as isClosed, ST_AsEWKT(spdba.ST_Polygon('(147.1,-43.2),(147.2,-43.2),(147.2,-43.1),(147.1,-43.1)'::polygon,4326)) as geom isClosed geom Closed SRID=4326;POLYGON((147.1 -43.2,147.2 -43.2,147.2 -43.1,147.1 -43.1,147.1 -43.2,147.1 -43.2)) Open SRID=4326;POLYGON((147.1 -43.2,147.2 -43.2,147.2 -43.1,147.1 -43.1,147.1 -43.2))

AUTHOR

Simon Greener

HISTORY

Simon Greener - May 2024 - Original coding.

COPYRIGHT

(c) 2008-2024 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_ToGeography- Given a geometry object returns a geography object.

SYNOPSIS

Function spdba.ST_ToGeography( p_geom geometry ) Returns geography

DESCRIPTION

Given valid geometry object this function converts it to a geography object.

PARAMETERS

p_geom (geometry) - A valid geometry object.

RESULT

geography (geography) - Geography object.

EXAMPLE

WITH data AS ( SELECT ST_GeomFromText('LINESTRING(147.234 -43.2345, 148.234 -43.2345)',4326) as geom ) SELECT ST_Length(spdba.ST_ToGeography(geom)) as geog_length, ST_Length(a.geom) as geom_length FROM data as a; geog_length geom_length 81229.7069403744 1

AUTHOR

Simon Greener

HISTORY

Simon Greener - Aug 2012 - Initial SQL Server 20012 implementation Simon Greener - Aug 2018 - Converted to PostGIS (trivial)

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_ToGeometry- Given a geography object returns a geometry object.

SYNOPSIS

Function spdba.ST_ToGeometry( p_geog geography ) Returns geometry

DESCRIPTION

Given valid geography object this function converts it to a geometry object

PARAMETERS

p_geog (geography) - A valid geographic object.

RESULT

geometry (geometry) - Geometry object.

EXAMPLE

WITH data AS ( SELECT ST_GeogFromText('SRID=4326;LINESTRING(147.234 -43.2345, 148.234 -43.2345)') as geog ) SELECT ST_Length(spdba.ST_ToGeometry(a.geog)) as geom_length, ST_Length(a.geog) as geog_length FROM data as a; geom_length geog_length 1 81229.7069403744

AUTHOR

Simon Greener

HISTORY

Simon Greener - Aug 20012 - Initial SQL Server 20012 implementation

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Append-- Appends two linestrings not just start-end but end-start, start-start and end-end.

SYNOPSIS

Function spdba.ST_Append( p_linestring1 geometry, p_linestring2 geometry, p_round_xy integer := 3, p_round_zm integer := 2 ) Returns geometry

DESCRIPTION

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

NOTES

Supports Linestrings with CircularString elements.

PARAMETERS

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

EXAMPLE

select ST_AsText( spdba.ST_Append( 'LINESTRING(0 0,1 1)'::geometry, 'LINESTRING(1 1,2 2)'::geometry, 3, 1 ) ) as aLine; aLine LINESTRING(0 0,1 1,2 2) select ST_AsText( spdba.ST_Append( 'MULTILINESTRING((-1 -1,0 -0.001),(0 0,1.123 1))'::geometry, 'LINESTRING(-1 -1,-2 -2)'::geometry, 3, 1 ) ) as aLine; aLine MULTILINESTRING((0 -0.001,-1 -1,-2 -2),(0 0,1.123 1))

AUTHOR

Simon Greener

HISTORY

Simon Greener - October 2019 - Original Coding for PostgreSQL

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Append -- Appends two linestrings not just start-end but end-start, start-start and end-end. Point equality testing by distance.

SYNOPSIS

Function spdba.ST_Append ( p_linestring1 geometry, p_linestring2 geometry, p_node_distance float DEFAULT 0.001 ) Returns geometry

DESCRIPTION

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

NOTES

Supports Linestrings with CircularString elements.

INPUTS

p_linestring1 (geometry) - Linestring geometry possibly with elevation (Z) and measures (M). p_linestring2 (geometry) - Linestring geometry possibly with elevation (Z) and measures (M). p_node_distance (float) - Distance between ends of linestrings defining "Equals"

RESULT

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

SEE ALSO

ST_Append() with round_xy/zm values.

AUTHOR

Simon Greener

HISTORY

Simon Greener - October 2019 - Original Coding for PostgreSQL Simon Greener - June 2020 - Modified to create new function based on p_node_distance

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

Function spdba.ST_Extend ( 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 ST_Extend(geometry::ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),5.0,'START',1,2,1).AsTextZM() as extendedGeom; extendedGeom LINESTRING(-4.9 30.2,-3.6 31.5)

DESCRIPTION

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

NOTES

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

INPUTS

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

RESULT

linestring (geometry) - Input geometry extended as instructed.

AUTHOR

Simon Greener

HISTORY

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

COPYRIGHT

(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Reduce-- Function that shortens the supplied linestring at either its start or end (p_end) the required length.

SYNOPSIS

Function spdba.ST_Reduce( p_linestring geometry, p_reduction_length float, p_end varchar(5), p_round_xy integer = 3, p_round_zm integer = 2 ) Returns geometry

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.

PARAMETERS

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.

EXAMPLE

SELECT ST_AsText( spdba.ST_Reduce( ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0), 5.0, 'START', 2, 1 ) ) as reducedGeom; reducedGeom LINESTRING(-4.9 30.2,-3.6 31.5)

NOTES

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

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original TSQL Coding for SQL Server. Simon Greener - May 2020 - Ported to PostGIS.

COPYRIGHT

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

[ Top ] [ Functions ]

NAME

STMakeCircularString-- Creates a three point Circular linestring.

SYNOPSIS

FunctionSTMakeCircularString( p_start_point geometry, p_mid_point geometry, p_end_point geometry ) Returns geometry

DESCRIPTION

Function creates a three point Circular linestring from supplied start, mid and end points.

NOTES

If p_start_point, or p_mid_point, or p_end_point are null, a null result is returned. If p_start_point, or p_mid_point, or p_end_point have different SRIDS, a null result is returned. Z is returned if ALL points have Z ordinates and all values are equal. M is returned if ALL points have M ordinates.

INPUTS

p_start_point (geometry) - Not null start point. p_mid_point (geometry) - Not null start point. p_end_point (geometry) - Not null end point.

RESULT

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

EXAMPLE

select ST_AsText( spdba.ST_MakeCircularString( ST_GeomFromText('POINT (0 0)',0), ST_GeomFromText('POINT (1 1)',0), ST_GeomFromText('POINT (2 2)',0) ) ) as cLine; cLine LINESTRING(0 0,1 1,2 2) select ST_AsText( spdba.ST_MakeCircularString( ST_GeomFromText('POINT (0 0)',0), ST_GeomFromText('POINT (1 1)',0), ST_GeomFromText('POINT (2 0)',0) ) ); cLine CIRCULARSTRING(0 0,1 1,2 0) select ST_AsText( spdba.ST_MakeCircularString( ST_GeomFromText('POINT Z (0 0 1)',0), ST_GeomFromText('POINT Z (1 1 1)',0), ST_GeomFromText('POINT Z (2 0 1)',0) ) ); cLine CIRCULARSTRING Z (0 0 1,1 1 1,2 0 1) select ST_AsText( spdba.ST_MakeCircularString( ST_GeomFromText('POINT M (0 0 0)',0), ST_GeomFromText('POINT M (1 1 1.570796)',0), ST_GeomFromText('POINT M (2 0 3.141593)',0) ) ); cLine CIRCULARSTRING M (0 0 0,1 1 1.570796,2 0 3.141593) select ST_AsText( spdba.ST_MakeCircularString( ST_GeomFromText('POINT ZM (0 0 0 1.1)',0), ST_GeomFromText('POINT ZM (1 1 1.570796 1.1)',0), ST_GeomFromText('POINT ZM (2 0 3.141593 1.1)',0) ) ); cLine CIRCULARSTRING ZM (0 0 0 1.1,1 1 1.570796 1.1,2 0 3.141593 1.1) TODBEDONE If points are collinear (XY only), null is returned.

AUTHOR

Simon Greener

HISTORY

Simon Greener - October 2019 - Original Coding for PostGIS

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Average-- Averages 2 Points

SYNOPSIS

Function spdba.ST_Average( p_first_point in geometry, p_second_point in geometry ) RETURNS boolean

ARGUMENTS

p_first_point (geometry) -- point p_second_point (geometry) -- point

RESULT

point (geometry - Average of two points

DESCRIPTION

This function takes two points and averages the ordinates. If points have different ordinate dimensions, 2D point is returned.

EXAMPLE

select ST_AsText(spdba.ST_Average('POINT(-1 -1)'::geometry,'POINT(1 1)'::geometry)) as aPoint; aPoint POINT(0 0) select ST_AsText(spdba.ST_Average('POINTZ(-1 -1 1)'::geometry,'POINTZ(1 1 2)'::geometry)) as aPoint; aPoint POINT(0 0 1.5) select ST_AsText(spdba.ST_Average('POINTM(-1 -1 1)'::geometry,'POINTM(1 1 2)'::geometry)) as aPoint; aPoint POINT(0 0 1.5)

AUTHOR

Simon Greener

HISTORY

Simon Greener - April 2019, Original Coding

COPYRIGHT

(c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Chop-- Splits a (multi)linestring into individual linestrings.

SYNOPSIS

Function spdba.ST_Chop( p_geometry geometry, p_distance numeric, p_round_xy int default 3, -- For comparing ordinates of xy coordinates p_treat_as_one int default 0, -- Treat MultiLineString as single object if 1, otherwise break in to LineStrings if 0 p_mode int default 0 -- Processing mode: 0 returns all segments even if < p_distance; 1 causes segment < p_distance to be merged with previous segment. ) RETURNS SETOF spdba.T_Segment

PARAMETERS

p_geometry (geometry) -- LineString or MultiLineString. p_distance (numeric) -- Desired length of individual segmenets generated by function. p_round_xy (int) -- For comparing ordinates of xy coordinates. Result ordinates are not rounded (See ST_SnapToGrid). p_treat_as_one (int) -- Treat MultiLineString as single object if 1, otherwise break in to LineStrings if 0 p_mode (int) -- Processing mode: 0 returns all segments even if < p_distance; 1 causes segment < p_distance to be merged with previous segment.

RESULT

Set of LineStrings (set of geometry) -- Collection / Set of individual linestrings.

DESCRIPTION

This function chops/cuts the input geometry into linestring geometries (segments) of length p_distance. If p_treat_as_one is set to 1, and p_geometry is a MultiLineString, the segments are created by cutting up the MultiLineString as if it were a single linestring. This can mean that a returned segment cross from one linestring to the next thus returning it as a MultiLineString. If p_treat_as_one is set to 0, each LineString is chopped without reference to any other LineString. If the last segment to be returned is < p_distance and p_mode = 0 it is returned; if p_mode = 1 the segment is joined to the previous LineString such that the returned segment is > p_distance but < p_distance * 2.0

EXAMPLE

-- Chop MultiLineString's individual LineStrings last segment is balance of length (< p_distance) select (a.segs).id, ROUND(ST_Length((a.segs).segment)::numeric,3) as len, ST_AsText( ST_SnapToGrid((a.segs).segment,0.001) ) as geom from (select spdba.ST_Chop( ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'), 40.0, 3, 0, 0 ) as segs ) as a; id len geom text numeric geometry ---- ------- ------------------------------------------- 1 40.000 LINESTRING(0 0,40 0) 2 40.000 LINESTRING(40 0,80 0) 3 20.000 LINESTRING(80 0,100 0) 4 40.000 LINESTRING(200 0,212.649 37.947) 5 40.000 LINESTRING(212.649 37.947,225.298 75.895) 6 40.000 LINESTRING(225.298 75.895,237.947 113.842) 7 40.000 LINESTRING(237.947 113.842,250.596 151.789) 8 40.000 LINESTRING(250.596 151.789,263.246 189.737) 9 40.000 LINESTRING(263.246 189.737,275.895 227.684) 10 40.000 LINESTRING(275.895 227.684,288.544 265.631) 11 36.228 LINESTRING(288.544 265.631,300 300) -- Process individual LineStrings of the MultiLineString with last segment merge select (a.segs).id, ROUND(ST_Length((a.segs).segment)::numeric,3) as len, ST_AsText( ST_SnapToGrid((a.segs).segment,0.001) ) as geom from (select spdba.ST_Chop( ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'), 40.0, 3, 0, 1 ) as segs ) as a; id len geom text numeric geometry ----- ------- ------------------------------------------------------ 1 40.000 LINESTRING(0 0,40 0) 2 60.000 LINESTRING(40 0,80 0,100 0) 4 40.000 LINESTRING(200 0,212.649 37.947) 5 40.000 LINESTRING(212.649 37.947,225.298 75.895) 6 40.000 LINESTRING(225.298 75.895,237.947 113.842) 7 40.000 LINESTRING(237.947 113.842,250.596 151.789) 8 40.000 LINESTRING(250.596 151.789,263.246 189.737) 9 40.000 LINESTRING(263.246 189.737,275.895 227.684) 10 76.228 LINESTRING(275.895 227.684,288.544 265.631,300 300) -- Treat MultiLineString as one select (a.segs).id, ROUND(ST_Length((a.segs).segment)::numeric,3) as len, ST_AsText( ST_SnapToGrid((a.segs).segment,0.001) ) as geom from (select spdba.ST_Chop( ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'), 40.0, 3, 1, 1 ) as segs ) as a; id len geom text numeric geometry ----- ------- ------------------------------------------------------ 1 40.000 LINESTRING(0 0,40 0) 2 40.000 LINESTRING(40 0,80 0) 3 40.000 MULTILINESTRING((80 0,100 0),(200 0,206.325 18.974)) 4 40.000 LINESTRING(206.325 18.974,218.974 56.921) 5 40.000 LINESTRING(218.974 56.921,231.623 94.868) 6 40.000 LINESTRING(231.623 94.868,244.272 132.816) 7 40.000 LINESTRING(244.272 132.816,256.921 170.763) 8 40.000 LINESTRING(256.921 170.763,269.57 208.71) 9 40.000 LINESTRING(269.57 208.71,282.219 246.658) 10 56.228 LINESTRING(282.219 246.658,294.868 284.605,300 300)

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2018, Original Coding

COPYRIGHT

(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_InsideLine-- Generates a line that is fitted inside a polygon.

SYNOPSIS

CREATE OR REPLACE FUNCTION spdba.ST_insideLine( p_point in geometry, p_direction_start in numeric, p_direction_end in numeric default null, p_polygon in geometry, p_dIncrement in numeric default 5.0 ) RETURNS geometry

ARGUMENTS

p_point (geometry) -- Starting point which must fall inside p_polygon p_direction_start (numeric) -- Whole circle bearing in degrees defining direction of line from p_point to the start of the line p_direction_end (numeric) -- Whole circle bearing in degrees defining direction of line from p_point to the end of the line p_polygon (geometry) -- Polygon for which the inside line must be fitted. p_dIncrement (numeric) -- Line increment (distance) for incrementally extending a line, and testing line to find its boundary intersection point.

RESULT

line (geometry) -- Line at desired bearing which touches p_polygon at line start and end.

DESCRIPTION

This function creates a line that lies inside p_polygon but whose start and end points touch p_polygon's boundary. Line is generated from a starting point and two bearings Algorithm generates a line from supplied point to the line's start point by extending and testing the line by p_dIncrement until it finds a p_polygon boundary. After finding point for first half of line, the second line is generated using p_direction_end. If p_direction_end is null or the same as p_direction_start, a default direction of p_direction_start - 180.0 is used The algorithm uses a stepping approach: it first creates a line at p_direction_start for p_dIncrement distance. If the line does not touch a p_polygon boundary point it increases the line length by p_dIncrement and tests again. The stepping process continues until the line touches or crosses the boundary. Once the two halves are created, they are unioned together and the resulting line returned.

EXAMPLE

select ST_AsText( spdba.ST_insideLine( ST_SetSrid(ST_Point(82,60),28355), 0.0::numeric, 0.0::numeric, ST_GeomFromText('POLYGON((8.003 66.926, 11.164 70.086, 13.692 70.929, 19.171 70.929, 23.385 70.508, 26.546 70.297, 33.078 71.983, 36.871 74.301, 43.824 75.776, 51.199 75.986, 59.206 74.511, 62.788 71.772, 64.685 70.719, 73.535 71.351, 78.592 69.244, 83.649 64.187, 84.913 62.501, 86.178 57.022, 85.756 53.019, 85.124 49.226, 86.81 45.433, 87.863 40.376, 89.338 37.215, 89.338 32.58, 87.653 27.522, 83.438 18.462, 81.12 15.933, 74.799 17.619, 77.538 25.205, 80.067 30.472, 80.488 37.215, 78.381 41.219, 75.22 53.229, 72.06 60.394, 62.999 63.133, 52.463 65.451, 46.353 66.926, 37.714 63.344, 29.496 62.501, 20.646 61.447, 14.114 62.922, 9.899 61.447, 3.157 63.765, 3.367 64.187, 8.003 66.926))',28355), 5.0) ) as geom ; geom text LINESTRING(82 16.8931035375324,82 60,82 65.836)

NOTES

This is a simplistic approach that is likely to perform slowly.

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2018, Original Coding

COPYRIGHT

(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_isCollinear-- Checks three points are collinear

SYNOPSIS

CREATE OR REPLACE FUNCTION spdba.ST_insideLine( p_start_point in geometry, p_mid_point in geometry, p_end_point in geometry ) RETURNS boolean

ARGUMENTS

p_start_point (geometry) -- Starting point p_mid_point (geometry) -- Mid point p_end_point (geometry) -- End point

RESULT

true or false (boolean) -- Checks if three points are collinear.

DESCRIPTION

This function takes three points and determines if any combination of them forms a straight line (ie are collinear).

EXAMPLE

select spdba.ST_IsCollinear('POINT(0 0)'::geometry, 'POINT(-1 -1)'::geometry,'POINT(1 1)'::geometry); st_iscollinear true select spdba.ST_IsCollinear('POINT(-1 -1)'::geometry,'POINT(0 0)'::geometry,'POINT(1 1)'::geometry); st_iscollinear true select spdba.ST_IsCollinear('POINT(-1 -1)'::geometry,'POINT(0 0)'::geometry,'POINT(1 2)'::geometry); st_iscollinear false

AUTHOR

Simon Greener

HISTORY

Simon Greener - February 2019, Original Coding

COPYRIGHT

(c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_LineInsidePolygon-- Generates line and clips to polygon returning part p_point is within

SYNOPSIS

CREATE OR REPLACE FUNCTION spdba.ST_LineInsidePolygon( p_point in geometry, p_bearing in numeric, p_length in numeric, p_polygon in geometry ) RETURNS geometry

ARGUMENTS

p_point (geometry) -- Starting point which must fall inside p_polygon p_bearing (numeric) -- Whole circle bearing in degrees defining direction of line from p_point to the start of the line p_length (numeric) -- Line length. p_polygon (geometry) -- Polygon for which the inside line must be fitted.

RESULT

line (geometry) -- Line at desired bearing which is clipped to correct polygon part and touches p_polygon.

DESCRIPTION

This function creates a line that lies inside p_polygon but whose start and end points touch p_polygon's boundary. Line is generated from a starting point and bearings The algorithm creates line from supplied point the length of the diagonal of the ST_BoundingRectangle of the polygon. It then intersects the created line with the polygon to create a set of intersecting lines. Each line is processsed to find the one that contains the starting point; this line is returned.

EXAMPLE

select ST_AsText( spdba.ST_LineInsidePolygon( ST_SetSrid(ST_Point(82,60),28355), 0.0::numeric, 5.0, ST_GeomFromText('POLYGON((8.003 66.926, 11.164 70.086, 13.692 70.929, 19.171 70.929, 23.385 70.508, 26.546 70.297, 33.078 71.983, 36.871 74.301, 43.824 75.776, 51.199 75.986, 59.206 74.511, 62.788 71.772, 64.685 70.719, 73.535 71.351, 78.592 69.244, 83.649 64.187, 84.913 62.501, 86.178 57.022, 85.756 53.019, 85.124 49.226, 86.81 45.433, 87.863 40.376, 89.338 37.215, 89.338 32.58, 87.653 27.522, 83.438 18.462, 81.12 15.933, 74.799 17.619, 77.538 25.205, 80.067 30.472, 80.488 37.215, 78.381 41.219, 75.22 53.229, 72.06 60.394, 62.999 63.133, 52.463 65.451, 46.353 66.926, 37.714 63.344, 29.496 62.501, 20.646 61.447, 14.114 62.922, 9.899 61.447, 3.157 63.765, 3.367 64.187, 8.003 66.926))',28355) ) as geom ; geom text LINESTRING(82 16.8931035375324,82 60,82 65.836)

NOTES

This is a simplistic approach that is likely to perform slowly.

AUTHOR

Simon Greener

HISTORY

Simon Greener - February 2019, Original Coding

COPYRIGHT

(c) 2005-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

Function spdba.ST_OneSidedBuffer( p_line geometry, p_width float ) 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_width; 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. Where the linestring either crosses itself or starts and ends at the same point, the result may not be as expected. Only 'endcap=flat join=mitre' is supported for initial buffer.

NOTES

Supports circular strings and compoundCurves. Is a 2D function, so any Z and M ordinates are lost. Only supports single linestrings like LineString, CircularString and CompoundCurve. Is an input linestring is not Simple the results will most likely be wrong. Complex linestrings and large offset distances will most likely not return a clean result due to the nature of the algorithm TOBEDONE Better handling of non Simple linestrings. Create implementation based on linear offsets.

PARAMETERS

p_line (geometry) - Must be a linestring geometry. p_width (float) - if < 0 then left side buffer; if > 0 then right sided buffer.

RESULT

polygon (geometry) - Result of one sided buffering of a linestring.

EXAMPLE

SELECT ST_AsEWKT( ST_SnapToGrid( spdba.ST_OneSidedBuffer('LINESTRING(0 0, 10 10)'::geometry, 5.0 ), 0.001 ) ) as sbuf; sBuf POLYGON((10 10,13.536 6.464,3.536 -3.536,0 0,10 10))

AUTHOR

Simon Greener

HISTORY

Simon Greener - Jan 2013 - Original coding (Oracle). Simon Greener - Nov 2017 - Original coding for SQL Server. Simon Greener - Oct 2019 - Improvements to handle disppearing segments. Simon Greener - Oct 2019 - Port to PostgreSQL

COPYRIGHT

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

[ Top ] [ Functions ]

NAME

ST_Segmentize- Dumps all fundamental segments of supplied geometry object to ordered array.

SYNOPSIS

Function spdba.ST_Segmentize( p_geometry geometry ) RETURNs SETOF spdba.T_Segment IMMUTABLE

DESCRIPTION

This function segments the supplied geometry into 2-point linestrings or 3 point CircularStrings. The returned data includes all the metadata about the segmented linestring: - Segment identifiers (ie from 1 through n); - Start/Mid/End Coordinates as ordinates; - Length of vector. - Geometry representation of segment.

PARAMETERS

p_geometry (geometry) - Any non-point geometry object

RESULT

SETOF spdba.T_Segment IMMUTABLE

RESULT

SQL Select statement with the following. id (int) - Unique identifier starting at segment 1. max_id (int) - Id of last segment. hierarchy (varchar(max)) - Hierarchically organised STGeometryTypes from start of @p_geometry element_id (int) - Top level element identifier eg 1 for first polygon in multiPolygon. sub_element_id (int) - SubElement identifier of subelement of element with parts eg OuterRing of Polygon segment_id (int) - Unique identifier for all segments of a specific element. sx (float) - Start Point X Ordinate sy (float) - Start Point Y Ordinate sz (float) - Start Point Z Ordinate sm (float) - Start Point M Ordinate mx (float) - Mid Point X Ordinate (Only if CircularString) my (float) - Mid Point Y Ordinate (Only if CircularString) mz (float) - Mid Point Z Ordinate (Only if CircularString) mm (float) - Mid Point M Ordinate (Only if CircularString) ex (float) - End Point X Ordinate ey (float) - End Point Y Ordinate ez (float) - End Point Z Ordinate em (float) - End Point M Ordinate measure_range (float) - Measure range for each segment (if -ve descending measures) z_range (float) - Z range for each segment (if -ve descending measures) cumulative_measure(float) - Cumulative measure (could descend or ascend in value) segment_length (float) - Length of this segment in SRID units start_length (float) - Length at start vertex of segment cumulative_length (float) - Sum Length from start of @p_geometry closest_distance (float) - Distance from supplied @p_point to segment min_distance (float) - Distance from supplied @p_point to closest segment max_distance (float) - Distance from supplied @p_point to furthest segment segment (geometry) - Geometry representation of segment. next_segment (geometry) - Segment following on from current (ie id 2 when id 1)

EXAMPLE

SELECT e.id, e.element_id, e.subelement_id, e.segment_id, e.sx, e.sy, e.ex, e.ey, e.segment_length, ST_AsText(segment) as geomWKT FROM spdba.ST_Segmentize(ST_GeomFromText( 'MULTIPOLYGON( ((200 200, 400 200, 400 400, 200 400, 200 200)), ((0 0, 100 0, 100 100, 0 100, 0 0),(40 40,60 40,60 60,40 60,40 40)) )',0)) as e GO id element_id subelement_id segment_id sx sy ex ey length geomWKT -- ---------- ------------- ---------- --- --- --- --- ------ ----------------------------- 1 1 1 1 200 200 400 200 200 LINESTRING (200 200, 400 200) 2 1 1 2 400 200 400 400 200 LINESTRING (400 200, 400 400) 3 1 1 3 400 400 200 400 200 LINESTRING (400 400, 200 400) 4 1 1 4 200 400 200 200 200 LINESTRING (200 400, 200 200) 5 2 1 1 0 0 100 0 100 LINESTRING (0 0, 100 0) 6 2 1 2 100 0 100 100 100 LINESTRING (100 0, 100 100) 7 2 1 3 100 100 0 100 100 LINESTRING (100 100, 0 100) 8 2 1 4 0 100 0 0 100 LINESTRING (0 100, 0 0) 9 2 2 1 40 40 60 40 20 LINESTRING (40 40, 60 40) 10 2 2 2 60 40 60 60 20 LINESTRING (60 40, 60 60) 11 2 2 3 60 60 40 60 20 LINESTRING (60 60, 40 60) 12 2 2 4 40 60 40 40 20 LINESTRING (40 60, 40 40)

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2008, Original Coding; January 2017 - Support for Circular Curve objects and subobjects.

COPYRIGHT

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

[ Top ] [ Functions ]

NAME

ST_CircularStringN-- Extracts CircularString from input CircularString that has more than one CircularArc in it.

SYNOPSIS

Function spdba.ST_CircularStringN( p_geometry geometry, p_stringN ) Returns geometry

DESCRIPTION

A CircularString can have more than one string encoded within it. For example if a circularString has 3 points it only has one circularString in it. If a circularString has 5 points then it has two CircularStrings in it (Point 3 if end of first and start of second). This function extracts each string but it is checked for validity before being returned. If the string is invalid (collinear) null is returned by spdba.ST_NumCircularStrings

INPUTS

p_geometry (geometry) -- CircularString p_stringN (integer) -- CircularString element within p_geometry 1..NumCircularStrings

RESULT

circularSting (geometry) -- Circular String described by 3 points. NOTE Uses spdba.ST_NumCircularStrings

EXAMPLE

with data as ( select ST_GeomFromText('CIRCULARSTRING (0 0, 0 4,3 6.3246, 5 5, 6 3, 5 0,0 0)',0) as p_geometry ) SELECT NumStrings as curveN, ST_AsText(spdba.ST_CircularStringN(a.p_geometry, NumStrings)) as cString FROM data as a cross apply generate_series(1,spdba.ST_NumCircularStrings(p_geometry),1) as NumStrings; GO CurveN cString 1 CIRCULARSTRING (0 0, 0 4, 3 6.3246) 2 CIRCULARSTRING (3 6.3246, 5 5, 6 3) 3 CIRCULARSTRING (6 3, 5 0, 0 0)

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2019 - Ported from SQL Server TSQL

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Determine- Determines all possible spatial relations between two geometry instances.

SYNOPSIS

Function spdba.ST_Determine( p_geometry1 geometry, p_geometry2 geometry ) Return varchar(500)

DESCRIPTION

Compares the first geometry against the second using all the instance comparison methods, returning a comma separated string containing tokens representing each method: STContains -> CONTAINS. Methods and returned Strings are: STDisjoint -> DISJOINT STEquals -> EQUALS STContains -> CONTAINS STCrosses -> CROSSES STOverlaps -> OVERLAPS STTouches -> TOUCHES STIntersects -> INTERSECTS So if two polygons overlap each othre, a string containing "OVERLAPS" will be returned (see Example). Or is a point lies inside a polygon the "CONTAINS" relationship would be returned (see Example); Two lines that meet at a start/end point will return "TOUCHES". Note that in all the cases above, "INTERSECTS" is always returned as a catch-all relationship: it always appears at the end of the returned string. If two geometry objects are equal or disjoint a string containing only "EQUALS" or "DISJOINT" is returned.

INPUTS

p_geometry1 (geometry) - Non-null geometry instance. p_geometry2 (geometry) - Non-null geometry instance.

RESULT

relations found (varchar) - Comma separated string containing tokens representing each method: STContains -> CONTAINS.

EXAMPLE

Select spdba.ST_Determine( ST_GeomFromText('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), ST_GeomFromText('POLYGON ((-175.0 0.0, 100.0 0.0, 0.0 75.0, 100.0 75.0, 100.0 200.0, 200.0 325.0, 200.0 525.0, -175.0 525.0, -175.0 0.0))',0) ) as relations; go relations --------- OVERLAPS Select spdba.ST_Determine( ST_GeomFromText('LINESTRING (100.0 0.0, 400.0 0.0)',0), ST_GeomFromText('LINESTRING (90.0 0.0, 100.0 0.0)',0) ) as relations; go relations --------- TOUCHES Select spdba.ST_Determine( ST_GeomFromText('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) , ST_PointFromText('POINT (250 150)',0) ) as relations; go relations --------- CONTAINS Select spdba.ST_Determine( ST_PointFromText('POINT (250 150)',0), ST_PointFromText('POINT (250 150)',0) ) as relations; go relations --------- EQUALS

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2018 - Original coding. Simon Greener - March 2020 - Port to PostgreSQL

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_NumCircularStrings-- Returns number of CircularString elements in provided CircularString

SYNOPSIS

Function spdba.ST_NumCircularStrings( p_geometry geometry ) Returns integer

DESCRIPTION

A CircularString can have more than one 3-point string encoded within it. For example if a circularString has 3 points it only has one circularString in it. If a circularString has 5 points then it has two CircularStrings in it (Point 3 if end of first and start of second). This function counts the number of individual CircularStrings in p_geometry .

INPUTS

p_geometry (geometry) -- CircularString

RESULT

NumCircularStrings (integer) -- Number of 3-point CircularStrings within p_geometry.

EXAMPLE

WITH data AS ( SELECT ST_GeomFromText('CIRCULARSTRING (0 0, 0 4,3 6.3246, 5 5, 6 3, 5 0,0 0)',0) as cGeom ) SELECT spdba.ST_NumCircularStrings(a.cGeom) as numStrings FROM data as a numStrings 3

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2019 - Ported to SQL Server TSQL from PostgreSQL

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_NumCurves-- Returns number of CircularString elements in provided geometry

SYNOPSIS

FunctionST_NumCurves( p_geometry geometry p_count_strings boolean default true ) Returns integer

DESCRIPTION

A CircularString can appear: - On its own; - As an element in a CompoundCurve or other geometry type. In addition a CircularString can have more than one 3-point string encoded within it. - For example if a circularString has 3 points it only has one circularString in it. - If a circularString has 5 points then it has two CircularStrings in it (Point 3 if end of first and start of second). This function counts: - The number of individual CircularStrings in p_geometry (if p_count_strings is false); - The number of CircularStrings within any CircularString within p_geometry if (p_count_strings is true).

INPUTS

p_geometry (geometry) -- CircularString, CompoundCurve, GeometryCollection (with CircularStrings) p_count_strings (boolean) -- If true, the function counts the number of CircularStrings within a CircularString, otherwise counts CircularStrings individually.

RESULT

NumCurves (integer) -- Number of CircularStrings within p_geometry with number within each CircularString if requested

EXAMPLE

SELECT spdba.ST_NumCurves( ST_GeomFromText('COMPOUNDCURVE( CIRCULARSTRING(4 2, 2 4, 0 2), CIRCULARSTRING(0 2,0 4,3 6.3246, 5 5, 6 3, 5 0,0 0))',0), false ) as numCurves; numCurves 2 SELECT spdba.ST_NumCurves( ST_GeomFromText('GEOMETRYCOLLECTION( LINESTRING(0 0,0 2), CIRCULARSTRING(0 2, 2 0, 4 2), CIRCULARSTRING(4 2, 2 4, 0 2), CIRCULARSTRING(0 2,0 4,3 6.3246, 5 5, 6 3, 5 0,0 0))',0), false ) as numCurves; numCurves 3 WITH data AS ( SELECT ST_GeomFromText('CIRCULARSTRING (0 0, 0 4,3 6.3246, 5 5, 6 3, 5 0,0 0)',0) as cGeom ) SELECT spdba.ST_NumCurves(a.cGeom,false) as numCurves FROM data as a UNION ALL SELECT spdba.ST_NumCurves(a.cGeom,true) as numCurves FROM data as a; numCurves 1 3

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2019 - Original Coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

Random_Between-- This function generates a random number between a low and high value.

SYNOPSIS

Function spdba.Random_Between( low numeric, high numeric ) RETURNS numeric.

DESCRIPTION

There is no function in PostgreSQL that generates a random number between two values. This function generates a random number between the supplied low and high values. Useful for generating test data for a known X or Y ordinate range.

PARAMETERS

low (numeric) - Low value of range. high (numeric) - High value of range.

RESULT

Random number (numeric) between low and high values.

EXAMPLE

SELECT spdba.random_between(100,200) as rValue; rvalue 127.400556065142 SELECT f.gs as id, spdba.random_between(345643.0,5200456.2) FROM (SELECT generate_series(1,5,1) as gs ) as f; id random_between 1 2962987.41091414 2 2835034.06130052 3 1056657.66063199 4 4136028.7282584 5 2595646.03442658

AUTHOR

Simon Greener

HISTORY

Simon Greener - September 2019 - Original Coding.

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_ConnectLineStrings-- Connects LineStrings together from input multiGeometry

SYNOPSIS

Function spdba.ST_ConnectLineStrings( p_geometry geometry, p_node_distance ) Returns geometry

DESCRIPTION

An Alternate to PostGIS standard linestring stitching functions.

INPUTS

p_geometry (geometry) -- MultiLineString or GeometryCollection p_node_distance (float) -- Distance between start/end points to define equality

RESULT

(Multi)LineString.

EXAMPLE

AUTHOR

Simon Greener

HISTORY

Simon Greener - November 2019 - Original coding.

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Densify-- This function adds additional vertices/points into the supplied linestring/geometry

SYNOPSIS

Function spdba.ST_Densify( p_geometry geometry, p_distance numeric, p_mode int, p_round_xy int ) RETURNS geometry

DESCRIPTION

This function adds additional vertices/points into the supplied linestring/geometry. If operates according to the selected mode. 0 Fit as many segments of length p_length as possible with last segment length < p_distance allowed 1 Is to ensure no segment < p_distance; means last segment have segment > distance 2 Fits segments via binary chop with all segments being same length

PARAMETERS

p_geometry (geometry) - A ST_LineString or ST_MultiLineString p_distance (numeric) - Resulting distance between vertices/points in modified linestring. p_mode (int) - p_mode = 0 fit as many segments of length p_length as possible with last segment length < p_distance allowed - p_mode = 1 is ensure no segment < p_distance; means last segment have segment > distance - p_mode = 2 fits segments via binary chop with all segments being same length p_round_xy (int)

RESULT

Densified input (Multi)LineString.

EXAMPLE

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2010 - Original Coding. Simon Greener - January 2015 - Converted to PostGIS

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Explode-- This function breaks all multi-geometries down into single geometries.

SYNOPSIS

Function spdba.ST_Explode( p_geometry ) RETURNS SETOF geometry IMMUTABLE

DESCRIPTION

Any single geometry eg point, linestring or polygon. Any multi geometry is broken into its constituent parts and returns.

INPUTS

p_geometry - A Geometry of any type.

RESULT

One or more single/individual geometry objects.

EXAMPLE

select ST_AsText(e) from spdba.ST_Explode('MULTIPOINT((2 3 4),(2 3 4),(3 4 5))'::geometry) as e; POINT Z (2 3 4) POINT Z (2 3 4) POINT Z (3 4 5) select ST_AsText(e) from spdba.ST_Explode('GEOMETRYCOLLECTION(POINT(2 3 4),LINESTRING(2 3 4,3 4 5),POLYGON((326454.7 5455793.7 1,326621.3 5455813.7 2,326455.4 5455796.6 3,326454.7 5455793.7 4)))'::geometry) as e; POINT Z (2 3 4) LINESTRING Z (2 3 4,3 4 5) POLYGON Z ((326454.7 5455793.7 1,326621.3 5455813.7 2,326455.4 5455796.6 3,326454.7 5455793.7 4)) select ST_AsText(e) from spdba.ST_Explode('MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))'::geometry) As e; LINESTRING(0 0,1 1,1 2) LINESTRING(2 3,3 2,5 4) select ST_AsText(e) from spdba.ST_Explode('MULTIPOLYGON(((326454.7 5455793.7,326621.3 5455813.7,326455.4 5455796.6,326454.7 5455793.7)),((326771.6 5455831.6,326924.1 5455849.9,326901.9 5455874.2,326900.7 5455875.8,326888.9 5455867.3,326866 5455853.1,326862 5455851.2,326847.4 5455845.8,326827.7 5455841.2,326771.6 5455831.6)))'::geometry) as e; POLYGON((326454.7 5455793.7,326621.3 5455813.7,326455.4 5455796.6,326454.7 5455793.7)) POLYGON((326771.6 5455831.6,326924.1 5455849.9,326901.9 5455874.2,326900.7 5455875.8,326888.9 5455867.3,326866 5455853.1,326862 5455851.2,326847.4 5455845.8,326827.7 5455841.2,326771.6 5455831.6))

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2010 - Original Coding. Simon Greener - January 2015 - Converted to PostGIS

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Hilbert-- This function generates a hilbert key (cf ST_Morton) from the supplied parameters.

SYNOPSIS

Function spdba.ST_Hilbert( n int, x int, y int ) RETURNS int

DESCRIPTION

INPUTS

RESULT

EXAMPLE

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2019 - Original Coding.

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Hilbert2Point-- This function computes a hilbert value returning it as a point.

SYNOPSIS

Function spdba.ST_Hilbert2Point( n int, d int ) RETURNS geometry

USAGE

DESCRIPTION

NOTES

INPUTS

RESULT

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2010 - Original Coding. Simon Greener - January 2015 - Converted to PostGIS

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_SmoothTile- Smooths polygon created FROM raster to segment conversion

SYNOPSIS

FunctionST_SmoothTile( p_geometry geometry, p_precision integer default 3 ) Returns GEOMETRY

DESCRIPTION

A polygon created FROM raster to vector conversion, will have many vertices falling along the same straight line but whose sides will be "stepped". This function removes coincident points on a side so that a side will be defined by only a start and end vertex. The stepped sides will be replaced with vertices in the midpoint of each step so that any consistent stepped side will be replaced by a single line.

PARAMETERS

p_geometry (geometry) - LineString, MultiLineString, Polygon or MultiPolygon. p_precision (integer) - Precision of oridinates of any calculated points.

RESULT

geometry (geometry) - Grid shaped linestrings replaced by straight lines.

NOTES

Supports LineStrings, MultiLineStrings, Polygons and MultiPolygons.

EXAMPLE

SELECT ST_AsText( spdba.ST_SmoothTile( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,2 1,2 2,3 2,3 3,3 6,0 6,0 2)',0), 3 ) ) as geom; geom LINESTRING (0.5 0, 3 2.5, 3 4.5, 1.5 6, 0 4) SELECT ST_AsText( spdba.ST_SmoothTile( ST_GeomFromText('POLYGON ((12.5 2.5, 17.5 2.5, 17.5 7.5, 12.5 7.5, 12.5 2.5))',0), 3 ) ) as geom; geom POLYGON ((15 2.5, 17.5 5, 15 7.5, 12.5 5, 15 2.5)) SELECT ST_AsText( spdba.ST_SmoothTile( ST_GeomFromText('POLYGON ((0 0, 9 0, 9 9, 0 9, 0 0), (2.5 2.5, 2.5 7.5, 7.5 7.5, 7.5 2.5, 2.5 2.5))',0), 3 ) ) as geom; geom POLYGON ((4.5 0, 9 4.5, 4.5 9, 0 4.5, 4.5 0), (2.5 5, 5 7.5, 7.5 5, 5 2.5, 2.5 5)) SELECT ST_AsText( spdba.ST_SmoothTile( ST_GeomFromText('MULTIPOLYGON (((12.5 2.5, 17.5 2.5, 17.5 7.5, 12.5 7.5, 12.5 2.5)), ((10 0, 19 0, 19 9, 10 9, 10 0), (11 1, 11 8, 18 8, 18 1, 11 1)), ((0 0, 9 0, 9 9, 0 9, 0 0), (2.5 2.5, 2.5 7.5, 7.5 7.5, 7.5 2.5, 2.5 2.5)))',0), 3 ) ) as geom; geom LINESTRING (0.5 0, 3 2.5, 3 4.5, 1.5 6, 0 4)

AUTHOR

Simon Greener

HISTORY

Simon Greener - January 2013 - Original Coding (Oracle) Simon Greener - April 2020 - Port to PostGIS

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Centroid-- Generates multiple centroid points for a mulit-geometry.

SYNOPSIS

Function spdba.ST_Centroid( p_geom geometry ) Returns geometry

DESCRIPTION

The standardST_Centroidfunction does not generate multiple points for a multi-geometry object. This function explodes a multi-geometry object into its component geometries, generates a centroid for each part, and aggregates the result into a multipoint geometry.

PARAMETERS

p_geom (geometry) - A (multi)geometry object.

RESULT

A multi-point objects.

EXAMPLE

SELECT ST_AsText(spdba.ST_Centroid('LINESTRING Z (0 0 0,1 1 1)'::geometry)) as centroid; centroid MULTIPOINT(0.5 0.5) SELECT ST_AsText(spdba.ST_Centroid('MULTILINESTRING ((0 0,2 0),(10 0,12 0))'::geometry)) as centroid; centroid MULTIPOINT(1 0,11 0) SELECT ST_AsText(spdba.ST_Centroid('POLYGON ((0 0,10 0,10 10,0 10,0 0),(5 1,6 1,6 6,1 6,5 1))'::geometry)) as centroid; centroid MULTIPOINT(5.127450980392156 5.166666666666667) SELECT ST_AsText(spdba.ST_Centroid('MULTIPOLYGON (((0 0,10 0,10 10,0 10,0 0),(5 1,6 1,6 6,1 6,5 1)),((20 20,21 20,21 21,20 21,20 20)))'::geometry)) as centroid; centroid MULTIPOINT(5.127450980392156 5.166666666666667,20.5 20.5)

AUTHOR

Simon Greener

HISTORY

Simon Greener - July 2022 - Original coding.

COPYRIGHT

(c) 2008-2022 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_CurveN-- Given a geometry containing CircularStrings, and the position of a specific String, this function returns the CircularString at that position.

SYNOPSIS

Function spdba.ST_CurveN( p_geometry geometry, p_CurveN integer default 1 ) Returns geometry

DESCRIPTION

A geometry can contain zero, one or more CircularStrings. This function extracts a single circularString given a position or offset from 1. If a specific CircularString contains more than one 3 point CircularStrings, use spdba.ST_CircularStringN() on the result of this function

INPUTS

p_geometry (geometry) - Any geometry with CircularStrings p_CurveN (integer) - CircularString reference 1..ST_NumCurves.

RESULT

A CircularString or null

EXAMPLE

select ST_AsText( spdba.ST_CurveN( ST_GeomFromText('GEOMETRYCOLLECTION( LINESTRING(0 0,0 2), CIRCULARSTRING(0 2, 2 0, 4 2), CIRCULARSTRING(4 2, 2 4, 0 2), CIRCULARSTRING(0 2,0 4,3 6.3246, 5 5, 6 3, 5 0,0 0))',0), 1) ) as curve; curve CIRCULARSTRING(0 2,2 0,4 2)

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original coding.

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_HasM-- Determines if input geometry has a measure ordinate

SYNOPSIS

Function spdba.ST_HasM( p_geometry geometry ) Returns geometry

DESCRIPTION

Simple function like SQL Server Spatial's HasM instance method, that determines if the input geometry has a measure ordinate.

PARAMETERS

p_geometry (geometry) - Any valid geometry

RESULT

True if p_geometry has M ordinates; False otherwise.

EXAMPLE

SELECT spdba.ST_HasM('LINESTRING Z (0 0 0,1 1 1)'::geometry) as hasM; hasM false SELECT spdba.ST_HasM('LINESTRING M (0 0 0,1 1 1)'::geometry) as hasM; hasM true

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original coding.

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_HasZ-- Determines if input geometry has a measure ordinate

SYNOPSIS

Function spdba.ST_HasZ( p_geometry geometry ) Returns geometry

DESCRIPTION

Simple function like SQL Server Spatial's HasZ instance method, that determines if the input geometry has a measure ordinate.

PARAMETERS

p_geometry (geometry) - Any valid geometry

RESULT

True if p_geometry has Z ordinates; False otherwise.

EXAMPLE

SELECT spdba.ST_HasZ('LINESTRING Z (0 0 0,1 1 1)'::geometry) as hasZ; hasZ true SELECT spdba.ST_HasZ('LINESTRING M (0 0 0,1 1 1)'::geometry) as hasZ; hasZ false

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2019 - Original coding.

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Morton-- Function which creates a Morton (Space) Key from the supplied row and column reference.

SYNOPSIS

FUNCTION spdba.ST_Morton( p_col int4, p_row int4 ) RETURNS int4

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 (int4) - Grid Column Reference. p_row (int4) - Grid Row Reference.

RESULT

morton_key (int4) - Single integer morton key.

EXAMPLE

SELECT spdba.ST_Morton(10, 10) as mKey; # mKey 828

AUTHOR

Simon Greener

HISTORY

Professor David M. Mark - January 1984 - C; Simon Greener - December 2011 - Original Coding for SQL Server. Simon Greener - September 2012 - Port to PostGIS

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Variables ]

ATTRIBUTES

MinX -- X Ordinate of lower left (LL) corner of MBR. MinY -- Y Ordinate of lower left (LL) corner of MBR. MaxX -- X Ordinate of upper right (UR) corner of MBR. MaxY -- Y Ordinate of upper right (UR) corner of MBR.

SOURCE

CREATE TYPE spdba.T_Grid AS ( gcol int4, grow int4, geom geometry );

[ Top ] [ Variables ]

ATTRIBUTES

id (int) - Unique identifier starting at segment 1. element_id (int) - Top level element identifier eg 1 for first polygon in multiPolygon. sub_element_id (int) - SubElement identifier of subelement of element with parts eg OuterRing of Polygon segment_id (int) - Unique identifier for all segments of a specific element. sx (float) - Start Point X Ordinate sy (float) - Start Point Y Ordinate sz (float) - Start Point Z Ordinate sm (float) - Start Point M Ordinate mx (float) - Mid Point X Ordinate (Only if CircularString) my (float) - Mid Point Y Ordinate (Only if CircularString) mz (float) - Mid Point Z Ordinate (Only if CircularString) mm (float) - Mid Point M Ordinate (Only if CircularString) ex (float) - End Point X Ordinate ey (float) - End Point Y Ordinate ez (float) - End Point Z Ordinate em (float) - End Point M Ordinate segment_length (float) - Length of current segment cumulative_length (float) - Sum Length from start of @p_geometry segment (geometry) - LineString or CircularString representation of segment.

SOURCE

CREATE TYPE spdba.T_Segment AS ( id integer, element_id integer, sub_element_id integer, segment_id integer, sx float, sy float, sz float, sm float, mx float, my float, mz float, mm float, ex float, ey float, ez float, em float, cumulative_length float, segment_length float, segment geometry );

[ Top ] [ Functions ]

NAME

ST_QuadTree- Tesselates a two-dimensional space using a simple recursive quad tree grid.

SYNOPSIS

Procedure spdba.ST_QuadTree( p_SearchOwner IN Varchar(250), p_SearchTable IN VARCHAR(250), p_SearchColumn IN VARCHAR(250), p_LL IN geometry, p_UR IN geometry, p_TargetOwner IN Varchar(250), p_TargetTable IN varchar(250), p_TargetColumn IN VARCHAR(250), p_MaxQuadLevel IN integer, p_MaxCount IN integer )

DESCRIPTION

Recursively tesselates the two-dimensional space defined by p_SearchTable using a quad tree algorithm based on a set of criteria: 1. Depth of the Quad Tree; 2. Max number of features per quad (If number in a quad is > max number, quad is divided into four and each quad feature count is recomputed) The ouput polygons representing the quads that contain the data are written to the p_TagetTable with some specific fields

PARAMETERS

p_SearchOwner - Varchar(250) - Schema owner of p_SearchTable table p_SearchTable - VARCHAR(250) - Name of table in p_SchemaOwner that is to be quadded p_SearchColumn - VARCHAR(250) - Geometry column in p_SearchTable containing spatial data to be quadded. p_LL - geometry - Lower Left corner of extent of data in p_SearchColumn to be quadded (normally LL of extent of all data in p_searchColumn) p_UR - geometry - Upper Right corner of extent of data in p_SearchColumn to be quadded (normally UR of extent of all data in p_searchColumn) p_TargetOwner - Varchar(250) - Schema owner of p_TargetTable p_TargetTable - varchar(250) - Name of table that will be created and will hold the quad tree rectangles. p_TargetColumn - VARCHAR(250) - Name of geometry column in p_TargetTable that will hold resultant quad rectangles. p_MaxQuadLevel - integer - Maximum depth to recurse. p_MaxCount - integer - Max number of features per quad tree rectangle.

EXAMPLE

CALL spdba.ST_QuadTree( p_SearchOwner := 'data', p_SearchTable := 'valves', p_SearchColumn := 'geom', p_LL := ST_GeomFromEWKT('SRID=28356;POINT(515698.10890000034 6960213.1757)'), p_UR := ST_GeomFromEWKT('SRID=28356;POINT(519045.1911000004 6965208.943 )'), p_TargetOwner := 'data', p_TargetTable := 'valves_q', p_TargetColumn := 'geom', p_MaxQuadLevel := 8, p_MaxCount := 200 ); NOTE Ignores Z and only supports geometry objects not geography (separate procedure)

HISTORY

Simon Greener - March 2022 - Converted from Oracle PL/SQL

COPYRIGHT

Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. (http://creativecommons.org/licenses/by-sa/2.5/au/)

[ Top ] [ Functions ]

NAME

ST_GridFromPoint-- Computes a set (numTilesX/numTilesY) of tiles of size tilesSizeX/tileSizeY centred over p_point.

SYNOPSIS

Function spdba.ST_GridFromPoint( p_point geometry, p_TileSizeX numeric, p_TileSizeY numeric p_numtilesx integer, p_numtilesy integer, p_rotateX numeric, p_rotateY numeric, p_rotateAngle numeric, p_as_point boolean ) Returns geometry

DESCRIPTION

Creates a set (p_numTilesX/p_numTilesY) of rectangular tiles of size p_TileSizeX, p_TileSizeY over the supplied point. The tiles are centred over the supplied p_point. If p_rotatex/p_rotatey/p_rotateAngle are supplied, the resultant grid is rotated around p_rotateX and p_rotateY angle p_rotateAngle. if p_as_point is true, the centre point of each rectangular tile is returned, otherwise a polygon is returned.

PARAMETERS

p_point (geometry) - A Point geometry p_TileSizeX (numeric) - Size of a Tile's X dimension in real world units. p_TileSizeY (numeric) - Size of a Tile's Y dimension in real world units. p_numtilesx (integer) - Number of tiles in X dimension. p_numtilesy (integer) - Number of tiles in Y dimension. p_rotateX (numeric) - X ordinate of rotation point. p_rotateY (numeric) - Y ordinate of rotation point. p_rotateAngle (numeric) - Rotation angle expressed in decimal degrees between 0 and 360. p_as_point (boolean) - If true, the centre point of each rectangular tile is returned, otherwise each tile is returned as a polygon.

RESULT

gcol (int4) - Grid column index grow (int4) - Grid row index geom (geometry) - Polygon representing tile. NOTE If a point based grid is needed for a linestring or polygon geometry, compute its centroid and supply it to this function.

EXAMPLE

select gcol, grow, ST_AsText(geom) as tile from spdba.ST_GridFromPoint( ST_GeomFromText('POINT(511133.444 6972266.009)',28356), 0.03,0.03, 2,2, null,null,null, false); gcol grow geom 0 0 POLYGON((511133.444 6972266.009,511133.444 6972266.039,511133.474 6972266.039,511133.474 6972266.009,511133.444 6972266.009)) 0 1 POLYGON((511133.444 6972266.039,511133.444 6972266.069,511133.474 6972266.069,511133.474 6972266.039,511133.444 6972266.039)) 1 0 POLYGON((511133.474 6972266.009,511133.474 6972266.039,511133.504 6972266.039,511133.504 6972266.009,511133.474 6972266.009)) 1 1 POLYGON((511133.474 6972266.039,511133.474 6972266.069,511133.504 6972266.069,511133.504 6972266.039,511133.474 6972266.039)) select gcol, grow, ST_AsText(geom) as tile from spdba.ST_GridFromPoint( ST_GeomFromText('POINT(511133.444 6972266.009)',28356), 0.03,0.03, 2,2, 511133.444,6972266.009,45.0, true); gcol grow geom 0 0 POINT(511133.444 6972266.0302132) 0 1 POINT(511133.422786797 6972266.05142641) 1 0 POINT(511133.465213203 6972266.05142641) 1 1 POINT(511133.444 6972266.07263961)

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2017 - Original coding.

COPYRIGHT

(c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_GridFromXY-- Covers supplied envelope (LL/UR) with a mesh of tiles of size TileSizeX and TileSizeY.

SYNOPSIS

FunctionST_GridFromXY( p_xmin numeric, p_ymin numeric, p_xmax numeric, p_ymax numeric, p_TileSizeX numeric, p_TileSizeY numeric, p_rotateX numeric, p_rotateY numeric, p_rotateAngle numeric, p_srid int = 0 ) Returns SETOF spdba.T_Grid IMMUTABLE

DESCRIPTION

Function that takes a spatial extent (LL/UR), computes the number of tiles that cover it and the table array with its col/row reference. The number of columns and rows that cover this area is calculated using p_TileX/p_TileY which are in p_SRID units. All rows and columns are visited, with polygons being created that represent each tile. If p_rotatex/p_rotatey/p_rotateAngle are supplied, the resultant grid is rotated around p_rotateX and p_rotateY angle p_rotateAngle.

INPUTS

p_xmin (numeric) - Spatial Extent's lower left X ordinate. p_ymin (numeric) - Spatial Extent's lower left Y ordinate. p_xmax (numeric) - Spatial Extent's upper right X ordinate. p_ymax (numeric) - Spatial Extent's upper right Y ordinate. p_TileSizeX (numeric) - Size of a Tile's X dimension in real world units. p_TileSizeY (numeric) - Size of a Tile's Y dimension in real world units. p_rotateX (numeric) - X ordinate of rotation point. p_rotateY (numeric) - Y ordinate of rotation point. p_rotateAngle (numeric) - Rotation angle expressed in decimal degrees between 0 and 360. p_srid (int) - Geometric SRID.

RESULT

A table of spdba.T_Grid as follows is returned: ( gcol Int4 -- The column reference for a tile grow Int4 -- The row reference for a tile geom geometry -- The polygon geometry covering the area of the Tile. )

EXAMPLE

SELECT row_number() over (order by t.gcol, t.grow) as rid, t.gcol, t.grow, ST_AsText(t.geom) as geom FROM spdba.ST_GridFromXY(0,0,1000,1000,250,250,NULL,NULL,NULL,0) as t; GO rid col row geom --- --- --- ----------------------------------------------------------- 1 0 0 POLYGON ((0 0, 250 0, 250 250, 0 250, 0 0)) 2 0 1 POLYGON ((0 250, 250 250, 250 500, 0 500, 0 250)) 3 0 2 POLYGON ((0 500, 250 500, 250 750, 0 750, 0 500)) 4 0 3 POLYGON ((0 750, 250 750, 250 1000, 0 1000, 0 750)) 5 1 0 POLYGON ((250 0, 500 0, 500 250, 250 250, 250 0)) 6 1 1 POLYGON ((250 250, 500 250, 500 500, 250 500, 250 250)) 7 1 2 POLYGON ((250 500, 500 500, 500 750, 250 750, 250 500)) 8 1 3 POLYGON ((250 750, 500 750, 500 1000, 250 1000, 250 750)) 9 2 0 POLYGON ((500 0, 750 0, 750 250, 500 250, 500 0)) 10 2 1 POLYGON ((500 250, 750 250, 750 500, 500 500, 500 250)) 11 2 2 POLYGON ((500 500, 750 500, 750 750, 500 750, 500 500)) 12 2 3 POLYGON ((500 750, 750 750, 750 1000, 500 1000, 500 750)) 13 3 0 POLYGON ((750 0, 1000 0, 1000 250, 750 250, 750 0)) 14 3 1 POLYGON ((750 250, 1000 250, 1000 500, 750 500, 750 250)) 15 3 2 POLYGON ((750 500, 1000 500, 1000 750, 750 750, 750 500)) 16 3 3 POLYGON ((750 750, 1000 750, 1000 1000, 750 1000, 750 750))

AUTHOR

Simon Greener

HISTORY

Simon Greener - December 2011 - Original TSQL Coding for SQL Server. Simon Greener - October 2019 - Added rotation capability

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Equals-- Checks if all XYZM ordinates of two points are equal.

SYNOPSIS

FunctionST_Equals( 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 ST_Equal() function have no precision model other than ST_SnapToGrid of input points. This function checks XY but also Z and M. Decimal digits of precision are used to compare ordinates. 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.

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 - October 2019 - Original Coding For PostgreSQL

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_Equals -- Checks if all XYZM ordinates of two points are equal.

SYNOPSIS

Function ST_Equals ( p_point1 geometry, p_point2 geometry, p_distance float = 0.0 ) Returns geometry

DESCRIPTION

Standard ST_Equal() function have no precision model other than ST_SnapToGrid of input points. This function defines equals as being within the same distance of each other. The input geometry objects must conform to the following: 1. Both must be of geometry type point 2. Both must have the same SRID It is up to the caller to ensure these conditions are met.

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_distance (float) - Distance between points defining equals.

RESULT

1/0 (bit) - True is 1 and False is 0

AUTHOR

Simon Greener

HISTORY

Simon Greener - June 2020 - Original Coding For PostgreSQL

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_IsGeographicSrid-- Checks p_srid to see if exists in sys.spatial_reference_systems table (which holds geodetic SRIDS)

SYNOPSIS

FunctionST_IsGeographicSrid( p_srid int ) Returns int

DESCRIPTION

All geographic/geodetic SRIDs are stored in the sys.spatial_reference_systems table. This function checks to see if the supplied SRID is in that table. If it is, 1 is returned otherwise 0.

PARAMETERS

p_srid (int) - Srid value.

RESULT

Y/N (int) - 1 if True; 0 if False

EXAMPLE

SELECT spdba.ST_IsGeographicSrid(4283) as isGeographicSrid; isGeographicSrid ---------------- 1

AUTHOR

Simon Greener

HISTORY

Simon Greener - June 2018 - Original TSQL Coding for SQL Server. Simon Greener - February 2019 - Original pgPLSQL Coding for PostGIS

COPYRIGHT

(c) 2008-2019 by TheSpatialDBAdvisor/Simon Greener

[ Top ] [ Functions ]

NAME

ST_AddMeasure -- Adds measure ordinates to a geographic linestring. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_AddPoint -- Adds a geographic point to a geographic LineString before point <position> (0-based index). Third parameter can be omitted or set to -1 for appending. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_AddPoint -- Adds a point to a geography LineString. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_CoordDim -- Computes dimensionality of a single point ie XYZM->4 NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_Dimension -- Returns feature dimensionality of gepgraphy eg point is 0, line is 1, polygon is 3. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_EndPoint -- Returns the last point of a LINESTRING geography as a POINT.. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_ExteriorRing -- Returns Exterior Ring of polygon geography NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_GeometryN -- Returns specific element in a geography NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_GeometryType -- Returns Geometry Type String for a geography. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_InteriorRingN -- Returns nominated inner ring of a polygon geography NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_IsClosed -- Returns TRUE if LineString geography's start and end points are coincident. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_IsEmpty -- Returns TRUE if the geography is empty ie LINESTRING EMPTY NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_IsValid -- Returns true if the geography is well formed, allowed flags are documented in ST_IsValidDetail. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_IsValid -- Returns true if the geography is well formed. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_IsValidDetail -- Returns a valid_detail (valid,reason,location) row stating if a geometry is valid or not and if not valid, a reason why and a location where. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_IsValidDetail -- Returns a valid_detail (valid,reason,location) row stating if a geometry is valid or not and if not valid, a reason why and a location where. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_IsValidReason -- Returns a valid_detail (valid,reason,location) row stating if a geometry is valid or not and if not valid, a reason why and a location where. Flags documented in ST_IsValidDetail. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_IsValidReason -- Returns text stating if a geometry is valid or not an if not valid, a reason why. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_M -- Returns M Ordinate (normally linear reference) of a geographic point. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_NPoints -- Returns number of points in a geography independent of structure. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_NumGeometries -- Counts number of geographies in supplied geography NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_NumInteriorRings -- Returns number of inner rings in a polygon geography NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_NumPoints -- Returns number of points in a geography depends on structure. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_PointN -- Returns nominated point in a geography NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_Points -- Returns a MultiPoint containing all of the coordinates of a geography. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_RemovePoint -- Removes a point from a linestring geography, given its 0-based index. Useful for turning a closed ring into an open line string NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_SetPoint -- Replace a geography point of a geography linestring with a given point. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_SetPoint -- Returns a version of the given geometry with X (longitude) and Y (latitude) axis flipped. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_StartPoint -- Returns the first point of a LINESTRING geography as a POINT.. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_X -- Returns X Ordinate (longitude) of a geographic point. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_Y -- Returns Y Ordinate (latitude) of a geographic point. NOTE Installed in public schema.

[ Top ] [ Functions ]

NAME

ST_Z -- Returns Z Ordinate (normally elevation) of a geographic point. NOTE Installed in public schema.