CODEWITHSUNDEEP
phpmysql
djmzfKnm
djmzfKnm

Reputation: 27185

Nearest locations with latitude and longitude

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!

Upvotes: 2

Views: 5192

Answers (4)

Massimiliano Arione
Massimiliano Arione

Reputation: 2466

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';

Upvotes: 4

Nick
Nick

Reputation: 6346

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.

Upvotes: 2

Adam Davis
Adam Davis

Reputation: 93565

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.

Upvotes: 1

Ernestas Stankevičius
Ernestas Stankevičius

Reputation: 2493

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.

Upvotes: 0

Related Questions