MySQL Query for Nearest Location Based Service based on Haversine Formula

  • Posted on: 2 November 2014
  • By: oon
Location Based Service with MySQL

MySQL support geospatial since version 5.0.6 with spatial extensions [3], but since MySQL 5.6, it has several new geo functions [2].

But what if we only want to find nearest location that the coordicate (latitude and longitude) stored at MySQL?

You still can use Haversine Formulate to calculate the nearest location [3]. Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.


SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

the question is how efficient is this query? :)

if you are planning to extensively use this query, than from my opinion, you should find another alternative, using NoSQL for example. ;)

[1]https://developers.google.com/maps/articles/phpsqlsearch_v3
[2]http://www.percona.com/blog/2013/10/21/using-the-new-spatial-functions-i...
[3]http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.