Spatial Data Functions

MySQL Tutorials - Herong's Tutorial Examples

∟Spatial Data Functions

Describes some commonly used Spatial Data functions like, ST_GeomFromText(), ST_AsText(), ST_GeometryType(), POINT(), ST_X(), ST_Y(), ST_NumPoints(), ST_PointN(), ST_Area(), etc.

MySQL supports a number of built-in functions that allows you to manipulate spatial data objects.

ST_GeomFromText(string) - Returns a spatial data object from a given Well-Known Text (WKT) string.

ST_AsText(object) - Returns the Well-Known Text (WKT) string of a given spatial data object.

ST_GeometryType(object) - Returns geometry type of a given spatial data object.

ST_SRID(object) - Returns the SRID (Spatial Reference IDentifier) of a given spatial data object.

POINT(x, y) - Returns a "Point" spatial data object with given coordinates.

ST_X/ST_Y(point) - Returns the X/Y coordinate of a given "Point".

ST_PointFromText(string) - Returns a "Point" spatial data object from a given Well-Known Text (WKT) Point string.

ST_LineStringFromText(string) - Returns a "LineString" spatial data object from a given Well-Known Text (WKT) LineString string.

ST_NumPoints(linestring) - Returns the number of points of a given "LineString" object.

ST_PointN(linestring, n) - Returns the n'th "Point" object of a given "LineString" object.

ST_StartPoint(linestring) - Returns the first "Point" object of a given "LineString" object.

ST_EndPoint(linestring) - Returns the last "Point" object of a given "LineString" object.

ST_Area(polygon) - Returns the area value of a given "Polygon" object.

Examples of spatial data functions, SpatialFunctions.sql:

-- SpatialFunctions.sql
-- Copyright (c) 2005 HerongYang.com. All Rights Reserved.
--
SELECT ST_AsText(ST_GeomFromText('POINT(15 20)'));

SELECT ST_X(POINT(15,20)), ST_Y(POINT(12,20));
SELECT ST_X(ST_PointFromText('POINT(15 20)'));
SELECT ST_SRID(ST_PointFromText('POINT(15 20)'));
SELECT ST_GeometryType(ST_PointFromText('POINT(15 20)'));

SET @ls = ST_LineStringFromText('LINESTRING(15 20, 1 21, 2 3)');
SELECT ST_NumPoints(@ls);
SELECT ST_AsText(ST_PointN(@ls,2));
SELECT ST_AsText(ST_StartPoint(@ls));
SELECT ST_AsText(ST_EndPoint(@ls));
SELECT ST_SRID(@ls);
SELECT ST_GeometryType(@ls);

SET @poly = ST_GeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
SELECT ST_Area(@poly);

SET @poly = ST_GeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0),
   (0 0, 2 0, 2 2, 0 2, 0 0))');
SELECT ST_Area(@poly);

Output:

ST_AsText(ST_GeomFromText('POINT(15 20)'))
POINT(15 20)
ST_X(POINT(15,20))      ST_Y(POINT(12,20))
15      20
ST_X(ST_PointFromText('POINT(15 20)'))
15
ST_SRID(ST_PointFromText('POINT(15 20)'))
0
ST_GeometryType(ST_PointFromText('POINT(15 20)'))
POINT
ST_NumPoints(@ls)
3
ST_AsText(ST_PointN(@ls,2))
POINT(1 21)
ST_AsText(ST_StartPoint(@ls))
POINT(15 20)
ST_AsText(ST_EndPoint(@ls))
POINT(2 3)
ST_SRID(@ls)
0
ST_GeometryType(@ls)
LINESTRING
ST_Area(@poly)
4
ST_Area(@poly)
5