Calculating Distances Between Geographic Points with MySQL 5.7

Takahiro Iwasa

7/7/2017

2 min read

GIS MySQL

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

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;

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;

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;

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.