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 (2008) [ 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 (2008) [ 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 (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

    Function 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 (2008) [ 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/STToGeography (2008) [ Functions ]

[ Top ] [ Functions ]

NAME

    STToGeography - Given a geometry object returns a geography object.

SYNOPSIS

    Function STToGeography (
       p_geom geometry,
       p_srid Int = NULL
     )
     Returns geography

EXAMPLE

    SELECT [$(owner)].[STToGeography] (
             geometry::STGeomFromText('LINESTRING(147.234 -43.2345, 148.234 -43.2345)',0),
             4326
           ).STAsText() 
     as geog
    GO
    geog
    LINESTRING(147.234 -43.2345, 148.234 -43.2345)

DESCRIPTION

    Given valid geometry object this function converts it to a geography object, assigns p_srid value and returns. 
    The p_srid value should be a valid geography SRID.
    Mainly useful for converting 2008 geometry objects with geographic data to 2012+ geography objects.  *  INPUTS
    p_geom (geometry) - A valid geometry object.
    p_srid      (int) - Valid geographic SRID. If null, uses SRID associated with supplied geometry.

RESULT

    geography (geography) - Geography object.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - Aug 20012 - Initial SQL Server 20012 implementation

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

9. CONVERSION/STToGeometry (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

    STToGeometry - Given a geography object returns a geometry object.

SYNOPSIS

    Function STToGeometry (
       p_geog geography,
       p_srid Int = NULL
     )
     Returns geometry

EXAMPLE

    SELECT [$(owner)].[STToGeometry] (
             geography::STGeomFromText('LINESTRING(147.234 -43.2345, 148.234 -43.2345)',4326),
             NULL
           ).STAsText() as geom;
    GO
    geom
    LINESTRING (147.234 -43.2345, 148.234 -43.2345)

DESCRIPTION

    Given valid geography object this function converts it to a geometry object, assigns p_srid value and returns. 
    The p_srid value should be a valid projected SRID.
    Mainly useful for converting 2012 geography objects to geometry equalivalent to be able to use functions only available for geometry. 

INPUTS

    p_geog (geography) - A valid geographic object.
    p_srid       (int) - Valid projected SRID. If null, uses SRID associated with supplied geography

RESULT

    geometry (geometry) - Geometry object.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - Aug 20012 - Initial SQL Server 20012 implementation

COPYRIGHT

    (c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener

10. EDITOR/ST_Extend (2008) [ 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

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

11. GEOPROCESSING/ST_Average [ Functions ]

[ Top ] [ Functions ]

NAME

    ST_Average -- Averages ordinates of 2 Points 

SYNOPSIS

    CREATE OR REPLACE 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

12. GEOPROCESSING/ST_Chop [ Functions ]

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

    CREATE OR REPLACE 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.segments 

ARGUMENTS

    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(
                     /* p_geometry     */ ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'),
                     /* p_distance     */ 40.0,
                     /* p_round_xy     */    3,
                     /* p_treat_as_one */    0,
                     /* p_mode         */    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(
                     /* p_geometry     */ ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'),
                     /* p_distance     */ 40.0,
                     /* p_round_xy     */    3,
                     /* p_treat_as_one */    0,
                     /* p_mode         */    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(
                     /* p_geometry     */ ST_GeomFromText('MULTILINESTRING((0 0,100 0),(200 0,300 300))'),
                     /* p_distance     */ 40.0,
                     /* p_round_xy     */    3,
                     /* p_treat_as_one */    1,
                     /* p_mode         */    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

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

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

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

16. TOOLS/STIsGeographicSrid (2012) [ Functions ]

[ Top ] [ Functions ]

NAME

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

SYNOPSIS

    Function STIsGeographicSrid (
               p_srid int 
             )
     Returns int 

USAGE

    SELECT [$(owner)].[STIsGeographicSrid](4283) as isGeographicSrid
    GO
    isGeographicSrid
    ----------------
    1

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.

INPUTS

    p_srid (int) - Srid value.

RESULT

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

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