Distance between Two Points with MySQL 5.7

Takahiro Iwasa

Jul 7, 2017

2 min read

GIS MySQL

MySQL 5.7 offers ST_Distance_Sphere function to get accurate distance between two points.

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

You might assume that the distance calculation is accurate just because the two points are close to each other. Let’s measure a longer distance.

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;

You may think that the second result was just correct because the two points were not near North or South Pole. Let’s measure at Svalbard.

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;

Using MySQL spatial analysis functions, engineers can develop easily applications to handle spatial data.

I hope you will find this post useful.