mySQL longitude and latitude query for other rows within x mile radius

by

Here is the query I use on the store locator I work with:

SELECT
    `id`,
    (
        6371 *
        acos(
            cos( radians( :lat ) ) *
            cos( radians( `lat` ) ) *
            cos(
                radians( `long` ) - radians( :long )
            ) +
            sin(radians(:lat)) *
            sin(radians(`lat`))
        )
    ) `distance`
FROM
    `location`
HAVING
    `distance` < :distance
ORDER BY
    `distance`
LIMIT
    25

:lat and :long are the points the passed by the user where lat and long are the points stored in the database.

The :distance is measured in miles, in the working version of the code the :distance is actually pulled from a drop down ranging from 10-50 miles

Changing the code to work with kilometers can be accomplished by changing 3959 (the distance from the center of the earth to its surface in miles) to 6371 (3959 miles converted to kilometers) thanks to joshhendo for that solution.

More Related Contents:

  1. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  2. Find distance between two points using latitude and longitude in mysql
  3. Whats the fastest way to lookup big tables for points within radius MySQL (latitude longitude)
  4. Calculate distance given 2 points, latitude and longitude [duplicate]
  5. SQL split values to multiple rows
  6. Find duplicate records in MySQL
  7. How do I escape reserved words used as column names? MySQL/Create Table
  8. MySQL order by before group by
  9. MySQL Cannot Add Foreign Key Constraint
  10. Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
  11. MySQL: selecting rows where a column is null
  12. Difference between float and decimal data type
  13. Convert timestamp to date in MySQL query
  14. Update a column value, replacing part of a string
  15. mysql dynamic query in stored procedure
  16. mysql: get record count between two date-time
  17. MySQL Insert Into from one Database in another
  18. Sort by order of values in a select statement “in” clause in mysql
  19. How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?
  20. Removing MySQL 5.7 Completely [closed]
  21. ORDER BY date and time BEFORE GROUP BY name in mysql
  22. How to optimize COUNT(*) performance on InnoDB by using index
  23. script to convert mysql dump sql file into format that can be imported into sqlite3 db
  24. MySQL INT meaning
  25. MySQL Stored Procedures not working with SELECT (basic question)
  26. MySQL LAST_INSERT_ID() used with multiple records INSERT statement
  27. Too many auto increments with ON DUPLICATE KEY UPDATE
  28. MySQL errorno 121
  29. Are there any functions in MySQL like dense_rank() and row_number() like Oracle?
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment