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
Hi Mike
here is my query which does not work, can you figure it out im soo lost
SELECT ((ACOS(SIN(-27 * PI() / 180) * SIN(latitude * PI() / 180) + COS(-27 * PI() / 180) * COS(latitude * PI() / 180) * COS((153 – longtitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM Postcode HAVING distance<=10 ORDER BY distance ASC ………this is the query
.Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '– longtitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM Pos' at line 1
Oh I am using Austalian postcode incase you needed to know
thanks
Alan
Yes.. it is a copy/paste issue.. go back to what you think the the – symbol and delete it and just re-type it again. It should work then
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!
set @latitude = 121.12; — center latitude
set @longitude = 4.20; — center longitude
set @distance = 10; — search distance
select p.ID, p.post_name, ((ACOS(SIN(@latitude * PI() / 180) * SIN(`latitude.meta_value` * PI() / 180) + COS(@latitude * PI() / 180) * COS(`latitude.meta_value` * PI() / 180) * COS((@longitude – `longitude.meta_value`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance
from wp_posts p
left join wp_postmeta latitude on latitude.post_id = p.ID and latitude.meta_key = ‘_latitude’
left join wp_postmeta longitude on longitude.post_id = p.ID and longitude.meta_key = ‘_longitude’
having distance < @distance;
I’m trying to do exactly the same thing (geo search for WP); I did use your function but seems it does not work.
Any idea ?
Here’s my code : http://pastie.org/1579691
You ever get this to work with wordpress custom meta?
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
My field types are FLOAT instead of decimal. I’m not getting expected results with your query. Is this why?
Nope since mine are floats. You are having another issue somewhere in the query or data
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.
Mortanon does have a point but Its a simple upgrade, and only needed on a big database. All you do is trim down the results your going to do the full crunching to by an approximate method. If you only care about results in a 10 mile radius than you can do a simple select with a 10 mile square box and then do the proper crunching. You could even approximate your 10 mile square box to start with and say I have no users futher north than 60 degrees so ill pick a 10 mile square box at that point by a difference in co-ordinates and use the same distance down south (even though it would be a bigger box in miles it would still trim down your database a lot!)
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
Hi, anyone know if that equation is faster in PL/SQL than normal SQL command? Also what is fastest equation?
thanks
Any idea why this would be returning an empty set, when there are plenty of records that should qualify for a given distance?
Mike,
Respect! That is truly awesome!
Thank you for sharing, you saved me hours of banging my head on my desk.
Cheers
glad it saved you some time!
Great post.
One shot workable solution.
Thanks
Chirag
I was searching this query. Thank you. But, I have a question. Is this Haversine formula? I tested this query. It works great. But Haversine Formula’s definition is little bit different. Isn’t it?
And, I tested it with same location. It didn’t return zero. Can you explain that for me?
Thank you, and sorry for my poor English.
Sorry, It was my fault. It returned 0.
Hi,
I am busy trying to do this in MySQL, but I wanted to confirm that the maths does what it says on the tin. So I drew a straight line on google and got the start end lat/lon and plugged it into various functions.
I am doing distances over short and long distances so it needs to cope with both. Sadly your example is not accurate.
I think its all to do with the curvature of the earth etc.
I am going to convert this:
http://www.movable-type.co.uk/scripts/latlong.html
into a stored procedure, which I’ll blog about and link back here.
Is it we are getting distance in MILES or in meters?
i m using following formula…
3956 * 2 * ASIN(SQRT( POWER(SIN((inLAT -
LATITUDE) * pi()/180 / 2), 2) +
COS(inLAT * pi()/180) * COS(LATITUDE * pi()/180) *
POWER(SIN((inLONG – LONGITUDE) * pi()/180 / 2), 2) )) as
distance
hi,
Does it works with SQL server also?
Regards,
Radhika
Thanks, works perfect…
Looks very nice, although i can’t get it working:
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘– lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM lp_praktij’ at line 1
I noticed the strange ‘– but that is not showing in my query, only in the error message
It’s just the minus character,
I know it’s to late, but it could help an other person
Hey guys, I’m have some issues with query:
user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘– longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `z’ at line 1 query: SELECT zip,((ACOS(SIN(33.786594 * PI() / 180) * SIN(latitude * PI() / 180) + COS(33.786594 * PI() / 180) * COS(latitude * PI() / 180) * COS((-118.298662 – longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `zip_codes` HAVING `distance`<=100 ORDER BY `distance` ASC in C:\inetpub\ECOM\Store\includes\common.inc(1695) : eval()'d code on line 65.
I think some of the problems were related to backticks rendering improperly in wordpress, but I can't figure out what is wrong now.. maybe an extra parenthesis somewhere?
oops.. here was my original query:
$zip_result = db_query(” SELECT zip,((ACOS(SIN(“.$lat.” * PI() / 180) * SIN(latitude * PI() / 180) + COS(“.$lat.” * PI() / 180) * COS(latitude * PI() / 180) * COS((“.$long.” – longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `zip_codes` HAVING `distance`<=".(int)$distance." ORDER BY `distance` ASC ");
You had the issue the next commenter had.. find the – delete it and put in place your own minus sign
Mike, great post that saved me some time, thanks! A few things:
In your original post above there is an en dash (–) character that should really be a minus sign. So those who copy-paste the SQL will get an error upon execution.
Also, here’s an HQL adaptation for users of Hibernate (with a Profile entity having properties latitude & longitude):
from Profile p where ((acos(sin(:latitude * pi() / 180) * sin(p.latitude * pi() / 180) + cos(:latitude * pi() / 180) * cos(p.latitude * pi() / 180) * cos((:longitude – p.longitude) * PI() / 180)) * 180 / pi()) * 60 * 1.1515) < :radius
And here is just the distance algorithm in Java:
((Math.acos(Math.sin(latitude * Math.PI / 180) * Math.sin(p.latitude * Math.PI / 180) + Math.cos(latitude * Math.PI / 180) * Math.cos(p.latitude * Math.PI / 180) * Math.cos((longitude – p.longitude) * Math.PI / 180)) * 180 / Math.PI) * 60 * 1.1515)
Cheers,
-b.e.
Thanks, I made the change
Nice – SQL works well. I created a function to make it easier to call.
create FUNCTION geo_distance (in_lat decimal(10,6), in_lon decimal(10,6), latitude decimal(10,6), longitude decimal(10,6))
returns decimal(10,3) DETERMINISTIC
return ((ACOS(SIN(in_lat * PI() / 180) * SIN(latitude * PI() / 180) + COS(in_lat * PI() / 180) * COS(latitude * PI() / 180) * COS((in_lon – longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) ;
–Sample Usage, assuming a table called Places with fields latitude & longitude:
select geo_distance(-34.017330, 22.809500, latitude, longitude) as distance_from_input from places;
nice function!
Heya, Mike, great script! Just wanna say thanks. You’ve helped me loads. One question though. How can I add a WHERE clause in there? Whenever I try it throws me an error. I wanna select by date, field name is startDate and variable name is $startDate…
Add the where after the FROM table and before the HAVING
Does this need any particular version of MySQL to work? I pasted in exactly as is and I just get a syntax error when executing it.
Never mind, I figured it out. When pasting the code into Toad it was converting the – character to something else. Weird..
syntax errors are usually due to the putting incorrect minus signs or ‘ in the post. Go through the query and replace them
Awesome, awesome code. Really appreciate the help. I’m just curious, how were you able to engineer this equation? Law of sines/law of cosines? Something more awesome I haven’t even thought of? Used to love trig in college.
Kevin
elevatedthird.com
I did it so long ago, I really do not remember. I know I got formulas from other websites and pieced it together in sql.
Hi, thx for a nice script.
I have bben using it for a while until today I discovered that if lon is example -1.567 then I would not get any results back. It works perfect on example 1.567 so if the lon field in mysql have a minus infront I would not get any results.
My sql looks like this.
$query = ‘SELECT t1.id, t1.name, t1.image, t1.address, t1.zip, t1.city, t1.country, t1.phone, t1.email, t1.url1, t1.url2, t1.latlng, t1.content, t1.ingress, t1.campaign, t1.ingress_short, ((ACOS(SIN(‘.$pos[0].’ * PI() / 180) * SIN(lat * PI() / 180) + COS(‘.$pos[0].’ * PI() / 180) * COS(lat * PI() / 180) * COS((‘.$pos[1].’ – `lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM locations AS t1 WHERE t1.client_id=’.$companyID.’ AND t1.active = “yes” AND t1.lat>1 AND lon>1 HAVING distance<='.$radius.' ORDER BY distance ASC '.$maxlimit;
Can someone see what is wrong? The mysql field is a float field. Is that ok?
Best Jim
Sorry, found the reason.. prety obious when lon>1 is set in the query
Blush
id – 1,
college – auburn,
zipcode – 36849,
url – http://www.auburn.edu,
ipeds is – 100858,
campus – city,
latitude – 32.59942,
logitude – -85.4881
given this with columns and values… how do you go about a script that will bring a bout the or m0st importantly a picture of the school with the lat, log and url…. and calling it from your database for your website ?
let me add.. you’re are doing a nice job give and helping humanity
Sir, i need to calculate the Positions of Latitude and Longitude in JAVA using Edit+…… ll u plz post me a querry for that.
Quite possibly one of the most impressive MySQL calculations I’ve ever seen. Thanks!
I have used this before from a different set of software. It is very cool. One thing I am unsure of though is what type of miles. Statute or nautical. Surely if it is Nautical miles as it is talking in terms of lat and lon then it is time 1.852 to get the distance in Kilometres?
hello. i want to ask you if your formula applies in my case. i want to build a site and when you visit the page of .. tower of london(just an example) i want to know what hotels are in a range of 10 km from the tower of london based on the coordonates of each. thank you
Im using http://postcodepal.com/?page=mysql-query
It seems to work much better on larger databases.
Hey Mike, Thank you for this. It was really helpful. I’m just stuck on one thing. I want to show the distance of each result as it pertains to my location. I’m guessing that this is something really simple that I am just failing at.
Thanks in advance.
That query already does that.. it returns the distance from a member.. You pass in your current lat/lon via the $lat/$lon in the query.. so those are like PHP vars to form the query.