How to Efficiently Find Closest Locations in a SQL Database?

Efficiently Finding the Closest Locations Near a Given Location

To efficiently identify the closest locations to a specified coordinate, consider utilizing the Haversine formula within your SQL query. Refer to Google's tutorial on retrieving nearby locations using MySQL.

Here's an example SQL statement:

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

By using the Haversine formula within the database, you eliminate the need to load all businesses into your PHP script. This significantly enhances performance, especially when working with large datasets like your 5k businesses.

The above is the detailed content of How to Efficiently Find Closest Locations in a SQL Database?. For more information, please follow other related articles on the PHP Chinese website!