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

~

**Did this help you out? Like what you see?**

Thank me with a coffee.

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?

thank you