How Can I Use MySQL to Find Points Within a Given Radius of Latitude and Longitude?

Using MySQL Spatial Extensions to Retrieve Points Within a Circle

To perform spatial queries based on latitude and longitude, extending MySQL with geospatial functions is necessary. However, it's crucial to note that MySQL lacks built-in spatial functions specifically designed for distance computations involving these coordinates.

Instead, proximity circles on Earth's surface require the use of the Great Circle Distance formula, which can be implemented using the haversine or other appropriate algorithms. MySQL 5.6 provides an undocumented st_distance(p1, p2) function that calculates Cartesian distances within planar shapes, but this is unsuitable for latitude and longitude-based calculations due to the distortions introduced by Cartesian distances.

For a practical application, consider a query to find all flags within ten statute miles of a given latitude/longitude point:

SELECT id, coordinates, name, r,
        units * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(latpoint))
                  * COS(RADIANS(latitude))
                  * COS(RADIANS(longpoint) - RADIANS(longitude))
                  + SIN(RADIANS(latpoint))
                  * SIN(RADIANS(latitude))))) AS distance
   FROM flags
   JOIN (
        SELECT 42.81  AS latpoint,  -70.81 AS longpoint, 
               10.0 AS r, 69.0 AS units
        ) AS p ON (1=1)
  WHERE MbrContains(GeomFromText (
        CONCAT('LINESTRING(',
              latpoint-(r/units),' ',
              longpoint-(r /(units* COS(RADIANS(latpoint)))),
              ',', 
              latpoint+(r/units) ,' ',
              longpoint+(r /(units * COS(RADIANS(latpoint)))),
              ')')),  coordinates)

In this query:

• latpoint and longpoint represent the latitude and longitude of the user's location.
• r is the search radius in miles.
• units represent the distance units per degree of latitude on Earth's surface (e.g., 69 miles for miles or 111.045 km for kilometers).
• MbrContains excludes points that are definitely outside the search area based on a bounding rectangle.
• The Great Circle Distance formula calculates the distance between points.

To limit the results to points within the circle and sort them by proximity, enclose the query within another query:

SELECT id, coordinates, name
   FROM (
         /* the above query, pasted in here */
        ) AS d
  WHERE d.distance <= d.r
  ORDER BY d.distance ASC

The above is the detailed content of How Can I Use MySQL to Find Points Within a Given Radius of Latitude and Longitude?. For more information, please follow other related articles on the PHP Chinese website!