MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
The functions in this section provide convenience operations on geometry values.
ST_Distance_Sphere(
g1
,
g2
[,
radius
])
Returns the mimimum spherical distance between two points
and/or multipoints on a sphere, in meters, or
NULL
if any geometry argument is
NULL
or empty.
Calculations use a spherical earth and a configurable radius.
The optional radius
argument should
be given in meters. If omitted, the default radius is
6,370,986 meters. An
ER_WRONG_ARGUMENTS
error
occurs if the radius
argument is
present but not positive.
The geometry arguments should consist of points that specify (longitude, latitude) coordinate values:
Longitude and latitude are the first and second coordinates of the point, respectively.
Both coordinates are in degrees.
Longitude values must be in the range (-180, 180]. Positive values are east of the prime meridian.
Latitude values must be in the range [-90, 90]. Positive values are north of the equator.
Supported argument combinations are (Point
,
Point
), (Point
,
MultiPoint
), and
(MultiPoint
, Point
). An
ER_GIS_UNSUPPORTED_ARGUMENT
error occurs for other combinations.
If any geometry argument is not a syntactically well-formed
geometry byte string, an
ER_GIS_INVALID_DATA
error
occurs.
mysql>SET @pt1 = ST_GeomFromText('POINT(0 0)');
mysql>SET @pt2 = ST_GeomFromText('POINT(180 0)');
mysql>SELECT ST_Distance_Sphere(@pt1, @pt2);
+--------------------------------+ | ST_Distance_Sphere(@pt1, @pt2) | +--------------------------------+ | 20015042.813723423 | +--------------------------------+
Returns 1 if the argument is syntactically well-formed and is
geometrically valid, 0 if the argument is not syntactically
well-formed or is not geometrically valid. If the argument is
NULL
, the return value is
NULL
. Geometry validity is defined by the
OGC specification.
The only valid empty geometry is represented in the form of an
empty geometry collection value.
ST_IsValid()
returns 1 in this
case.
ST_IsValid()
works only for the
Cartesian coordinate system and requires a geometry argument
with an SRID of 0. An
ER_WRONG_ARGUMENTS
error
occurs otherwise.
mysql>SET @ls1 = ST_GeomFromText('LINESTRING(0 0,-0.00 0,0.0 0)');
mysql>SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
mysql>SELECT ST_IsValid(@ls1);
+------------------+ | ST_IsValid(@ls1) | +------------------+ | 0 | +------------------+ mysql>SELECT ST_IsValid(@ls2);
+------------------+ | ST_IsValid(@ls2) | +------------------+ | 1 | +------------------+
Returns the rectangle that forms the envelope around two
points, as a Point
,
LineString
, or Polygon
.
If any argument is NULL
, the return value
is NULL
.
Calculations are done using the Cartesian coordinate system rather than on a sphere, spheroid, or on earth.
Given two points pt1
and
pt2
,
ST_MakeEnvelope()
creates the
result geometry on an abstract plane like this:
If pt1
and
pt2
are equal, the result is
the point pt1
.
Otherwise, if (
is a vertical or
horizontal line segment, the result is the line segment
pt1
,
pt2
)(
.
pt1
,
pt2
)
Otherwise, the result is a polygon using
pt1
and
pt2
as diagonal points.
The result geometry has an SRID of 0.
ST_MakeEnvelope()
requires
Point
geometry arguments with an SRID of 0.
An ER_WRONG_ARGUMENTS
error
occurs otherwise.
If any argument is not a syntactically well-formed geometry
byte string, or if any coordinate value of the two points is
infinite or NaN
, an
ER_GIS_INVALID_DATA
error
occurs.
mysql>SET @pt1 = ST_GeomFromText('POINT(0 0)');
mysql>SET @pt2 = ST_GeomFromText('POINT(1 1)');
mysql>SELECT ST_AsText(ST_MakeEnvelope(@pt1, @pt2));
+----------------------------------------+ | ST_AsText(ST_MakeEnvelope(@pt1, @pt2)) | +----------------------------------------+ | POLYGON((0 0,1 0,1 1,0 1,0 0)) | +----------------------------------------+
Simplifies a geometry using the Douglas-Peucker algorithm and
returns a simplified value of the same type. If any argument
is NULL
, the return value is
NULL
.
The geometry may be any geometry type, although the Douglas-Peucker algorithm may not actually process every type. A geometry collection is processed by giving its components one by one to the simplification algorithm, and the returned geometries are put into a geometry collection as result.
The max_distance
argument is the
distance (in units of the input coordinates) of a vertex to
other segments to be removed. Vertices within this distance of
the simplified linestring are removed. If the
max_distance
argument is not
positive, or is NaN
, an
ER_WRONG_ARGUMENTS
error
occurs.
According to Boost.Geometry, geometries might become invalid
as a result of the simplification process, and the process
might create self-intersections. To check the validity of the
result, pass it to
ST_IsValid()
.
If the geometry argument is not a syntactically well-formed
geometry byte string, an
ER_GIS_INVALID_DATA
error
occurs.
mysql>SET @g = ST_GeomFromText('LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)');
mysql>SELECT ST_AsText(ST_Simplify(@g, 0.5));
+---------------------------------+ | ST_AsText(ST_Simplify(@g, 0.5)) | +---------------------------------+ | LINESTRING(0 0,0 1,1 1,2 3,3 3) | +---------------------------------+ mysql>SELECT ST_AsText(ST_Simplify(@g, 1.0));
+---------------------------------+ | ST_AsText(ST_Simplify(@g, 1.0)) | +---------------------------------+ | LINESTRING(0 0,3 3) | +---------------------------------+
Validates a geometry according to the OGC specification. A
geometry can be syntactically well-formed (WKB value plus
SRID) but geometrically invalid. For example, this polygon is
geometrically invalid: POLYGON((0 0, 0 0, 0 0, 0 0, 0
0))
ST_Validate()
returns the
geometry if it is syntactically well-formed and is
geometrically valid, NULL
if the argument
is not syntactically well-formed or is not geometrically valid
or is NULL
.
ST_Validate()
can be used to
filter out invalid geometry data, although at a cost. For
applications that require more precise results not tainted by
invalid data, this penalty may be worthwhile.
If the geometry argument is valid, it is returned as is,
except that if an input Polygon
or
MultiPolygon
has clockwise rings, those
rings are reversed before checking for validity. If the
geometry is valid, the value with the reversed rings is
returned.
The only valid empty geometry is represented in the form of an
empty geometry collection value.
ST_Validate()
returns it
directly without further checks in this case.
ST_Validate()
works only for
the Cartesian coordinate system and requires a geometry
argument with an SRID of 0. An
ER_WRONG_ARGUMENTS
error
occurs otherwise.
mysql>SET @ls1 = ST_GeomFromText('LINESTRING(0 0)');
mysql>SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
mysql>SELECT ST_AsText(ST_Validate(@ls1));
+------------------------------+ | ST_AsText(ST_Validate(@ls1)) | +------------------------------+ | NULL | +------------------------------+ mysql>SELECT ST_AsText(ST_Validate(@ls2));
+------------------------------+ | ST_AsText(ST_Validate(@ls2)) | +------------------------------+ | LINESTRING(0 0,1 1) | +------------------------------+