Calculating Distances Between Points Using MySQL 5.7

Takahiro Iwasa

Jul 7, 2017

2 min read

GIS MySQL

It has been more than 3 years since this post was published.

Introduction

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

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

Example: Calculating Distance Between Points

Short Distance Example

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

SQL

SELECT
  ST_Distance_Sphere(
    GeomFromText('POINT(135.507260 34.693946)'),
    GeomFromText('POINT(135.526201 34.687316)')
  ) AS distance_meter
FROM
  dual;

Result

row	distance_meter
1	1882.1360099034516

Google Map Comparison

Long Distance Example

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

SQL

SELECT
  ST_Distance_Sphere(
    GeomFromText('POINT(135.495951 34.702488)'), -- JR Osaka station
    GeomFromText('POINT(139.767052 35.681168)')  -- JR Tokyo station
  ) AS distance_meter
FROM
  dual;

Result

row	distance_meter
1	403048.2752256764

Google Map Comparison

Near the Poles Example

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

SQL

SELECT
  ST_Distance_Sphere(
    GeomFromText('POINT(16.379258 78.655621)'), -- Pyramiden Container Hostel
    GeomFromText('POINT(16.328528 78.655143)')  -- Hotel Tulpan
  ) AS distance_meter
FROM
  dual;

Result

row	distance_meter
1	1110.8932928975748

Google Map Comparison

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.

Conclusion

The ST_Distance_Sphere function in MySQL 5.7 enables developers to compute distances between geographic points with precision, even across long distances or near the poles. Leveraging this capability can significantly simplify spatial data processing in your applications.

Happy Coding! 🚀