MySQL UDF to compute the Haversine formula

Returns the haversine distance from two points

Note: MySQL 5.7 introduced the native st_distance_sphere function, so this project here is not required anymore.

lat1 (real), lng1 (real), lat2 (real), lng2 (real), type (string - optinal - 'km', 'ft', 'mi')

Distance between the two points. The measurement is returned in kilometers (real) by default, however if you specify the 5th type argument you may return in other measurements.

gcc -shared -fPIC -o lib_mysqludf_haversine.so lib_mysqludf_haversine.c $(mysql_config --cflags) \
    -lm -DMYSQL_DYNAMIC_PLUGIN

At mysql commandline prompt, type:

CREATE FUNCTION haversine_distance RETURNS REAL SONAME 'lib_mysqludf_haversine.so';

At mysql commandline prompt, type:

DROP FUNCTION haversine_distance;

At mysql commandline prompt, type:

SELECT  FROM mysql.func;

If you have permission problems installing function such as:

ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_haversine.so' 
(errno: 22 /usr/lib/mysql/plugin/lib_mysqludf_haversine.so: failed to map segment from shared object: Permission denied)

Follow this steps:

1. type: sudo vi /etc/apparmor.d/usr.sbin.mysqld;
2. add this line: /usr/lib/mysql/plugin/** mr, and save;
3. type: sudo /etc/init.d/apparmor restart

then register the function in MySQL.

Once installed, to calculate distance between two Latitude/Longitude points:

SELECT id, name FROM MY_PLACES WHERE haversine_distance(lat1, lng1, lat2, lng2) < 40;

The SELECT will return all the records with a distance less then 40 kilometers.

SELECT id, name FROM MY_PLACES WHERE haversine_distance(lat1, lng1, lat2, lng2, 'ft') < 25;

The SELET will return all the records with a distance less then 25 feet.