NOTE
Ignores Z and only supports geometry objects not geography (separate procedure)
[ Top ] [ Functions ]
NAME
DD2DMS -- Returns string equivalent of decimal degree numeric value.
SYNOPSIS
Function DD2DMS(
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
Function DMS2DD(
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
Function DMSS2DD(
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
Function ST_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
[ Top ] [ Functions ]
NAME
ST_Polygon -- Converts the input Geometric circle into a PostGIS polygon geometry.
SYNOPSIS
Function spdba.ST_Polygon (
pGeom circle,
iSegments integer,
pSrid integer,
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 circle
pSrid (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
Function STMakeCircularString (
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
Function ST_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
Function ST_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 standard ST_Centroid function 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
Function ST_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
Function ST_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
Function ST_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.