One of the cool things about MySQL 5.7 is the fact that it sports 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.
Recently I was working an project where I needed said function, yet unfortunately they were running some version of MySQL 5.6 … on which ST_Distance_Sphere
isn’t available.
Instead of requesting to upgrade the MySQL server, I decided to polyfill ST_Distance_Sphere
instead. It’s really easy since it’s a function, and it basically is nothing more than the Haversine Formula which I, as a mapping aficionado, know how to juggle.
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)
Happy Mapping! 🙂
Consider donating.
I don’t run ads on my blog nor do I do this for profit. A donation however would always put a smile on my face though. Thanks!
More discussion: https://stackoverflow.com/questions/50986432/create-mysql-function-in-laravel-5-5
Thank you so much, it was very useful !