Calculate Distance In Mysql with Latitude and Longitude
So you have a whole table full of members or places with latitude and longitude’s associated with them. Just replace the $lat and $lon with the center point you want to find distances from. You can also change the distance<=10 to a number you want to search from. This will limit your results to all results that are under 10 miles from the starting point
SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `members` HAVING `distance`<=’10′ ORDER BY `distance` ASC
About mike
Thanks for the great algorithm! I added some back-ticks ( ` ) to your code to make it easier to read for the uninitiated. Anything denoted by a back-tick, (NOT the same as a single quote), means it is a field or table in your database table in this case the field names would be `lat` and `lon` and the table name would be `members`
————————-
SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS($lat * PI() / 180) * COS(`lat` * PI() / 180) * COS(($lon – `lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM `members` HAVING distance<=’10′ ORDER BY distance ASC
Thanks for improving it.. I’ll add the backticks into the post
in this part: HAVING `distance`<=’10′
what are those characters around the number 10?
Those are a single quote
Anyway thanks for posting this, my problem is that I want to use this on a WordPress database where the coordinates are stored in the wp_postmeta table like this:
meta_id
post_id
meta_key
meta_value
11294
4448
_latitude
40.722039
11295
4448
_longitude
-74.004142
WHERE wp_posts.ID = wp_postmeta.post_id
If you happen to know how to write that query I would greatly appreciate that information!
What does the sql table look like to store the latitude and logitude information?
my lat/lon information was just 2 columns in the user profile table. They were both indexes and decimal types
If the table members is big you will have performance issues because of the full table scan.Take a look here for better solution
http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
actually you are completely wrong.. This is the query I run on Photoblog to find members around you and if you index the latitude and longitude columns it will never do a full table scan
I dont think so
My comment refer to the query you wrote:
SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `members` HAVING `distance`<=’10′ ORDER BY `distance` ASC
I assume your table design looks like this :
members
| member_id | latitude | longitude | … |
This particular query will never ever use an index because :
a) the query do not have where clause
b) the query have to calculate the distance for all the records in the table members to emit only those "HAVING `distance`<=’10′"
a) and b) means always full table scan on MySQL.
Try EXPLAIN on this query and see what it says
Take a look at the link a send to you to see how to optimize your query using "between".
regards
+----+-------------+---------+-------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+-------+-----------------------------+
| 1 | SIMPLE | members | range | id,lat,lon | lat | 4 | NULL | 21532 | Using where; Using filesort |
+----+-------------+---------+-------+---------------+------+---------+------+-------+-----------------------------+
My members table is around 800k rows and I around NYC where we have a ton of users. I agree I do need to redo the query to better optimize it but for now it runs at .2 seconds for an uncached query.
Hi i’ve tried to implement this on my site but the query doesn’t work?
What isn’t working?
This is what I’m using for the `distance`:
(DEGREES(ACOS(SIN(RADIANS(” . $user_data['user_lat'] . “)) * SIN(RADIANS(p.location_geolat)) + COS(RADIANS(” . $user_data['user_lat'] . “)) * COS(RADIANS(p.location_geolat)) * COS(RADIANS(” . $user_data['user_lon'] . ” – p.location_geolon)))) * 60 * 1.1515) AS `distance`
Do you know what you have to change in this equation to change the distance to kilometers instead of miles?
Hi Mike,
Looks good.
I too would be interested in understanding how to apply kilometres instead of miles.
Would you be able to help with a similar scenario – taking a series of points which represent an itinerary, find all the recorded locations within in a specified distance (eg: 25 Km) ?
Best regards
1 miles = 1.609344 kilometers
Times the output number by 1.609344 to get the value in Km