Haversine in MySQL
##################

:date: 2013-03-05T12:49:28Z
:category: blog
:tags: mysql,development
:url: blog/2013/3/5/haversine-in-mysql.html
:save_as: blog/2013/3/5/haversine-in-mysql.html
:status: published
:author: Gergely Polonkai

Just insert it in your database, feed them two Google coordinates, and you get the distance in
kilometres.  If you happen to need it in miles, change the constant ``12756.200`` in the
``RETURN`` row to ``7922.6`` instead.

.. code-block:: sql

   DELIMITER $$

   CREATE FUNCTION `haversine` (lng1 FLOAT, lat1 FLOAT, lng2 FLOAT, lat2 FLOAT)
   RETURNS float NO SQL DETERMINISTIC
   BEGIN
       SET @a = ABS(POWER(SIN(RADIANS(lat1 - lat2)) / 2, 2) + COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * POWER(SIN(RADIANS(lng1 - lng2)) / 2, 2));
       RETURN 12756.200 * ATAN2(SQRT(@a), SQRT(1 - @a));
   END$$