Топ-100
 

ST_DISTANCE

RDBMS Type: MySQL
Topic

ST_Distance(g1, g2 [, unit])

Returns the distance between g1 and g2, measured in the length unit of
the spatial reference system (SRS) of the geometry arguments, or in the
unit of the optional unit argument if that is specified.

This function processes geometry collections by returning the shortest
distance among all combinations of the components of the two geometry
arguments.

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

o ST_Distance() detects arguments in a geographic (ellipsoidal) spatial
reference system and returns the geodetic distance on the ellipsoid.
As of MySQL 8.0.18, ST_Distance() supports distance calculations for
geographic SRS arguments of all geometry types. Prior to MySQL
8.0.18, the only permitted geographic argument types are Point and
Point, or Point and MultiPoint (in any argument order). If called
with other geometry type argument combinations in 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.

o If any argument is geometrically invalid, either the result is an
undefined distance (that is, it can be any number), or an error
occurs.

o If an intermediate or final result produces NaN or a negative number,
an ER_GIS_INVALID_DATA
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_gis_invalid_data) error occurs.

As of MySQL 8.0.14, ST_Distance() permits an optional unit argument
that specifies the linear unit for the returned distance value. These
rules apply:

o If a unit is specified but not supported by MySQL, an
ER_UNIT_NOT_FOUND
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_unit_not_found) error occurs.

o If a supported linear unit is specified and the SRID is 0, an
ER_GEOMETRY_IN_UNKNOWN_LENGTH_UNIT
(https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference
.html#error_er_geometry_in_unknown_length_unit) error occurs.

o If a supported linear unit is specified and the SRID is not 0, the
result is in that unit.

o If a unit is not specified, the result is in the unit of the SRS of
the geometries, whether Cartesian or geographic. Currently, all MySQL
SRSs are expressed in meters.

A unit is supported if it is found in the INFORMATION_SCHEMA
ST_UNITS_OF_MEASURE table. See
https://dev.mysql.com/doc/refman/8.0/en/information-schema-st-units-of-
measure-table.html.

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

Example

mysql> SET @g1 = Point(1,1);
mysql> SET @g2 = Point(2,2);
mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
| 1.4142135623730951 |
+-----------------------+

mysql> SET @g1 = ST_GeomFromText('POINT(1 1)', 4326);
mysql> SET @g2 = ST_GeomFromText('POINT(2 2)', 4326);
mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
| 156874.3859490455 |
+-----------------------+
mysql> SELECT ST_Distance(@g1, @g2, 'metre');
+--------------------------------+
| ST_Distance(@g1, @g2, 'metre') |
+--------------------------------+
| 156874.3859490455 |
+--------------------------------+
mysql> SELECT ST_Distance(@g1, @g2, 'foot');
+-------------------------------+
| ST_Distance(@g1, @g2, 'foot') |
+-------------------------------+
| 514679.7439273146 |
+-------------------------------+