Thursday, September 26, 2013

MySQL distance calculation in kilometers or miles

To calculate distance in MySQL, use the following query

The distance is in kilometers.

SELECT *,
6371 * acos( cos( radians($user_latitude) ) * cos( radians( latitude ) ) * cos( radians ( longitude ) - radians($user_longitude) ) + sin( radians($user_latitude) ) * sin( radians ( latitude ) ) )  as 'distance'
FROM locations
HAVING distance < 10;

To calculate distance in miles instead of kilometers

replace 6371 woth 3959

SELECT *,
6371 * acos( cos( radians($user_latitude) ) * cos( radians( latitude ) ) * cos( radians ( longitude ) - radians($user_longitude) ) + sin( radians($user_latitude) ) * sin( radians ( latitude ) ) )  as 'distance'
FROM locations
HAVING distance < 10;

Note: $user_latitude and $user_longitude is PHP variables containing user lat and lon, change them as you wish.