Find distance between two points using latitude and longitude in mysql

Question

Hi I have the following table

 --------------------------------------------
 |  id  |  city  |  Latitude  |  Longitude  |
 --------------------------------------------
 |  1   |   3    |   34.44444 |   84.3434   |
 --------------------------------------------
 |  2   |   4    | 42.4666667 | 1.4666667   |
 --------------------------------------------
 |  3   |   5    |  32.534167 | 66.078056   |
 --------------------------------------------
 |  4   |   6    |  36.948889 | 66.328611   |
 --------------------------------------------
 |  5   |   7    |  35.088056 | 69.046389   |
 --------------------------------------------
 |  6   |   8    |  36.083056 |   69.0525   |
 --------------------------------------------
 |  7   |   9    |  31.015833 | 61.860278   |
 --------------------------------------------

Now I want to get distance between two points. Say a user is having a city 3 and a user is having a city 7. My scenario is one user having a city and latitue and longtitude is searching other users distance from his city. For example user having city 3 is searching. He wants to get distance of user of any other city say it is 7. I have searched and found following query

SELECT `locations`.`city`, ( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) * sin( radians( Latitude ) ) ) ) AS `distance` FROM `locations` HAVING (distance < 50)

As for as I know this query finds distance from one point to all other points. Now I want to get distance from one point to other point.

Any guide line will be much appreciated.

So do a self-join against the table to get both sets of coordinates, and then you just run your calculation on those two points. — Marc B, Jun 23 '14 at 16:40
Have you considered taking this out of a SQL query, an using the Google Maps API for this? I know it doesn't answer your question. But you may find that this work is already done for you. — durbnpoisn, Jun 23 '14 at 16:41
@durbnpoisn No I don't want that solution. This is my requirement. — Enthusiast, Jun 23 '14 at 16:45
@MarcB This query will run in loop so I will be having values of both points.But how to put in this query isn't easy to understand. — Enthusiast, Jun 23 '14 at 16:46
`select t1.lat, t2.lat from yourtable as t1 inner join yourtable as t2 on ...` — Marc B, Jun 23 '14 at 16:47
@Enthusiast - It's all good. I said I knew it wasn't the answer. I was just offering an alternative. — durbnpoisn, Jun 23 '14 at 17:33
`st_distance_sphere()` is a helpful function for this, available since MySQL 5.7.6 — JVE999, Feb 28 '20 at 18:28

score 121 · Accepted Answer · edited Mar 01 '22 at 16:03

I think your question says you have the city values for the two cities between which you wish to compute the distance.

This query will do the job for you, yielding the distance in km. It uses the spherical cosine law formula.

Notice that you join the table to itself so you can retrieve two coordinate pairs for the computation.

SELECT a.city AS from_city, b.city AS to_city, 
   111.111 *
    DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.Latitude))
         * COS(RADIANS(b.Latitude))
         * COS(RADIANS(a.Longitude - b.Longitude))
         + SIN(RADIANS(a.Latitude))
         * SIN(RADIANS(b.Latitude))))) AS distance_in_km
  FROM city AS a
  JOIN city AS b ON a.id <> b.id
 WHERE a.city = 3 AND b.city = 7

Notice that the constant 111.1111 is the number of kilometres per degree of latitude, based on the old Napoleonic definition of the metre as one ten-thousandth of the distance from the equator to the pole. That definition is close enough for location-finder work.

If you want statute miles instead of kilometres, use 69.0 instead.

http://sqlfiddle.com/#!9/21e06/412/0

If you're looking for nearby points you may be tempted to use a clause something like this:

   HAVING distance_in_km < 10.0    /* slow ! */
    ORDER BY distance_in_km DESC

That is (as we say near Boston MA USA) wicked slow.

In that case you need to use a bounding box computation. See this writeup about how to do that. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

The formula contains a LEAST() function. Why? Because the ACOS() function throws an error if its argument is even slightly greater than 1. When the two points in question are very close together, the expression with the COS() and SIN() computations can sometimes yield a value slightly greater than 1 due to floating-point epsilon (inaccuracy). The LEAST(1.0, dirty-great-expression) call copes with that problem.

There's a better way, a formula by Thaddeus Vincenty. It uses ATAN2() rather than ACOS() so it's less susceptible to epsilon problems.

Edit 2022 (by Alexio Vay): As of today the modern solution should be the following short code:

   select ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267))

Please check out the answer of Naresh Kumar.

what you have written here and on sqlfiddle are different. Though both works but sqlfiddle one gives accurate result. — Mubasshir Pawle, Feb 11 '15 at 12:12
Used the code to query for the closest location of 2 coordinates. Worked well! — El Dude, Jun 14 '15 at 22:56
I was getting null values for very close distances when using this formula in mysql. My soultion was to add a "LEAST" function to prevent out of bounds numbers. DEGREES(ACOS(LEAST(COS(RADIANS(lat)) * COS(RADIANS(google_lat)) * COS(RADIANS(lng - google_lng)) + SIN(RADIANS(lat)) * SIN(RADIANS(google_lat)),1.0))) AS distance_in_km, — Robert, May 04 '17 at 12:17
Thanks you for your answer. Could you explain reason behind using least function and inserting 1.0 ? — haneulkim, Sep 20 '19 at 00:25
makewhite, good question! Please see the comment of @Robert and my recent edit. — O. Jones, Sep 20 '19 at 11:50

score 62 · Answer 2 · edited Oct 26 '22 at 09:24

62

You can use the ST_Distance_Sphere() MySQL built-in function, supported since MySQL 5.7 version and above. It computes the distance in meters more efficiently.

select ST_Distance_Sphere(point(lng, lat), point(lng,lat))

i.e.

    select ST_Distance_Sphere(
        point(-87.6770458, 41.9631174),
        point(-73.9898293, 40.7628267)
    )

Referred from Calculating distance using MySQL

edited Oct 26 '22 at 09:24

Marcin Orlowski

72,056
11
123
141

answered Mar 27 '20 at 09:01

Naresh Kumar

871
7
8

1

This needs to be way more up and the nowadays accepted answer. This solution is the modern solution. – AlexioVay Mar 01 '22 at 15:58
1

Note that the result is given in Meters, so you may need to convert it. – arrmani88 Jul 17 '22 at 20:10
1

This answer gives you only approximate distance and NOT accurate. @O. Jones method using COS gives exact same as google map distance calculator. – Vinith Jul 26 '22 at 12:30
2

In my tests the ST_Distance_Sphere is 3-5 times slower than the accepted answer with the long COS term. To calculate the distance of my 16480 zip codes against my location it takes 160ms with the ST_Distance_Sphere method and only 63ms with the cos term. – heiwil Jan 09 '23 at 07:47
Similar results here, but not as bad 1.73 times slower. ST_Distance_Sphere on GCP took 2.04 seconds to calculate ~1 million records with 1 vCPU and 614.4 MB RAM MySQL 8.0. And 1.18 seconds with the accepted answer. I'll use ST_Distance_Sphere for readability, later if I need to optimize, I'll use the accepted answer. (P.S. My test excludes network times and only includes calculation times. Network times for downloading 1M rows would take significantly longer.) – IcyIcicle Aug 24 '23 at 22:30

score 34 · Answer 3 · edited Oct 27 '22 at 08:17

Heres is MySQL query and function which use to get distance between two latitude and longitude and distance will return in KM.

Mysql Query :-

SELECT (6371 * acos( 
                cos( radians(lat2) ) 
              * cos( radians( lat1 ) ) 
              * cos( radians( lng1 ) - radians(lng2) ) 
              + sin( radians(lat2) ) 
              * sin( radians( lat1 ) )
        ) ) as distance 
FROM your_table;

Mysql Function :-

DELIMITER $$
CREATE FUNCTION `getDistance`(`lat1` VARCHAR(200), `lng1` VARCHAR(200), `lat2` VARCHAR(200), `lng2` VARCHAR(200)) RETURNS varchar(10) CHARSET utf8
begin
declare distance varchar(10);

set distance = (select (6371 * acos( 
                                     cos( radians(lat2) ) 
                                   * cos( radians( lat1 ) ) 
                                   * cos( radians( lng1 ) - radians(lng2) )       
                                   + sin( radians(lat2) ) 
                                   * sin( radians( lat1 ) )
                ) ) as distance); 

if(distance is null)
then
 return '';
else 
return distance;
end if;
end$$
DELIMITER ;

How to use in your PHP Code

SELECT getDistance($lat1,$lng1,$lat2,$lng2) as distance 
FROM your_table.

Almost a decade later, this function gives THE SAME results as Google Maps distance measurement. Thank you! — fernandojmartin, May 06 '20 at 15:40

score 14 · Answer 4 · edited Oct 25 '18 at 11:59

14

Here's a MySQL function that will take two latitude/longitude pairs, and give you the distance in degrees between the two points. It uses the Haversine formula to calculate the distance. Since the Earth is not a perfect sphere, there is some error near the poles and the equator.

To convert to miles, multiply by 3961.
To convert to kilometers, multiply by 6373.
To convert to meters, multiply by 6373000.
To convert to feet, multiply by (3961 * 5280) 20914080.

DELIMITER $$

CREATE FUNCTION \`haversine\`(

        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) RETURNS float
    NO SQL
    DETERMINISTIC
    COMMENT 'Returns the distance in degrees on the Earth between two known points of latitude and longitude. To get miles, multiply by 3961, and km by 6373'

BEGIN

    RETURN DEGREES(ACOS(
              COS(RADIANS(lat1)) *
              COS(RADIANS(lat2)) *
              COS(RADIANS(lon2) - RADIANS(lon1)) +
              SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
            ));

END;

DELIMITER;

edited Oct 25 '18 at 11:59

O. Jones

103,626
17
118
172

answered Apr 02 '17 at 11:16

Sloan Thrasher

4,953
3
22
40

Notice that the value `3961` is the distance on the surface of the earth you travel when you move one radian north or south. – O. Jones Oct 25 '18 at 12:00
I think it's 6371 instead of 6373. – Buffalo Jul 25 '20 at 09:46
Do not use the DEGREES call if you are converting to miles/km/etc, the conversion factors are in RADIANS, so you don't want to convert to degrees before multiplying by the conversion factor. – Andy Sep 22 '20 at 00:07
Thanks for the information about the conversion – RahulAN Dec 06 '20 at 16:53

score 4 · Answer 5 · answered Jun 23 '14 at 16:55

Not sure how your distance calculation is going on but you need to do a self join your table and perform the calculation accordingly. Something like this probably

select t1.id as userfrom, 
t2.id as userto, 
( 3959 * acos ( cos ( radians(31.589167) ) * cos( radians( t1.Latitude ) ) * 
cos( radians( t1.Longitude ) - radians(64.363333) ) + sin ( radians(31.589167) ) * 
sin( radians( t2.Latitude ) ) ) ) AS `distance` 
from table1 t1 
inner join table1 t2 on t2.city > t1.city

score 3 · Answer 6 · answered Feb 07 '20 at 22:19

IMPORTANT! Anyone using or copying these calculations MAKE SURE to use least(1.0, (...)) when passing the calculation to the acos() function. The acos() function will NOT take a value above 1 and I have found when comparing lat/lng values that are identical there are times when the calculations come out to something like 1.000002. This will produce a distance of NULL instead of 0 and may not return results you're looking for depending on how your query is structured!

This is CORRECT:

select round( 
  ( 3959 * acos( least(1.0,  
    cos( radians(28.4597) ) 
    * cos( radians(lat) ) 
    * cos( radians(lng) - radians(77.0282) ) 
    + sin( radians(28.4597) ) 
    * sin( radians(lat) 
  ) ) ) 
), 1) as distance
from locations having distance <= 60 order by distance

This is WRONG:

select round( 
  ( 3959 * acos( 
    cos( radians(28.4597) ) 
    * cos( radians(lat) ) 
    * cos( radians(lng) - radians(77.0282) ) 
    + sin( radians(28.4597) ) 
    * sin( radians(lat) 
  ) ) 
), 1) as distance
from locations having distance <= 60 order by distance

The highest rated answer also talks about this, but I wanted to make sure this was very clear since I just found a long standing bug in my query.

score 1 · Answer 7 · answered May 13 '18 at 04:26

Here's a formula I converted from https://www.geodatasource.com/developers/javascript

It's a nice clean function that calculates the distance in KM

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `FN_GET_DISTANCE`(
lat1 DOUBLE, lng1 DOUBLE, lat2 DOUBLE, lng2 DOUBLE
) RETURNS double
BEGIN
    DECLARE radlat1 DOUBLE;
    DECLARE radlat2 DOUBLE;
    DECLARE theta DOUBLE;
    DECLARE radtheta DOUBLE;
    DECLARE dist DOUBLE;
    SET radlat1 = PI() * lat1 / 180;
    SET radlat2 = PI() * lat2 / 180;
    SET theta = lng1 - lng2;
    SET radtheta = PI() * theta / 180;
    SET dist = sin(radlat1) * sin(radlat2) + cos(radlat1) * cos(radlat2) * cos(radtheta);
    SET dist = acos(dist);
    SET dist = dist * 180 / PI();
    SET dist = dist * 60 * 1.1515;
    SET dist = dist * 1.609344;
RETURN dist;
END$$
DELIMITER ;

You'll also find the same function in different languages on the site;

score -2 · Answer 8 · answered May 23 '21 at 09:29

Maybe someone will come in handy, I managed to implement my task through the FN_GET_DISTANCE function:

SELECT SUM (t.distance) as Distance FROM
(SELECT (CASE WHEN (FN_GET_DISTANCE (Latitude, Longitude, @OLDLatitude, @OLDLongitude)) BETWEEN 0.01 AND 2 THEN
FN_GET_DISTANCE (Latitude, Longitude, @OLDLatitude, @OLDLongitude) ELSE 0 END) AS distance,
IF (@OLDLatitude IS NOT NULL, @OLDLatitude: = Latitude, 0),
IF (@OLDLongitude IS NOT NULL, @OLDLongitude: = Longitude, 0)
FROM `data`, (SELECT @OLDLatitude: = 0) var0, (SELECT @OLDLongitude: = 0) var1
WHERE ID_Dev = 1
AND DateTime BETWEEN '2021-05-23 08:00:00' AND '2021-05-23 20:00:00'
ORDER BY ID DESC) t;

Find distance between two points using latitude and longitude in mysql

8 Answers8

Linked

Related