Calculating Distances Between Geographic Points with MySQL 5.7

MySQL 5.7 provides the STDistanceSphere function for calculating the great-circle distance between two geographic points.

Takahiro Iwasa

2017-07-07

2 min read

MySQL 5.7 provides the ST_Distance_Sphere function for calculating the great-circle distance between two geographic points.

❗ Important

The arguments for the ST_Distance_Sphere function should follow the order of longitude first, then latitude.

Short Distance Example

Here, we calculate the distance between two points in Osaka.

1
SELECT
2
    ST_Distance_Sphere(
3
            GeomFromText('POINT(135.507260 34.693946)'),
4
            GeomFromText('POINT(135.526201 34.687316)')
5
    ) AS distance_meter
6
FROM
7
    dual;

row	distance_meter
1	1882.1360099034516

Long Distance Example

For longer distances, let’s calculate the distance between JR Osaka Station and JR Tokyo Station.

1
SELECT
2
  ST_Distance_Sphere(
3
    GeomFromText('POINT(135.495951 34.702488)'), -- JR Osaka station
4
    GeomFromText('POINT(139.767052 35.681168)')  -- JR Tokyo station
5
  ) AS distance_meter
6
FROM
7
  dual;

row	distance_meter
1	403048.2752256764

Near the Poles Example

To demonstrate the accuracy near the poles, let’s measure a distance at Svalbard, a high-latitude region.

1
SELECT
2
  ST_Distance_Sphere(
3
    GeomFromText('POINT(16.379258 78.655621)'), -- Pyramiden Container Hostel
4
    GeomFromText('POINT(16.328528 78.655143)')  -- Hotel Tulpan
5
  ) AS distance_meter
6
FROM
7
  dual;

row	distance_meter
1	1110.8932928975748

Benefits of MySQL Spatial Functions

Using MySQL’s spatial analysis functions, developers can easily implement robust geographic data processing capabilities. These functions are highly optimized for performance and accuracy, making them ideal for applications requiring geographic calculations.

Back to posts