Nearest locations with latitude and longitude

2
votes

I just have latitude and longitude of a store, now I want to find out all the stores near by in 1 hour drive or say at 10 KM distance.

I have table as, 

Id, storeName, store_lat, store_lng

Say I have selected lat and lng of store 1 and I want to find out all the nearby stores. How to do that using php/mysql.

I have checked many post on SO but when I am running query, it's returning 0 results all the time.

Please help, thanks!

4
phpmysql
I suspect you might get answers more quickly if you post your query. It might be something wrong with the query, or it might be the general approach you're taking. – Adam Davis
possible duplicate of Get results from mysql based on latitude longidude, see as well this Geo Distance Search with MySQL Tutorial. – hakre
if you want simple and fast try something like this stackoverflow.com/questions/3983325/… – Jon Black
Hope it helps... stackoverflow.com/questions/11586708/… – jsist

4 Answers

4
votes

Assuming you get $lat and $long as latitidue/longitude of your starting point:

$sql = 'SELECT *, (6371 * acos(cos(radians(' . $lat . ')) * cos(radians(latitude)) * cos(radians(longitude) - radians(' . $lng . ')) + sin(radians(' . $lat . ')) * sin(radians(latitude)))) AS distance from table_name WHERE distance < 10';
1
votes

One approach is to take your input lat/long pair, create a range, then query for all records that fall in that range:

Distance = 0.1; // Range in degrees (0.1 degrees is close to 11km)
LatN = lat + Distance;
LatS = lat - Distance;
LonE = lon + Distance;
LonW = lon - Distance;

...Query DB with something like the following:
SELECT *
FROM table_name
WHERE 
(store_lat BETWEEN LatN AND LatS) AND
(store_lon BETWEEN LonE AND LonW)

The query should find everything where the stored lat is less than LatN and greater than LatS, and where the stored lon is less than LonE and greater than LonW.

You might then do a real distance calcuation for the returned results because the above search gives you a box, and locations in the corner will be greater than 0.1 degrees away, as the crow flies.

Use a map API with directions to get the driving distance, if "as the crow flies" isn't good enough.

1
votes
SELECT * FROM `table` WHERE acos(sin(LATITUDE) * sin(`store_lat`) + cos(LATITUDE) * cos(`store_lat`) * cos(`store_lng` - (LONGITUDE))) * 6371 <= 1000;

Just replace LONGITUDE and LATITUDE.

Distance here is in km - it's currently set to 1000. This can be changed as needed too.

I've used this on a few scripts so far, seems to work pretty well, although can be slow sometimes. The issue I believe is that it can't utilize indices because the WHERE clause is so complex.

0
votes
function inRange ( $lat1, $lng1, $lat2, $lng2, $range = 100 )
{
        return ( 6371 * acos( cos( deg2rad( $lat1 ) ) * 
               cos( deg2rad( $lat2 ) ) * 
               cos( deg2rad( $lng2 ) - 
               deg2rad( $lng1 ) ) + 
               sin( deg2rad( $lat1 ) ) * 
               sin( deg2rad( $lat2 ) ) ) ) <= $range;
}

This generates distance. You can convert it to MySQL query.