Loading ...Sometimes we need to find out list of places that are within a certain radius from a center place where coordinates of the places are saved in the database. Now we have 2 solutions for this – either loop through all the places find the distance from the center point and keep the places that have distance less or equal to the radius, or make an sql function to find the distance of two places, and select the places having distance less or equal to the radius. Obviously the second option is better than the first one. So I have written a Mysql function that does the work for you.
In my case, the coordinates were saved in the database as a string of the form "10.1357002, 49.9225563, 0". This is the standard format of coordinates that is used by many (for example Google map). The first element is the longitude, second one is latitude and we can ignore the third (always 0). So here is the Mysql function that returns the distance between 2 coordinates in Miles.
DELIMITER $$
DROP FUNCTION IF EXISTS `GetDistance`$$
CREATE FUNCTION `GetDistance`(coordinate1 VARCHAR(120), coordinate2 VARCHAR(120))
RETURNS VARCHAR(120)
BEGIN
DECLARE pos_comma1, pos_comma2 INT;
DECLARE lon1, lon2, lat1, lat2, distance DECIMAL(18,12);
select locate(',', coordinate1) into pos_comma1;
select locate(',', coordinate1, pos_comma1+1) into pos_comma2;
select CAST(substring(coordinate1, 1, pos_comma1-1) as DECIMAL(18,12)) into lon1;
select CAST(substring(coordinate1, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(18,12)) into lat1;
select locate(',', coordinate2) into pos_comma1;
select locate(',', coordinate2, pos_comma1+1) into pos_comma2;
select CAST(substring(coordinate2, 1, pos_comma1-1) as DECIMAL(18,12)) into lon2;
select CAST(substring(coordinate2, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(18,12)) into lat2;
select ((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lon1 - lon2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) into distance;
RETURN distance;
END$$
DELIMITER ;
Example Usage:
Lets say you have to find out all the postcodes that are in 40 miles radius from a place having coordinate "10.1357002, 49.9225563, 0". You have a table POSTCODES having fields id, postcode, coordinates. So your sql should look like this:
select id, postcode from POSTCODES where GetDistance("10.1357002, 49.9225563, 0", coordinates) <= 40;
Isn’t it simple?
#1 by Ryan McDermott on July 27th, 2009
how well would this work with 1,000,000+ locations to sort through?
#2 by Imran on July 27th, 2009
Good question!
I just tried a simple php script today that executes the select statement with 26 records in the table. It took 0.004187 ms. So simple calculation says, it should take around (0.004187/26) * 1000000 = 161.04ms = .16 sec to execute with 1,000,000 record. Is that good enough?
Let me know if this can be optimized somehow.
#3 by jb on October 23rd, 2009
Could this function be written to calculate driving distance?
#4 by Imran on October 23rd, 2009
This is the simplest form of finding perpendicular distance between 2 locations. To find out the driving distance, you need to know the lat/long of all the nodes of the roads and sum up the distances of all the nodes.
In that case you may use this function with some loops over the nodes.
#5 by jb on October 24th, 2009
I think I will just to the function as it is. I have way to many records as it is. I appreciate you posting this function here, it will be very helpful.
#6 by jb on October 24th, 2009
To use this function with lat and long in separate columns would I just do something like :
CREATE FUNCTION `GetDistance`(lat VARCHAR(120),long VARCHAR(120), coordinate2 VARCHAR(120))
RETURNS VARCHAR(120)
BEGIN
DECLARE pos_comma1, pos_comma2 INT;
DECLARE lon1, lon2, lat1, lat2, distance DECIMAL(12,8);
select locate(lat) into pos_comma1;
select locate(long) into pos_comma2;
select CAST(long) as DECIMAL(12,8)) into lon1;
select CAST(lat) as DECIMAL(12,8)) into lat1;
…..
?
#7 by Imran on October 24th, 2009
Yes, it should work like that. Let me know how it performs with large number of data. The database I used it for still does not have a good number of data.
Good to know that it helped you
#8 by jb on October 24th, 2009
I will do that. I just realized that I don’t have the latitude and longitude values for the majority of my database. I probably wont have a good number to go through for quite some time.
#9 by js on November 24th, 2009
Something funny I noticed about this code. When you use ORDER By getDistance(.. it orders the results with a split between east and west. All results to the east are listed before the west. It\’s rather strange.
Example: 8, 22, 28, 2, 123.
I’m not sure how to go about fixing that.
#10 by imran on November 24th, 2009
Sounds interesting. I’ll have a look later. A bit busy at the moment. Thanks for sharing.
#11 by Tom on May 11th, 2010
great… thanks. Also, the ad on the left hand side of the page is a bit obtrusive. It blocks part of the content of your article. I had to use firebug to remove it so I could see the full article. Please consider an alternative placement
#12 by Imran on May 11th, 2010
Sorry that the ad was bugging, which FF version you used, I fixed the position so that it does not come over the content.
#13 by patrick on August 5th, 2010
Hi Imran,
See great to find your blog on your answer to the finding distance from long/lat from database. An very interested to find out how to link this to finding distance between database coordinates and a jpeg photo with long/lat in its EXIF. Can you reply me through my email. Thanks.
Patrick
#14 by Richard on August 24th, 2010
This is a cracking procedure. Just replaced our out of date and very inaccurate current measurement script with this and it’s yard perfect where ever in the UK you run it.
top stuff!
#15 by Paul on January 10th, 2011
For short distances or when precision is less important, we could use the following approximation to improve performance:
sqrt( pow( sin( lat1 – lat2 ), 2 ) + pow( sin( lng1 – lng2), 2 ) )
lat and lng should be in radians, so * PI()/180 when degrees
#16 by Paul on January 10th, 2011
edit: multiply by earth radius, e.g. 6366km
#17 by Imran on January 10th, 2011
Thanks Paul for sharing. It will surely help others,
#18 by Bill on February 23rd, 2011
Imran, how are you storing the lat/long in `coordinates`?
#19 by Imran on February 23rd, 2011
I have considered the lat/long as a varchar(120) field that contains lat/long as “10.1357002, 49.9225563, 0″ format (the format google uses).
#20 by Mike on May 24th, 2011
Imran, I tried your function and tested it against a couple of online tools and found that if I run the function on 2 points (“33.745,-84.389,0″, “34.037,-84.576,0″) I get 13.06459190 miles. Which is off by about 10 miles based on other online tools (including gmaps). However, when I pull out your query and run it outside of the function manually I get the correct distance: 22.8477833829375 miles. Wandering what’s up but more importantly I prefer running the sql rather than the function so thanks for posting this.
#21 by Stephen on December 9th, 2011
Imran,
I am trying to use a non-scalar value for the value of “coordinates” in your example above, and it is not working. How do you use the function in a query of a table where “coordinates” is a non-scalar query? My query is below:
mysql> select ZipCode from zip where GetDistance(“10.1357002, 49.9225563, 0″, (select concat_ws(‘, ‘, Longitude, Latitude, ‘0′) from zip)) <= 40;
ERROR 1242 (21000): Subquery returns more than 1 row
#22 by Stephen on December 9th, 2011
Imran,
I am trying to use a non-scalar value for the value of \"coordinates\" in your example above, and it is not working. How do you use the function in a query of a table where \"coordinates\" is a non-scalar query? My query is below:
mysql> select ZipCode from zip where GetDistance(\"10.1357002, 49.9225563, 0\", (select concat_ws(\’, \’, Longitude, Latitude, \’0\’) from zip)) <= 40;
ERROR 1242 (21000): Subquery returns more than 1 row
#23 by Imran on December 10th, 2011
In your query,
select ZipCode from zip where GetDistance(“10.1357002, 49.9225563, 0″, ( select concat_ws(’, ’, Longitude, Latitude, ’0’) from zip )) <= 40;
you can see it can return multiple results as there is no condition to limit the number of result to be one. Try the following if that helps:
Thanks
Imran
#24 by Stephen on January 17th, 2012
The code function works great for me. The only problem is that I am getting the following warning:
Data truncated for column ‘distance’ at row X
where X is the row number. It appears that the variable distance is being truncated because it is too large for type DECIMAL(12,8). I am wondering if I should turn the warnings off or make a change to DECIMAL.
#25 by Imran on January 17th, 2012
Do not ignore the warning. Better you should change the distance to DECIMAL(18,12) otherwise it may return some distorted result.
I have updated the function, please update it. Thanks for pointing it out.