TABLE OF CONTENTS

1. AFFINE/ST_Move (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_Move -- Function which moves a geometry the supplied delta X and delta Y.

SYNOPSIS

    Function ST_Move (
                p_geometry geometry,
                p_dX       double,
                p_dY       double,
                p_round_xy int 
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_Move(
                 ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),
                 -5.0, 30.1, 1
              )
           ) as movedGeom;

    # movedGeom
    'LINESTRING(-4.9 30.2,-3.6 31.5)'

DESCRIPTION

    Function that moves the supplied geometry's ordinates the supplied x and y deltas.
    The computed ordinates of the new geometry are rounded to p_round_xy number of decimal digits of precision.

INPUTS

    p_geometry (geometry) - supplied geometry of any type.
    p_dX       (double)   - X ordinate delta shift.
    p_dy       (double)   - Y ordinate delta shift.
    p_round_xy (int)      - Decimal degrees of precision to which calculated XY ordinates are rounded.

RESULT

    moved geom (geometry) - Input geometry moved by supplied X and Y ordinate deltas.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

2. AFFINE/ST_Rotate (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_Rotate -- Function which rotates a shape a supplied rotation point a provided number of degrees.

SYNOPSIS

    Function ST_Rotate ( 
                p_geometry geometry,
                p_rX       double,
                p_rY       double,
                p_angle    double
                p_round_xy int 
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_Rotate(
                 ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),
                 0.0, 0.0, 125, 2
              )
           ) as rotatedGeom;

    # rotatedGeom
    'LINESTRING(-0.14 -0.17,-1.95 -2.4)'

DESCRIPTION

    Function that rotates the supplied geometry around a supplied point.
    The rotation angle, p_angle, is in degrees between -360 and 360 degrees.
    The computed ordinates of the new geometry are rounded to p_round_xy number of decimal digits of precision.

INPUTS

    p_geometry   (geometry) - supplied geometry of any type.
    p_rX         (double)   - X ordinate of rotation point.
    p_rY         (double)   - Y ordinate of rotation point.
    p_angle      (double)   - Rotation angle specified in range degrees -360 to 360 degrees.
    p_round_xy   (int)      - Decimal degrees of precision to which calculated XY ordinates are rounded.

RESULT

    rotated geom (geometry) - Input geometry rotated p_angle degrees around supplied rotation point.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

3. AFFINE/ST_Scale (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_Scale -- Function which scales a geometry using the required XY ordinate input factors.

SYNOPSIS

    Function ST_Scale (
                p_geometry geometry,
                p_xFactor  double,
                p_yFactor  double,
                p_round_xy int 
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_Scale(
                 ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),
                 -5.0, 3.2, 3
              )
           ) as scaledGeom;

    # scaledGeom
    'LINESTRING(-0.5 0.32,-7 4.48)'

DESCRIPTION

    Function that scales the supplied geometry's ordinates using the supplied p_xFactor and p_yFactors .
    The computed ordinates of the new geometry are rounded to p_round_xy number of decimal digits of precision.

INPUTS

    p_geometry  (geometry) - supplied geometry of any type.
    p_xFactor   (double)   - X ordinate scale factor.
    p_yFactor   (double)   - Y ordinate scale factor.
    p_round_xy  (int)      - Decimal degrees of precision to which calculated XY ordinates are rounded.

RESULT

    scaled geom (geometry) - Input geometry scaled by supplied X and Y ordinate factor values.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

4. EDITOR/ST_DeleteN (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    STDeletePointN -- Function which deletes referenced coordinate from the supplied geometry.

SYNOPSIS

    Function STDeletePointN (
               p_geometry geometry,
               p_position int 
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_DeletePointN(
                 ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2,120 394.23)',0),
                 2
              )
           ) as deleteGeom; 

    # deleteGeom
    'LINESTRING(0.1 0.2,120 394.23)'

DESCRIPTION

    Function that removes a single, nominated, coordinates from the supplied geometry.
    The function does not process POINT or GEOMETRYCOLLECTION geometries.
    The point to be deleted is supplied as a single integer.
    The point number can be supplied as -1 (last number), or 1 to the total number of points in a WKT representation of the object.
    A point number does not refer to a specific point within a specific sub-geometry eg point number 1 in the 2nd interiorRing in a polygon object.

INPUTS

    p_geometry   (geometry) - supplied geometry of any type.
    p_position        (int) - Valid point number in geometry.

RESULT

    smaller geom (geometry) - Input geometry with required point deleted.

NOTES

    May throw error message ST_GeomFromText error if point deletion invalidates the geometry.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

5. EDITOR/ST_DeletePoints (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_DeletePoints -- Function which deletes one or more coordinates from the supplied geometry.

SYNOPSIS

    Function ST_DeletePoints (
                p_geometry   geometry,
                p_point_list text 
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_DeletePoints(
                 ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2,120 394.23)',0),
                 '2'
              )
           ) as deleteGeom; 

    # deleteGeom
    'LINESTRING(0.1 0.2,120 394.23)'

DESCRIPTION

    Function that removes one or more coordinates from the supplied geometry.
    The function does not process POINT or GEOMETRYCOLLECTION geometries.
    The list of points to be deleted is supplied as a comma separated string of point numbers.
    The point numbers are from 1 to the total number of points in a WKT representation of the object.
    Point numbers do not refer to specific points within a specific sub-geometry eg point number 1 in the 2nd interiorRing in a polygon object.

INPUTS

    p_geometry   (geometry) - supplied geometry of any type.
    p_point_line (text)     - Comma separated list of point numbers from 1 to the total number in a geometry's WKT representation.

RESULT

    smaller geom (geometry) - Input geometry with referenced points deleted. 
  NOTE
    May throw "Error Code: 3037. Invalid GIS data provided to function st_geometryfromtext." if point deletion invalidates the geometry.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

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

6. EDITOR/ST_InsertN (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_InsertN -- Function which inserts new coordinate (p_point) at position p_position in the supplied geometry.

SYNOPSIS

    Function ST_InsertN (
                p_geometry  geometry,
                p_point     geometry,
                p_position  int,
                p_round_xy  int 
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_InsertN(
                 ST_GeomFromText ('LINESTRING(0 0,2 2)',0),
                 ST_PointFromText('POINT(1 1)',0),
                 2,
                 1 ) ) as newGeom;

    # updatedGeom
    'LINESTRING(0 0,1 1,2 2)'

DESCRIPTION

    Function that inserts the coordinate p_point into position p_position in the supplied geometry.
    p_position Values:
      1. null -> defaults to 1;
      2. -1   -> maximum number of points ie ST_NumPoints(p_geometry) 
      3. Greater than ST_NumPoints(p_geometry) -> maximum number of points ie ST_NumPoints(p_geometry)
    The inserted coordinate's ordinates are rounded to p_round_xy number of decimal digits of precision.

INPUTS

    p_geometry  (geometry) - Supplied geometry of any type.
    p_point     (geometry) - Insert coordinate
    p_position  (int)      - Coordinate position in p_geometry: between 1 and STNumPoints.
    p_round_xy  (int)      - Decimal degrees of precision to which calculated XY ordinates are rounded.

RESULT

    larger geom (geometry) - Input geometry with coordinate inserted.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

7. EDITOR/ST_Reverse (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_Reverse -- Function which reverses the vertices of a linestring and parts/vertices of multilinestring.

SYNOPSIS

    Function ST_Reverse (
                p_geometry geometry 
             )
     Returns geometry

SYNOPSIS

    select id, action, geom 
      from (select 'Before' as action, id, ST_AsText(geom) as geom
              from (select 1 as id, ST_GeomFromText('LINESTRING(0 0, 10 0)',0) as geom
                    union all
                    select 2 as id, ST_GeomFromText('MULTILINESTRING((1 1,2 2), (3 3, 4 4))',0) as geom
                    union all
                    select 3 as id, ST_GeomFromText('MULTIPOINT((1 1),(2 2),(3 3),(4 4))',0) as geom
                    ) as data
           union all
           select 'After' as action, id, ST_AsText(ST_Reverse(geom)) as geom
             from (select 1 as id, ST_GeomFromText('LINESTRING(0 0, 10 0)',0) as geom
                   union all
                   select 2 as id, ST_GeomFromText('MULTILINESTRING((1 1,2 2), (3 3, 4 4))',0) as geom
                   union all
                   select 3 as id, ST_GeomFromText('MULTIPOINT((1 1),(2 2),(3 3),(4 4))',0) as geom
                  ) as data
           ) as f
    order by id, action desc;

   id,  action,   geom
   '1', 'Before', 'LINESTRING(0 0,10 0)'
   '1', 'After',  'LINESTRING(10 0,0 0)'
   '2', 'Before', 'MULTILINESTRING((1 1,2 2),(3 3,4 4))'
   '2', 'After',  'MULTILINESTRING((4 4,3 3),(2 2,1 1))'
   '3', 'Before', 'MULTIPOINT((1 1),(2 2),(3 3),(4 4))'
   '3', 'After',  'MULTIPOINT((4 4),(3 3),(2 2),(1 1))'

DESCRIPTION

    Function that reverses the coordinates of the supplied linestring.
    If the geometry is a multilinestring, the parts, and then their vertices are reversed.
    Supports reversal of MULTIPOINT geometries;
    The function does not process POINT, (MULTI)POLYGON or GEOMETRYCOLLECTION geometries.

INPUTS

    p_geometry    (geometry) - Supplied geometry of supported type.

RESULT

    reversed geom (geometry) -- Input geometry with parts and vertices reversed.

NOTES

    May throw "Error Code: 3037. Invalid GIS data provided to function st_geometryfromtext." if reversal invalidates the geometry.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

8. EDITOR/ST_SwapOrdinates (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_SwapOrdinates -- Allows for swapping ordinate pairs in a geometry.

SYNOPSIS

    Function ST_SwapOrdinates (
               p_geometry geometry,
               p_swap     varchar(2) = 'XY'
             )
     Returns geometry

USAGE

    SELECT [$(owner)].[ST_SwapOrdinates] (
             ST_PointFromText('POINT(143.282374 20.293874)',4326),
             'XY'
           ).AsTextZM() as correctedOrdinates;
    correctedOrdinates
    POINT (20.293874 143.282374)

DESCRIPTION

    Sometimes the ordinates of a geometry can be swapped such as latitude for X
    and Longitude for Y when it should be reversed. This function allows for the
    swapping of pairs of ordinates controlled by the p_swap parameter.
    Will support Z and M ordinate values when MySQL provides such support.

INPUTS

    p_geometry (geometry)  - Supplied geometry object.
    p_swap     (varchar 2) - The ordinate pair to swap: XY (only one supported), XZ, XM, YZ, YM or ZM

RESULT

    altered geom (geometry) - Changed Geometry;

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - January 2018 - Original Port to MYSQL

COPYRIGHT

    (c) 2008-2017 by TheSpatialDBAdvisor/Simon Greener

9. EDITOR/ST_Update (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_Update -- Function that updates (replaces) all geometry points that are equal to the supplied point with the replacement point.

SYNOPSIS

    Function ST_Update (
                p_geometry      geometry,
                p_point         geometry,
                p_replace_point geometry,
                p_round_xy      int
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_Update(
                 ST_GeomFromText ('POLYGON(0 0,10 0,10 10,0 10,0 0)',0),
                 ST_PointFromText('POINT(0 0)',0),
                 ST_PointFromText('POINT(1 1)',0),
                 1 
              ) 
           ) as newGeom;

    # updatedGeom
    'POLYGON(1 1,10 0,10 10,0 10,1 1)'

DESCRIPTION

    Function that updates all coordinates that equal p_point with the supplied p_replace_point.
    The p_round_xy value is used when comparing geometry point's XY ordinates to p_point's.
    The updated coordinate's ordinates are rounded to p_round_xy number of decimal digits of precision.

INPUTS

    p_geometry      (geometry) - supplied geometry of any type.
    p_point         (geometry) - Original coordinate to be replaced.
    p_replace_point (geometry) - Replacement coordinate
    p_round_xy      (int)      - Decimal degrees of precision to which calculated XY ordinates are rounded.

RESULT

    updated geom    (geometry) - Input geometry with one or more coordinate replaced.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

10. EDITOR/ST_UpdateN (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_UpdateN -- Function which updates the coordinate at position p_position in the supplied geometry.

SYNOPSIS

    Function ST_UpdateN (
                p_geometry      geometry,
                p_replace_point double,
                p_position      int,
                p_round_xy      int 
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_UpdateN(
                 ST_GeomFromText ('LINESTRING(0 0,2 2)',0),
                 ST_PointFromText('POINT(1 1)',0),
                 2,
                 1 
              ) 
           ) as newGeom;
 
    # updatedGeom
    'LINESTRING(0 0,1 1)'

DESCRIPTION

    Function that updates coordinate identified by p_position with the new point (p_replace_point) in the supplied geometry.
    p_position Values:
      1. null -> defaults to 1;
      2. -1   -> maximum number of points ie ST_NumPoints(p_geometry) 
      3. Greater than ST_NumPoints(p_geometry) -> maximum number of points ie ST_NumPoints(p_geometry)
    The updated coordinate's ordinates are rounded to p_round_xy number of decimal digits of precision.

INPUTS

    p_geometry      (geometry) - supplied geometry of any type.
    p_replace_point (geoemtry) - Replacement coordinate
    p_position      (int)      - Coordinate position in p_geometry: between 1 and STNumPoints.
    p_round_xy      (int)      - Decimal degrees of precision to which calculated XY ordinates are rounded.

RESULT

    updated geom    (geometry)-- Input geometry with coordinate replaced.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

11. INSPECT/ST_End_Point (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_End_Point -- Function which returns the last point in supplied geometry inclusive of all parts.

SYNOPSIS

    Function ST_End_Point (
                p_geometry geometry
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_End_Point(
                 ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),
                 -5.0, 3.2, 3
              )
           ) as startPoint;

    # startPoint
    'POINT(1.4 45.2)'

DESCRIPTION

    Function that returns last point in supplied geometry.
    Standard MySQL ST_EndPoint does not work Multi(Geometry) objects.

INPUTS

    p_geometry (geometry) - supplied geometry of any type.

RESULT

    point      (geometry) - Last point in Geometry inclusive of all parts.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

12. INSPECT/ST_Start_Point (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_Start_Point -- Function which returns first point in supplied geometry.

SYNOPSIS

    Function ST_Start_Point (
                p_geometry geometry
             )
     Returns geometry

USAGE

    SELECT ST_AsText(
              ST_Start_Point(
                 ST_GeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),
                 -5.0, 3.2, 3
              )
           ) as startPoint;

    # startPoint
    'POINT(0.1 0.2)'

DESCRIPTION

    Function that returns first point in supplied geometry.

INPUTS

    p_geometry (geometry) - supplied geometry of any type.

RESULT

    point      (geometry) - First point in Geometry

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

13. SORT/ST_Morton (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

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

SYNOPSIS

    Function ST_Morton ( 
                p_col int,
                p_row int 
             )
     Returns int

USAGE

    SELECT ST_Morton (10, 10) as mKey;

     # mKey
     828

DESCRIPTION

    Function that creates a Morton Key from a row/col (grid) reference. 
    The generated value can be used to order/sort geometry objects.

INPUTS

    p_col      (int) - Grid Column Reference.
    p_row      (int) - Grid Row Reference.

RESULT

    morton_key (int) - single integer morton key.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener

14. TOOLS/ST_Round (1.0) [ Methods ]

[ Top ] [ Methods ]

NAME

    ST_Round -- Function which rounds the XY ordinates of a geometry to the supplied number of decimal digits.

SYNOPSIS

    Function ST_Round ( 
                p_geometry geometry,
                p_round_xy int 
             )
     Returns geometry

USAGE

    SELECT ST_AsText(ST_Round(ST_GeomFromText('POINT(100.12223 100.345456)',0),2)) as rGeom; 
              ST_Round(
                 ST_GeomFromText('POINT(100.12223 100.345456)',0),
                 2)
           ) as rGeom; 

    # rGeom
    'POINT(100.12 100.35)'

DESCRIPTION

    The ordinates of the supplied geometry are rounded to p_round_xy number of decimal digits of precision.

INPUTS

    p_geometry (geometry) - supplied geometry of any type.
    p_round_xy (int)      - Decimal degrees of precision to which the geometry's XY ordinates are rounded.

RESULT

    geometry   (geometry) - Input geometry rounded to the supplied number of decimal digits of precision.

AUTHOR

    Simon Greener

HISTORY

    Simon Greener - December 2017 - Original Coding for MySQL.

COPYRIGHT

    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener