MySQL select coordinates within range

Question

I've in my database 100 000 addresses (that is records).

Each one of them has its own coordinates (latitude and longitude).

Now, given the geo location of the user (latitude and longitude), I want to show on a map only the addresses inside the 5 miles range (using Google maps v3 APIs).

This means that usually only 5 or 6 addresses have to be shown out of the 100 000 addresses.

One solution could be retrieving all the records and apply a formula in Java to calculate the distance of each address and show it only if it's inside the range.

That would be a waste of processing power, because I would need to retrieve all the records, when I only need to show 5 or 6 of them on the map.

How can I solve this problem on the database side (MySQL), in order to return only the addresses in the 5 miles range?

Answer 1

You can use what is called the Haversine formula.

$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";

Where $lat and $lng are the coordinates of your point, and lat/lng are your table columns. The above will list the locations within a 5 nm range. Replace 3959 by 6371 to change to kilometers.

This link could be useful: https://developers.google.com/maps/articles/phpsqlsearch_v3

Edit: I didn't see you mentioned Java. This example is in PHP but the query is still what you need.