How to find nearest location using latitude and longitude from SQL database?

by

Finding locations nearby with MySQL

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.

Table Structure :

id,name,address,lat,lng

NOTE – Here latitude = 37 & longitude = -122. So you just pass your own.

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

You can find details here.

More Related Contents:

  1. 1052: Column ‘id’ in field list is ambiguous
  2. Codeigniter – multiple database connections
  3. How to add levenshtein function in mysql?
  4. How to search JSON data in MySQL?
  5. Hierarchical queries in MySQL
  6. MySQL > Table doesn’t exist. But it does (or it should)
  7. #1071 – Specified key was too long; max key length is 1000 bytes
  8. Is the primary key automatically indexed in MySQL?
  9. Is there ANY_VALUE capability for mysql 5.6?
  10. Cumulative sum over a set of rows in mysql
  11. MySql Error 150 – Foreign keys
  12. Set NOW() as Default Value for datetime datatype?
  13. No connection could be made because the target machine actively refused it (PHP / WAMP)
  14. Add Auto-Increment ID to existing table?
  15. MySQL SELECT DISTINCT multiple columns
  16. SQL DELETE with JOIN another table for WHERE condition
  17. How to connect to a MySQL Data Source in Visual Studio
  18. DOs and DONTs for Indexes [closed]
  19. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  20. How do I get the first day of the week of a date in mysql?
  21. How to insert images from database?
  22. MySQL – ORDER BY values within IN()
  23. MySQL convert timediff output to day, hour, minute, second format
  24. How do I rename a MySQL database (change schema name)?
  25. MySQL my.cnf file – Found option without preceding group
  26. Returning records from the last 3 months only in MySQL
  27. MySQL optimizing INSERT speed being slowed down because of indices
  28. MySQL – why not index every field?
  29. Selecting distinct 2 columns combination in mysql
  30. Sequel Pro and MySQL connection failed

Leave a Comment