# 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

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

So what is the type of returning value ? what kind of distance ? kilometers? miles?

miles

thats a conflict , replace the ‘_’ withe a minus sign ‘-’

it is a conflict , just replace the the ‘–’ with a minus sign ‘-’ in (153 – longtitude)

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 tableand before theHAVINGDoes 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.

GREAT! Practically saved my life haha

Hi,

I am Using a same algorithm like this.

i am getting Results while querying in mysql window but….

when i am using this with hibernate i am getting

Caused by: java.sql.SQLException: Column ‘column name’ not found.

can you please help me(RAJUMUDDANA@GMAIL.COM) how to work out this algorithm using hibernate.

Thanks & Regards

raju.

How can I echo the actual distance between starting point and the sql results in miles? i tried something like echo $distance; but it wont work,.. how can i do it ? thanks

Sounds like a php problem and not the query.. you iterate through the results and echo it out

What a great algorithm. I needed exactly the same.

I think some of the problems readers are having s that the ticks are formatted a little weird in the above code examples.

Here’s what I have working:

# Ruby

SELECT ((ACOS(SIN(#{self.latitude} * PI() / 180) * SIN(`latitude` * PI() / 180) + COS(#{self.latitude} * PI() / 180) * COS(`latitude` * PI() / 180) * COS((#{self.longitude} – `longitude`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM `users` HAVING distance <= `5` ORDER BY distance ASC

Thank you so much for the algorithm. It really saved my day.

I tried to take this code a step further, but I have done something wrong somewhere. Basically, on the site, the user inputs a five digit zip code. In the code below I have a query that get s the latitude and longitude from a zip code table (ZIP_FULL), then tries to use these fields in the distance query. I am stepping way out of my knowledge base, learning fast too, but can you give me some pointers as to what I have done wrong? I would really appreciate it!

$colname_LOCATION = “-1″;

if (isset($_POST['ZIPCODE'])) {

$colname_LOCATION = $_POST['ZIPCODE'];

}

mysql_select_db($database_XMASTREE, $XMASTREE);

$query_LOCATION = sprintf(“SELECT * FROM ZIP_FULL WHERE zip_code = %s”, GetSQLValueString($colname_LOCATION, “int”));

$LOCATION = mysql_query($query_LOCATION, $XMASTREE) or die(mysql_error());

$row_LOCATION = mysql_fetch_assoc($LOCATION);

$totalRows_LOCATION = mysql_num_rows($LOCATION);

$lat = $row_LOCATION['latitude'];

$lon = $row_LOCATION['longitude'];

mysql_select_db($database_XMASTREE, $XMASTREE);

$query_DIST = “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";

$DIST = mysql_query($query_DIST, $XMASTREE) or die(mysql_error());

$row_DIST = mysql_fetch_assoc($DIST);

$totalRows_DIST = mysql_num_rows($DIST);

What is ” * 60 * 1.1515″ in formula?

converting degrees to miles