How to Efficiently Query for Addresses Within a Specific Range in MySQL using the Haversine Formula?

MySQL Query for Selecting Coordinates within a Specified Range

In your database, you have a vast number of addresses, each with its own latitude and longitude. You seek to display only those addresses within a 5-mile radius of a specified user location on a map using Google Maps API.

To avoid unnecessarily retrieving all records and performing distance calculations in Java, which would be inefficient, consider utilizing the Haversine formula within your MySQL query. This formula will calculate the distance between two points on the Earth's surface. Here's the modified query:

<code class="sql">SELECT *, (3959 * acos(cos(radians(?)) * cos(radians(lat)) * cos(radians(lng) - radians(?)) + sin(radians(?)) * sin(radians(lat)))) AS distance
FROM your_table
HAVING distance < 5</code>

In this query, replace the question marks (?) with the latitude and longitude values for the user's location ($lat and $lng from your Java code). The distance column will store the distance between each address and the user's location.

By using this query, you will only retrieve the addresses that are within the specified range, optimizing your query performance.

The above is the detailed content of How to Efficiently Query for Addresses Within a Specific Range in MySQL using the Haversine Formula?. For more information, please follow other related articles on the PHP Chinese website!