select longitude and latitude from table in MySQL

Question

There's a table called user, and in it, there are some records as following

name  -- age  --  longitude  -- latitude
jack1     24        12.00000       13.0000
jack2     23        16.00000       11.0000
jack3     22        10.00000       11.0000
jack4     25        12.00000       13.0000
...//more records like above

and I have another longitude and latitude, I want to select the name, age, distance from the user order by distance asc, the distance is the user's longitude and latitude with mine, so how can I write this?

score 1 · Accepted Answer · edited May 23 '17 at 12:21

You can calculate distance (in miles) this way from latitude & longitude. Say your latitude & longitude is 40.5,80.5 respectively.See related example for idea here Fastest Way to Find Distance Between Two Lat/Long Points

SELECT name, age, (3959 * acos(cos( radians(40.5)) * cos(radians(latitude)) 
* cos(radians(longitude) -  radians(80.5)) + sin( radians(40.5)) *    
sin(radians(latitude)))) AS distance 
FROM user
ORDER BY distance ASC

Haversine Formula

score 0 · Answer 2 · answered May 02 '15 at 03:35

You can use great circle distance formula. Haversine.

Assuming your lat/lon is 37,-122

SELECT name, age, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
    * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) 
AS distance 
FROM `user`     
ORDER BY distance

score 0 · Answer 3 · answered May 02 '15 at 08:09

As is already mentioned, the Haversine Formula is what you want to use to calculate distances between two lat/long points. You can implement it as a stored procedure like so:

delimiter //
create function DistanceInKm(
        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) returns float
    NO SQL DETERMINISTIC
begin
    return degrees(acos(
              cos(radians(lat1)) *
              cos(radians(lat2)) *
              cos(radians(lon2) - radians(lon1)) +
              sin(radians(lat1)) * sin(radians(lat2))
            )) * 111.045;
END//
delimiter ;

Use 69 instead of 111.045 if you want the distance in miles instead of kilometers.

You can then use this stored procedure in your query in the following way:

select *, DistanceInKm(TARGET_LAT, TARGET_LONG, user.latitude, user.longitude) distance
  from user
  order by distance asc;

Where TARGET_LAT and TARGET_LONG are the coordinates of the point you are comparing against. Using a stored procedure in the query instead of the formula adds a ton of readability, and also saves you from any bugs introduced by a typo in your formula (so long as you get the stored proc right, of course)

select longitude and latitude from table in MySQL

3 Answers3