TABLE OF CONTENTS

1. COGO/DD2DMS [ Functions ]

[ 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

2. COGO/DMS2DD [ Functions ]

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

    Function DMS2DD(
               p_dDeg 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

3. COGO/DMSS2DD [ Functions ]

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

    Function DMSS2DD(
               p_strDegMinSec 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/

4. COGO/ST_BearingBetweenPoints [ Functions ]

[ 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

5. COGO/ST_NormalizeBearing [ Functions ]

[ 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

6. COGO/ST_PointFromBearingAndDistance [ Functions ]

[ 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

7. COGO/ST_PointFromCOGO [ Functions ]

[ 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

8. CONVERSION/ST_ToGeography [ Functions ]

[ 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

9. CONVERSION/ST_ToGeometry [ Functions ]

[ 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

10. EDITOR/ST_Append [ Functions ]

[ 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

11. EDITOR/ST_Append (p_node_distance) [ Functions ]

[ 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

12. EDITOR/ST_Extend [ Functions ]

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

EXAMPLE

    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

AUTHOR

    Simon Greener

HISTORY

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

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

13. EDITOR/ST_Reduce [ Functions ]

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

14. EDITOR/STMakeCircularString [ Functions ]

[ 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

15. GEOPROCESSING/ST_Average [ Functions ]

[ 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

16. GEOPROCESSING/ST_Chop [ Functions ]

[ 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

17. GEOPROCESSING/ST_InsideLine [ Functions ]

[ 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

18. GEOPROCESSING/ST_isCollinear [ Functions ]

[ 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

19. GEOPROCESSING/ST_LineInsidePolygon [ Functions ]

[ 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

20. GEOPROCESSING/ST_OneSidedBuffer [ Functions ]

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

21. GEOPROCESSING/ST_Segmentize [ Functions ]

[ 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

22. INSPECT/ST_CircularStringN [ Functions ]

[ 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

23. INSPECT/ST_Determine [ Functions ]

[ 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

24. INSPECT/ST_NumCircularStrings [ Functions ]

[ 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

25. INSPECT/ST_NumCurves [ Functions ]

[ 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

26. PROCESSING/Random_Between [ Functions ]

[ 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

27. PROCESSING/ST_ConnectLineStrings [ Functions ]

[ 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

28. PROCESSING/ST_Densify [ Functions ]

[ 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

29. PROCESSING/ST_Explode [ Functions ]

[ 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

30. PROCESSING/ST_Hilbert [ Functions ]

[ 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

31. PROCESSING/ST_Hilbert2Point [ Functions ]

[ 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

32. PROCESSING/ST_SmoothTile [ Functions ]

[ 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

33. PROPERTIES/ST_CurveN [ Functions ]

[ 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

34. PROPERTIES/ST_HasM [ Functions ]

[ 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

35. PROPERTIES/ST_HasZ [ Functions ]

[ 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

36. SORT/ST_Morton [ Functions ]

[ 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

37. T_GRID/ATTRIBUTES(T_MBR) [ Variables ]

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

38. T_SEGMENT/ATTRIBUTES(T_Segment) [ Variables ]

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

39. TILING/ST_GridFromPoint [ Functions ]

[ 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

40. TILING/ST_GridFromXY [ Functions ]

[ 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

41. TILING/ST_GridGeometry [ Functions ]

[ Top ] [ Functions ]

NAME

    ST_GridGeometry -- Computes a set of tiles (a grid) over the MBR of the supplied geometry.

SYNOPSIS

    Function spdba.ST_GridGeometry (
               p_geometry  geometry,
               p_TileSizeX numeric,
               p_TileSizeY numeric
             )
     Returns geometry

DESCRIPTION

    Creates a set of rectangular grids over the supplied geometry usinmg dimensions supplied by p_TileSizeX, p_TileSizeY.

PARAMETERS

RESULT

    p_geometry  (geometry) - Any not-null geometry
    p_TileSizeX  (numeric) - Size of tile in X dimension.
    p_TileSizeY  (numeric) - Size of tile in Y dimension.

EXAMPLE

TODO

    Add rotation.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original coding.

COPYRIGHT

    (c) 2008-2020 by TheSpatialDBAdvisor/Simon Greener

42. TOOLS/ST_Equals [ Functions ]

[ 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

43. TOOLS/ST_Equals (p_distance) [ Functions ]

[ 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

44. TOOLS/ST_IsGeographicSrid [ Functions ]

[ 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

45. WRAPPER/ST_AddMeasure (geography float8 float8) [ Functions ]

[ Top ] [ Functions ]

NAME

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

46. WRAPPER/ST_AddPoint (geography geography integer) [ Functions ]

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

47. WRAPPER/ST_AddPoint (geography geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

48. WRAPPER/ST_CoordDim (geography geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

49. WRAPPER/ST_Dimension (geography) [ Functions ]

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

50. WRAPPER/ST_EndPoint (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

51. WRAPPER/ST_ExteriorRing (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

52. WRAPPER/ST_GeometryN (geography integer) [ Functions ]

[ Top ] [ Functions ]

NAME

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

53. WRAPPER/ST_GeometryType (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

54. WRAPPER/ST_InteriorRingN (geography integer) [ Functions ]

[ Top ] [ Functions ]

NAME

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

55. WRAPPER/ST_IsClosed (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

56. WRAPPER/ST_IsEmpty (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

57. WRAPPER/ST_IsValid (geography integer) [ Functions ]

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

58. WRAPPER/ST_IsValid (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

59. WRAPPER/ST_IsValidDetail (geography integer) [ Functions ]

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

60. WRAPPER/ST_IsValidDetail (geography) [ Functions ]

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

61. WRAPPER/ST_IsValidReason (geography integer) [ Functions ]

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

62. WRAPPER/ST_IsValidReason (geography) [ Functions ]

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

63. WRAPPER/ST_M (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

64. WRAPPER/ST_NPoints (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

65. WRAPPER/ST_NumGeometries (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

66. WRAPPER/ST_NumInteriorRings (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

67. WRAPPER/ST_NumPoints (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

68. WRAPPER/ST_PointN (geography integer) [ Functions ]

[ Top ] [ Functions ]

NAME

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

69. WRAPPER/ST_Points (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

70. WRAPPER/ST_RemovePoint (geography integer) [ Functions ]

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

71. WRAPPER/ST_SetPoint (geography integer geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

72. WRAPPER/ST_SetPoint (geography) [ Functions ]

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

73. WRAPPER/ST_StartPoint (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

74. WRAPPER/ST_X (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

75. WRAPPER/ST_Y (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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

76. WRAPPER/ST_Z (geography) [ Functions ]

[ Top ] [ Functions ]

NAME

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