How to Efficiently Retrieve Addresses within a Specified Range using MySQL?

Efficiently Retrieving Addresses within a Specified Range using MySQL

To effectively retrieve addresses within a 5-mile radius from a given location, it is inefficient to retrieve all 100,000 addresses and calculate the distance in Java. Instead, MySQL provides a suitable solution through the Haversine formula.

The Haversine formula calculates the great-circle distance between two points on a sphere, in this case, the user's location and each address. Using this formula, you can construct a MySQL query as follows:

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

In the above query, $lat and $lng represent the user's coordinates, while lat and lng represent the address coordinates. By applying the Haversine formula, the query calculates the distance between each address and the user's location and selects only those within the 5-mile range.

By utilizing this approach, you can efficiently retrieve the addresses that need to be displayed on the map while avoiding the computationally expensive task of calculating distances for all 100,000 addresses.

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