How to Efficiently Retrieve Rows within a Specified Mile Radius using MySQL Queries?

MySQL Query for Locating Rows within a Specified Mile Radius

When dealing with database queries involving longitude and latitude coordinates, it becomes essential to efficiently retrieve rows falling within a specific geographical radius. To achieve precise results within a 25-mile radius, the following query leverages a mathematical formula:

<code class="sql">SELECT *,(((acos(sin(($lat*pi()/180)) * sin((`latitude`*pi()/180))+cos(($lat*pi()/180))
* cos((`latitude`*pi()/180)) * cos((($lon - `longitude`)*pi()/180))))*180/pi())*60*1.1515)
AS `distance` FROM `geo_locations` HAVING `distance` <= 25 ORDER BY `distance` ASC</code>

This query employs a trigonometric formula to calculate the distance between each row's coordinates and a specified center latitude ($lat) and longitude ($lon). However, it may occasionally produce inaccurate results when extended to a larger radius.

Improved Query

For more precise results, consider using the following query:

<code class="sql">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</code>

In this query, :lat and :long represent the center latitude and longitude passed by the user, while location is the table containing the target rows. :distance is the desired radius in miles. By replacing 3959 (the distance from Earth's center to its surface in miles) with 6371 (converted to kilometers), kilometers can be used instead.

The above is the detailed content of How to Efficiently Retrieve Rows within a Specified Mile Radius using MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!