Mysql function to find distance between two places using lat/long


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. #1 by Ryan McDermott on July 27, 2009 - 12:08 am

    how well would this work with 1,000,000+ locations to sort through?

  2. #2 by Imran on July 27, 2009 - 2:02 pm

    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. #3 by jb on October 23, 2009 - 1:28 pm

    Could this function be written to calculate driving distance?

  4. #4 by Imran on October 23, 2009 - 1:48 pm

    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. #5 by jb on October 24, 2009 - 4:02 am

    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. #6 by jb on October 24, 2009 - 8:44 am

    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. #7 by Imran on October 24, 2009 - 9:04 am

    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. #8 by jb on October 24, 2009 - 9:37 am

    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. #9 by js on November 24, 2009 - 5:23 am

    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. #10 by imran on November 24, 2009 - 12:56 pm

    Sounds interesting. I’ll have a look later. A bit busy at the moment. Thanks for sharing.

  11. #11 by Tom on May 11, 2010 - 2:20 am

    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. #12 by Imran on May 11, 2010 - 3:10 am

    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. #13 by patrick on August 5, 2010 - 1:09 pm

    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. #14 by Richard on August 24, 2010 - 7:59 pm

    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. #15 by Paul on January 10, 2011 - 11:11 pm

    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. #16 by Paul on January 10, 2011 - 11:15 pm

    Paul :
    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 ) ) * 6366 (km)
    lat and lng should be in radians, so * PI()/180 when degrees

    edit: multiply by earth radius, e.g. 6366km

  17. #17 by Imran on January 10, 2011 - 11:57 pm

    Thanks Paul for sharing. It will surely help others,

  18. #18 by Bill on February 23, 2011 - 7:20 am

    Imran, how are you storing the lat/long in `coordinates`?

  19. #19 by Imran on February 23, 2011 - 1:10 pm

    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. #20 by Mike on May 24, 2011 - 7:52 pm

    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. #21 by Stephen on December 9, 2011 - 9:00 pm

    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. #22 by Stephen on December 9, 2011 - 9:01 pm

    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. #23 by Imran on December 10, 2011 - 8:01 am

    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:

    select ZipCode from zip where GetDistance(“10.1357002, 49.9225563, 0”, concat_ws(’, ’, Longitude, Latitude, ’0’)) <= 40;

    Thanks
    Imran

  24. #24 by Stephen on January 17, 2012 - 4:59 am

    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. #25 by Imran on January 17, 2012 - 10:13 am

    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.

  26. #26 by HobieCat on July 12, 2012 - 10:50 pm

    Hi, thanks for your great work!!

    In order to have the distance in km (kilometers) is it enough to substitute the * 60 *1.1515 with * 6371?

    Pls note that 6371 is the mean radius of the earth modeled as a spehre expressed in km.

  27. #27 by Imran on July 13, 2012 - 10:16 am

    Hi HobieCat,

    Yes you can substitute with 6371 to get the distance in km.
    Thanks for sharing your thoughts.

    Imran

  28. #28 by Asharaf on August 25, 2012 - 12:31 am

    Thanks Imran. It’s a great a post !!!……..

  29. #29 by Asharaf on August 25, 2012 - 12:31 am

    Thanks Imran. It\’s a great a post !!!……..

  30. #30 by Debbie on August 28, 2012 - 10:08 pm

    This is an interesting post. I am currently using a simliar formula to calculate distance and I am close but not quite there. Could you make any suggestions to my formula? I am formatting out to 9 decimals and converting to miles. Thanks in advance. PS Is POW an abbreviation for Power?

    =SQRT(((lat2-lat1)^2)+((Jlong2-long1)^2))

  31. #31 by Anton on February 13, 2013 - 10:00 pm

    Maybe too much time has passed since the last comment, but what the hell. I really want to use this function, and my php is up to par but i´m a noob at MYSQL, Tried looking it up but I get this error when trying to add this function in workbench or phpmyadmin
    Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

  32. #32 by asdasd on February 10, 2017 - 1:55 pm

    sdfsdf

(will not be published)