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! 🙂
Thank me with a coffee.
I don\'t do this for profit but a small one-time donation would surely put a smile on my face. Thanks!
To stay in the loop you can follow @bramus or follow @bramusblog on Twitter.
More discussion: https://stackoverflow.com/questions/50986432/create-mysql-function-in-laravel-5-5
Thank you so much, it was very useful !
Thanks a lot, really saved me on a MySQL 5.6 environment! Though you might want to update it with an optional radius argument to fully emulate the native ST_Distance_Sphere convenience function. 🙂
You are awesome! Was using ST_DISTANCE_SPHERE locally to build an app, then discovered my hosted version of MySQL Server is 5.6.35, resulting in exceptions. You saved the day, thank you 🙂
You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable).
Cannot create a function, what can I do?
Leave a comment