Order table by proximity to specific latitude/longitude (using MySQL+PHP)

Question

MySQL (table)

id | url |    lat    |    lng
----------------------------------
1  |  x  | 44.339565 | -101.337891
----------------------------------
2  |  y  | 44.150681 | -101.074219
----------------------------------
3  |  z  | 43.897892 | -100.634766

what I want to do now is order the list according to their proximity to (43.834527,-99.140625).

$a = mysql_query("SELECT * FROM table ORDER BY proximity DESC");
while($b = mysql_fetch_assoc($a))
{
echo $b['url'].'<br />';
}

Skateboard

Answer 1

You may be interested in checking out the following presentation:

• Geo/Spatial Search with MySQL¹ by Alexander Rubin

The author describes how you can use the Haversine Formula in MySQL to order by proximity and limit your searches to a defined range. He also describes how to avoid a full table scan for such queries, using traditional indexes on the latitude and longitude columns.

---

¹ PDF Version

Ahders