How to measure distance using Haversine formula with MySQL?

Question

I get Latitude and Longitudes from Google Maps Reverse-Geocoding API and then I need something like this:

mysql_query("SELECT users.*, ".mysql_distance_column($lat,$lng)." FROM users ORDER BY DISTANCE";

function mysql_distance_column($lat=40 , $lng=-73) {

   $defaultLatitudeColumn = 'user_lat'; 
   $defaultLongitudeColumn='user_lng';
   $defaultColumnName='user_distance';
    return  "(( 
(3956 * 2 * ASIN(SQRT( POWER(SIN(({$lat} - abs({$defaultLatitudeColumn})) 
* pi()/180 / 2), 2) + COS({$lat} * pi()/180 ) 
* COS(abs({$defaultLatitudeColumn}) * pi()/180) 
* POWER(SIN(({$lng} - {$defaultLongitudeColumn}) * pi()/180 / 2), 2) ))
 )) ) as {$defaultColumnName} ";

}

UPDATE I cant ge this to work

delimiter //
CREATE FUNCTION `GeoDistMiles`( lat1 FLOAT (10,6), lon1 FLOAT (10,6), lat2 FLOAT (10,6), lon2 FLOAT (10,6) ) 
RETURNS FLOAT 
DETERMINISTIC 
NO SQL
BEGIN
  DECLARE pi, q1, q2, q3 FLOAT (10,6);
  DECLARE rads FLOAT (10,6) DEFAULT 0;
  SET pi = PI();
  SET lat1 = lat1 * pi / 180;
  SET lon1 = lon1 * pi / 180;
  SET lat2 = lat2 * pi / 180;
  SET lon2 = lon2 * pi / 180;
  SET q1 = COS(lon1-lon2);
  SET q2 = COS(lat1-lat2);
  SET q3 = COS(lat1+lat2);
  SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
  RETURN 3963.346 * rads;
END

How far off are your calculations? Are they WAY off or just a little off? — anon, Feb 02 '11 at 18:39

score 3 · Accepted Answer · answered Feb 02 '11 at 18:46

3

Here is the formula I use. Remember that the Earth is not a perfect sphere, so the results will never be perfect.

CREATE DEFINER=`root`@`localhost` FUNCTION `GeoDistMiles`( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
  DECLARE pi, q1, q2, q3 FLOAT;
  DECLARE rads FLOAT DEFAULT 0;
  SET pi = PI();
  SET lat1 = lat1 * pi / 180;
  SET lon1 = lon1 * pi / 180;
  SET lat2 = lat2 * pi / 180;
  SET lon2 = lon2 * pi / 180;
  SET q1 = COS(lon1-lon2);
  SET q2 = COS(lat1-lat2);
  SET q3 = COS(lat1+lat2);
  SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
  RETURN 3963.346 * rads;
END

answered Feb 02 '11 at 18:46

anon

4,578
3
35
54

2

Note that ACOS is numerically unstable for points that are close together. See http://en.wikipedia.org/wiki/Haversine_formula for a formula that avoids this issue. – btilly Feb 02 '11 at 18:49
1

this gives really inaccurate results! – Neo Sep 10 '11 at 19:20

btilly · Answer 2 · 2011-02-02T23:50:28.367

1

I assume that you are trying to use http://en.wikipedia.org/wiki/Haversine_formula.

This is lightly tested, but I think that your formula should be:

(ROUND((3956 * 2 * ASIN(SQRT(POWER(SIN(({$lat} - {$defaultLatitudeColumn}) * pi() / 180 / 2), 2) + COS({$lat} * pi()/180 ) * COS({$defaultLatitudeColumn} * pi()/180) *POWER(SIN(({$lng} - {$defaultLongitudeColumn}) * pi()/180 / 2), 2) )) )*{$magicNumber}) )/{$magicNumber}

(I removed the abs calls.)

edited Feb 02 '11 at 23:50

answered Feb 02 '11 at 18:58

btilly

43,296
3
59
88

I used this and it shows the same user 4708.0 miles away from themselve. – Neo Feb 02 '11 at 23:15
@Neo: I've tested this, and realized that my edits to the formula were incorrect. I believe that the error in your formula is due to the abs you were doing to $defaultLatitudeColumn. As a result locations in the Southern hemisphere were being compared to the equivalent location in the Northern hemisphere, which is a long ways off. I'm sorry for getting it wrong the first time. – btilly Feb 02 '11 at 23:53

score 1 · Answer 3 · edited Aug 22 '17 at 18:45

Hi I have a simple procedure you can use it for your work.

The procedure is very simple and calculate the distance between two cities.you can modify it in your way.

drop procedure if exists select_lattitude_longitude;


delimiter //

create procedure select_lattitude_longitude(In CityName1 varchar(20) , In CityName2 varchar(20))

begin

declare origin_lat float(10,2);

declare origin_long float(10,2);

declare dest_lat float(10,2);

declare dest_long float(10,2);

if CityName1  Not In (select Name from City_lat_lon) OR CityName2  Not In (select Name from City_lat_lon) then

select 'The Name Not Exist or Not Valid Please Check the Names given by you' as Message;


else

select lattitude into  origin_lat from City_lat_lon where Name=CityName1;

select longitude into  origin_long  from City_lat_lon where Name=CityName1;

select lattitude into  dest_lat from City_lat_lon where Name=CityName2;

select longitude into  dest_long  from City_lat_lon where Name=CityName2;


select  origin_lat as CityName1_lattitude,
origin_long as CityName1_longitude,
dest_lat as CityName2_lattitude,
dest_long as CityName2_longitude;


SELECT 3956 * 2 * ASIN(SQRT( POWER(SIN((origin_lat - dest_lat) * pi()/180 / 2), 2) + COS(origin_lat * pi()/180) * COS(dest_lat * pi()/180) * POWER(SIN((origin_long-dest_long) * pi()/180 / 2), 2) )) * 1.609344 as Distance_In_Kms ;


end if;

end ;

//

delimiter ;

score 0 · Answer 4 · answered Jul 26 '17 at 07:25

0

3956 * 2 * ASIN(SQRT( POWER(SIN(($latitude -( cp.latitude)) * pi()/180 / 2),2) + COS($latitude * pi()/180 ) * COS( abs( cp.latitude) *  pi()/180) * POWER(SIN(($longitude - cp.longitude) *  pi()/180 / 2), 2) )) as distance

Returns miles or KM?

answered Jul 26 '17 at 07:25

Stergios Zg.

652
6
9

score 0 · Answer 5 · answered Sep 17 '11 at 15:57

SELECT ACOS(COS(RADIANS(lat)) *
COS(RADIANS(lon)) * COS(RADIANS(34.7405350)) * COS(RADIANS(-92.3245120)) +
COS(RADIANS(lat)) * SIN(RADIANS(lon)) * COS(RADIANS(34.7405350)) * 
SIN(RADIANS(-92.3245120)) + SIN(RADIANS(lat)) * SIN(RADIANS(34.7405350))) * 
3963.1 AS Distance
FROM Stores 
WHERE 1
HAVING Distance <= 50

Here's how I use it in PHP:

// Find rows in Stores within 50 miles of $lat,$lon
$lat = '34.7405350';
$lon = '-92.3245120';

$sql = "SELECT Stores.*, ACOS(COS(RADIANS(lat)) *
COS(RADIANS(lon)) * COS(RADIANS($lat)) * COS(RADIANS($lon)) +
COS(RADIANS(lat)) * SIN(RADIANS(lon)) * COS(RADIANS($lat)) * 
SIN(RADIANS($lon)) + SIN(RADIANS(lat)) * SIN(RADIANS($lat))) * 
3963.1 AS Distance
FROM Stores 
WHERE 1
HAVING Distance <= 50";

How to measure distance using Haversine formula with MySQL?

5 Answers5