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! 🙂
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!