Calculating distance using MySQL

One of the cool things about MySQL 5.7 is the fact that it supports a few spatial convenience functions (since 5.7.6), allowing one to do operations on geometric values.

One of those convenience functions is ST_Distance_Sphere, which allows one to calculate the (spherical) distance between two points. This function is unfortunately not available on MySQL 5.7.5 or lower.

Instead of requesting to upgrade the MySQL server, you can polyfill ST_Distance_Sphere instead. It’s really easy since it’s a function, and it basically is nothing more than like the Haversine Formula.

Please check out the ST_Distance_Sphere polyfill on MySQL 5.7.5 or lower:

DELIMITER $$

DROP FUNCTION IF EXISTS `ST_Distance_Sphere`$$

CREATE FUNCTION `ST_Distance_Sphere` (point1 POINT, point2 POINT)

	RETURNS FLOAT
	no sql deterministic
	BEGIN
		declare R INTEGER DEFAULT 6371000;
		declare `φ1` float;
		declare `φ2` float;
		declare `Δφ` float;
		declare `Δλ` float;
		declare a float;
		declare c float;
		set `φ1` = radians(y(point1));
		set `φ2` = radians(y(point2));
		set `Δφ` = radians(y(point2) - y(point1));
		set `Δλ` = radians(x(point2) - x(point1));

		set a = sin(`Δφ` / 2) * sin(`Δφ` / 2) + cos(`φ1`) * cos(`φ2`) * sin(`Δλ` / 2) * sin(`Δλ` / 2);
		set c = 2 * atan2(sqrt(a), sqrt(1-a));

		return R * c;
	END$$

DELIMITER ;

Run the little snippet above on your server after having selected a database first. Once executed it’ll persistently be available (just like a Stored Procedure) for you to use. Usage is the same as if it were natively available:

SELECT ST_Distance_Sphere(
  POINT(-87.6770458, 41.9631174),
  POINT(-73.9898293, 40.7628267)
);

// ~> 1148978.6738241839 (in metres)

Examples in Laravel

Here is an example showing the distance between two points using the Laravel Query Builder:

dd(\DB::select(\DB::raw('
    select ST_Distance_Sphere(
        point(:lonA, :latA),
        point(:lonB, :latB)
    ) * 0.00621371192
'), [
    'lonA' => -87.6770458,
    'latA' => 41.9631174,
    'lonB' => -73.9898293,
    'latB' => 40.7628267,
]));

And here’s a similar method for selecting results based on proximity to a given position:

// Eloquent Scope:
public function scopeCloseTo(Builder $query, $latitude, $longitude)
{
    return $query->whereRaw("
       ST_Distance_Sphere(
            point(longitude, latitude),
            point(?, ?)
        ) * .000621371192 < delivery_max_range
    ", [
        $longitude,
        $latitude,
    ]);
}

// Using the scope:
return Restaurant::closeTo($myLatitude, $myLongitude);

Caveats and limitations

However, I would be remiss if I didn’t mention the limitations of this method:

  • As you have probably already gathered, this is only “as the crow flies”. If you need distance with road routing or traffic taken into consideration, this method won’t be of much help.
  • These MySQL functions default to using SRID 0, which is close enough for basic use, but you will want to match your use case if high fidelity accuracy is important. More below.

What’s SRID?

SRID is basically the method of conversion from spatial coordinates to the Earth’s coordinates. By default, MySQL uses SRID 0, which represents an “infinite flat Cartesian plane with no units assigned to its axes”. Google and Bing, however, use SRID 3857, which is the “Spherical Mercator projection coordinate system.” (Note: Google Earth uses SRID 4326.)

For many applications, however, ST_Distance_Sphere is more than enough to build the functionality you need.

Conclusion

If you’re working in MySQL 5.7+ and need to find distances, remember to reach for ST_Distance_Sphere first. Even if it may not always be the right fit for your code, it’s an easy—and powerful—way to get, and query against, real-world distances across the face of the Earth.