I have lot of stores in my database table with their locations (lat and long). Now my requirement is to fetch the all the stores which are in the range of say 30 km.
Please see the image of table below. I have also attached MySQL file of the same.
store_id store_name lat lng
416 Modissa 47.374757 8.538985
916 A.C. Bang Pelze 47.367312 8.540046
917 Arbijoux 47.367312 8.540046
918 VP Bank (Schweiz) AG at Bahnhofstrasse 3 47.367537 8.539934
919 Pelzparadies Wyssbrod 47.367537 8.539934
920 ZKB 47.36895 8.542147
921 Weinbeg Damenmode 47.368943 8.539316
922 Kochoptik 47,368564 6.539617
923 Nouvelle Boutique 47.368639 8.539533
924 Trois Pommes 47.369897 8.540631
925 Lords of Sweden 47.205619 8.422326
926 Weinberg Herrenmode 47.368943 8.539316
927 Dior 47.305422 8.899034
928 Stefano Ricci 47.371972 8.5386
929 Villa Grisebach Auktionen 47.368505 8.539898
If you do not need a real precise (but fast!) result, you could try the following:
1.00 lat = ~110.54 km
1.00 lng = ~(111.32 * cos(deg2rad($lat)))
With that given, you may calculate your min/max lat/lng for the rectangle. To be more precise, here's my code for that:
$radiusLat = 30 / 110.54;
$radiusLng = 30 / (111.32 * cos(deg2rad($lat)));
$latMin = $lat - $radiusLat;
$latMax = $lat + $radiusLat;
$lngMin = $lng - $radiusLng;
$lngMax = $lng + $radiusLng;
$sqlWhere = "(lat >= '{$latMin}' AND lat <= '{$latMax}' AND lng >= '{$lngMin}' AND lng <= '{$lngMax}')";
P.S.: Do not forget to set an index to lat and lng columns.
You can use the Haversine formula to find out how far are this points from your coordinates center.
This is in TSQL (SQL SERVER) but I think with minor changes, will work in MySQL:
DECLARE @centerLat FLOAT
DECLARE @centerLng FLOAT
SET @centerLat = -24.184263 -- or whatever
SET @centerLng = -65.303147 -- or whatever
SELECT lat, lng,
(6371000 * acos(cos(PI() * lat / 180.0)
* cos(@centerLat) * cos(@centerLng - (PI() * lng / 180.0))
+ sin(PI() * lat / 180.0) * sin(@centerLat))
) as distanceFromCenter
FROM myTable
Now you only need to filter the rows that you need.
Query
select *,(((acos(sin((47.374757*pi()/180)) *
sin((lat*pi()/180))+cos((47.374757*pi()/180)) *
cos((lat*pi()/180)) * cos(((8.538980- lng)*
pi()/180))))*180/pi())*60*1.609344
) as distance
from stores
having distance <= 30;