如何在纬度和经度的帮助下找到30公里范围内的位置[关闭]

Question

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

Answer 1

If you do not need a real precise (but fast!) result, you could try the following:

• You have a given lat/lng of a place to search around
• Draw a rectangle around with that place in the middle
• Search in the database if your stores fit within the range

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.