How to find location in the range of say 30 km of location with the help of latitude and longitude

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

I have no Idea how to achieve this, but if you can provide me any formula of trigonometry which can be used in PHP or MySQL, it would be really appreciable — Jaskaran Singh, Jul 07 '15 at 11:59
possible duplicate of [How do I calculate distance between two latitude-longitude points?](http://stackoverflow.com/questions/27928/how-do-i-calculate-distance-between-two-latitude-longitude-points) — halfer, Jul 07 '15 at 12:43
Also [see this](https://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula), for SQL solutions. — halfer, Jul 07 '15 at 12:44
@halfer I am not calculating distance between two points but finding the points in range of say 30 km, I have only one pair of lat long to pass to a query. — Jaskaran Singh, Jul 07 '15 at 12:54
See my second link - it shows you how to do that in a SQL query. You can put in a clause to filter distances longer than 30km, and order by distance ascending. — halfer, Jul 07 '15 at 12:57

dieBeiden · Accepted Answer · 2015-07-07T12:14:47.097

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.

Thanks! This seems solved my problem, This type anwer was required which Can be embedded in where condition of my long sql query. — Jaskaran Singh, Jul 07 '15 at 13:10

score 1 · Answer 2 · answered Jul 07 '15 at 12:05

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.

score 1 · Answer 3 · answered Jul 07 '15 at 12:12

1

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;

Fiddle demo

answered Jul 07 '15 at 12:12

Ullas

11,450
4
33
50

How to find location in the range of say 30 km of location with the help of latitude and longitude

3 Answers3