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 introduced the ST_Distance_Sphere function, which computes the great-circle distance between two geographic points without requiring a manual implementation of the Haversine formula.

❗ Important

The arguments to ST_Distance_Sphere must be specified in longitude, then latitude order, which is the reverse of the conventional “latitude, longitude” notation. Reversing this order yields an incorrect result without raising an error.

Short Distance Example

The following example calculates the distance between two points in Osaka approximately 2km apart.

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

To validate the function over a longer, independently verifiable distance, this example uses JR Osaka Station and JR Tokyo Station, which are approximately 400km apart.

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 confirm that the underlying sphere approximation remains accurate at high latitudes, this example uses two points in Svalbard, a region well above the Arctic Circle.

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

Conclusion

These three examples confirmed that ST_Distance_Sphere returns accurate great-circle distances at a 2km scale, a 400km scale, and near 78°N, where a naive planar approximation would be most likely to drift. With this function handling the spherical math natively, there’s no need to reach for a Haversine implementation in application code or a separate geospatial library just to answer “how far apart are these two points.” One thing worth remembering is that GeomFromText('POINT(...)') still expects longitude before latitude, so it’s easy to silently swap the arguments if you’re used to the more common lat/long convention.

Back to posts