top of page
CerebroSQL

MySQL: 

ST_DISTANCE_SPHERE

ST_Distance_Sphere(g1, g2 [, radius])

Returns the mimimum spherical distance between Point or MultiPoint
arguments on a sphere, in meters. (For general-purpose distance
calculations, see the ST_Distance() function.) The optional radius
argument should be given in meters.

If both geometry parameters are valid Cartesian Point or MultiPoint
values in SRID 0, the return value is shortest distance between the two
geometries on a sphere with the provided radius. If omitted, the
default radius is 6,370,986 meters, Point X and Y coordinates are
interpreted as longitude and latitude, respectively, in degrees.

If both geometry parameters are valid Point or MultiPoint values in a
geographic spatial reference system (SRS), the return value is the
shortest distance between the two geometries on a sphere with the
provided radius. If omitted, the default radius is equal to the mean
radius, defined as (2a+b)/3, where a is the semi-major axis and b is
the semi-minor axis of the SRS.

ST_Distance_Sphere() handles its arguments as described in the
introduction to this section, with these exceptions:

o Supported geometry argument combinations are Point and Point, or
Point and MultiPoint (in any argument order). If at least one of the
geometries is neither Point nor MultiPoint, and its SRID is 0, an
ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_not_implemented_for_cartesian_srs) error occurs. If at
least one of the geometries is neither Point nor MultiPoint, and its
SRID refers to a geographic SRS, an
ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_not_implemented_for_geographic_srs) error occurs. If
any geometry refers to a projected SRS, an
ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_not_implemented_for_projected_srs) error occurs.

o If any argument has a longitude or latitude that is out of range, an
error occurs:

o If a longitude argument is not in the range (?180, 180], an
ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
ce.html#error_er_geometry_param_longitude_out_of_range) error
occurs (ER_LONGITUDE_OUT_OF_RANGE
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
ce.html#error_er_longitude_out_of_range) prior to MySQL 8.0.12).

o If a latitude argument is not in the range [?90, 90], an
ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
ce.html#error_er_geometry_param_latitude_out_of_range) error occurs
(ER_LATITUDE_OUT_OF_RANGE
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
ce.html#error_er_latitude_out_of_range) prior to MySQL 8.0.12).

Ranges shown are in degrees. If an SRS uses another unit, the range
uses the corresponding values in its unit. The exact range limits
deviate slightly due to floating-point arithmetic.

o If the radius argument is present but not positive, an
ER_NONPOSITIVE_RADIUS
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_nonpositive_radius) error occurs.

o If the distance exceeds the range of a double-precision number, an
ER_STD_OVERFLOW_ERROR
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_std_overflow_error) error occurs.

URL: https://dev.mysql.com/doc/refman/8.0/en/spatial-convenience-functions.html

Example

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 |
+--------------------------------+

bottom of page