Distance between Two Points with MySQL 5.7

Distance between Two Points with MySQL 5.7

Takahiro Iwasa
(岩佐 孝浩)
Takahiro Iwasa (岩佐 孝浩)
2 min read
GIS MySQL

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

Example 1

SQL

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

Result

rowdistance_meter
11882.1360099034516

Google Map Result

Example 2

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.

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

rowdistance_meter
1403048.2752256764

Google Map Result

Example 3

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.

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

rowdistance_meter
11110.8932928975748

Google Map Result

Takahiro Iwasa
(岩佐 孝浩)

Takahiro Iwasa (岩佐 孝浩)

Software Developer at KAKEHASHI Inc.
Involved in the design, development, and operation of the prescription data collection platform