## How to Accurately Query for MySQL Rows Within a Specified Mile Radius?

mySQL Longitude and Latitude Query for Rows Within X Mile Radius

Within a large database containing geospatial data, it is often necessary to retrieve rows that fall within a specific radius of a given location. This is commonly achieved using spherical geometry to calculate the distance between two points on a sphere.

One such query for finding rows within a 25-mile radius of a center point returns some inaccurate results, with distances ranging from 86 to 800 miles off the mark. Here's the original query:

<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>

To address this issue, let's modify the query based on a solution that has been successfully implemented in a store locator application:

<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 revised query:

• :lat and :long represent the latitude and longitude of the center point passed as parameters.
• lat and long are the latitude and longitude values stored in the database.
• :distance is a user-defined distance parameter passed to the query.
• 6371 is the earth's radius in miles.

By using this updated query, you can effectively retrieve rows that fall within a specified mile radius around a given central location.

The above is the detailed content of ## How to Accurately Query for MySQL Rows Within a Specified Mile Radius?. For more information, please follow other related articles on the PHP Chinese website!