MySQL ST_Distance_Sphere Polyfill

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.

I don\'t do this for profit but a small one-time donation would surely put a smile on my face. Thanks!

BuymeaCoffee (€4)

To stay in the loop you can follow @bramus or follow @bramusblog on Twitter.

Published by Bramus!

Bramus is a frontend web developer from Belgium, working as a Chrome Developer Relations Engineer at Google. From the moment he discovered view-source at the age of 14 (way back in 1997), he fell in love with the web and has been tinkering with it ever since (more …)

Unless noted otherwise, the contents of this post are licensed under the Creative Commons Attribution 4.0 License and code samples are licensed under the MIT License

Join the Conversation

8 Comments

  1. 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. 🙂

  2. 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 🙂

  3. 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

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.