Calculating Distances Between Geographic Points with MySQL 5.7

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

Takahiro Iwasa

2017-07-07

4 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.

Short Distance Example

The following example calculates the distance between two points in Osaka approximately 2 km 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;

Long Distance Example

To test the function over a longer, independently verifiable distance, this example uses JR Osaka Station and JR Tokyo Station, which are approximately 400 km 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;

Near the Poles Example

To examine how the spherical approximation behaves 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;

Difference from ST_Distance

Despite the similar names, ST_Distance and ST_Distance_Sphere use different models for space. In MySQL 5.7, spatial calculations assume SRID 0, an infinite flat Cartesian plane whose axes have no assigned unit. ST_Distance therefore returns the shortest planar distance between two geometries in the same units as their coordinates.

For example, when longitude and latitude are stored as POINT(135.5, 34.7), ST_Distance treats those values simply as X and Y positions on a flat plane. Its result is expressed in coordinate units—not meters or kilometers—and it does not account for the curvature of the Earth or the decreasing ground distance represented by one degree of longitude toward the poles.

ST_Distance_Sphere, by contrast, interprets a point’s X and Y values as longitude and latitude in degrees, calculates the shortest distance over a sphere, and returns the result in meters. MySQL 5.7 uses a default sphere radius of 6,370,986 meters unless a different radius is supplied.

Use ST_Distance_Sphere for longitude-latitude points when an approximate physical distance on Earth is needed. Use ST_Distance when the coordinates already belong to an appropriate planar system, or when calculating the shortest Cartesian distance between geometry shapes. Neither function calculates a road, rail, or walking route.

Conclusion

These three examples show how ST_Distance_Sphere calculates great-circle distances over approximately 2 km and 400 km, as well as near 78°N, where a simple planar approximation is especially prone to error. Because the function performs the spherical calculation itself, there is no need to implement the Haversine formula in application code or add a separate geospatial library simply to determine the distance between two points.

Remember that GeomFromText('POINT(...)') expects longitude before latitude. If you are used to the more common latitude-longitude convention, it is easy to reverse the arguments without noticing.

About the author

Takahiro Iwasa

LinkedIn

GitHub

Software Developer

This blog shares technical notes from hands-on projects—architecture, implementation, and AWS service integrations.