Calculate the distance(km) between two latitude and longitude using MySQL

0
votes

this is Lat and Lon example in mysql database

ID | Lat    | Lon
1  | 3.1412 | 101.6865
2  | 1.2897 | 103.8501

The formula below calculates the distance between two Lat (latitudes) and Lon (longitudes) and gets the result that I want. But this formula doesn't extract my Lat and Lon from the database. I would like to ask, how to get the distance result by using ID and calculate two Lat and Lon? My friend told me using Join table, but I still don't get the answer..

SELECT ROUND(6353 * 2 * ASIN(SQRT( POWER(SIN((3.1412 -
    abs(1.2897)) * pi()/180 / 2),2) + COS(3.1412 * pi()/180 ) * COS(
    abs(1.2897) *  pi()/180) * POWER(SIN((101.6865 - 103.8501) *  pi()/180 / 2), 2) )), 2);

Thanks for advance

2
mysqlgislatitude-longitude

2 Answers

4
votes

You can join the same table two time one for Id 1 and one for id 2 assigning different alias name then use the related column name 

  SELECT ROUND(6353 * 2 * ASIN(SQRT( POWER(SIN((a.lat -
      abs(b.lat)) * pi()/180 / 2),2) + COS(a.lat * pi()/180 ) * COS(
      abs(b.lat) *  pi()/180) * POWER(SIN((a.lng - b.lng) *  pi()/180 / 2), 2) )), 2);
  from my_table a
  inner join my_table b on a.id = 1 and b.id = 2
0
votes

It is working for me

SELECT *,(((acos(sin((12.942999*pi()/180)) * sin((`Lat`*pi()/180))+cos((12.942999*pi()/180)) * cos((`Lat`*pi()/180)) * cos(((74.82994- `Lon`)* pi()/180))))*180/pi())*60*1.1515*1.609344 ) as distance FROM ven_vendor HAVING distance <= 10