MySQL filter data based on distance and calculate that distance

Question

I have a use-case where the user pass his location (latitude and longitude) to the back-end, I have to get that location and find all the restaurant with in Miles radius area for his location at the same time I have to give the distance to each restaurant from his location as well.

My restaurant table.

res_name    |   lat         |   lng
--------------------------------------------------------------
McDonalds'  |   6.8705452   |   79.884038
KFC     |   6.8705452   |   79.884038
Burger king |   6.8686279   |   79.8873961
--------------------------------------------------------------

To find the records which is matching which is with in Miles radius for user provided coordinates I can use this query

SELECT
  id, (
    3959 * acos (
      cos ( radians(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
  ) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;

this also I got from a stackoverflow question and it works. Now the my problem is how do I get the distance between user location to restaurant location for each matched results?

I hope my questtion is clear to you.

User will pass his current location. Based on that location I need to filter restaurants with in 30 miles radius area. Same time I have to say distance for restaurant from his current location as well.

My expected output will be like this. You have two restaurants with in 30 miles area. for the KFC you have distance 12 miles. for the McDonalds' you have distance 29 miles.

How do I modify that query to achieve my requirement?

Pathum Kalhan Pathum Kalhan · Accepted Answer · 2018-11-26T05:02:49

For finding distance part

SELECT test2.restaurants.res_name, 
   111.111 *
    DEGREES(ACOS(COS(RADIANS(6.9117))
         * COS(RADIANS(test2.restaurants.lat))
         * COS(RADIANS(79.8646 - test2.restaurants.lng))
         + SIN(RADIANS(6.9117))
         * SIN(RADIANS(test2.restaurants.lat)))) AS distance_in_km
  FROM test2.restaurants;

MySQL filter data based on distance and calculate that distance

1 Answers