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

```    (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.
```

```    (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.
```

```    (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.
```

```    (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.
```

```    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
```

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

```    (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.
```

```    (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
```

```    (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.
```

```    (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.
```

```    (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.
```

```    (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.
```

```    (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.
```

```    (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.
```

```    (c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener