通过接近特定纬度/经度的顺序表(使用MySQL + PHP) [英] Order table by proximity to specific latitude/longitude (using MySQL+PHP)

问题描述

MySQL(表)

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

我现在要做的是根据与(43.834527，-99.140625)的接近​​程度来排序列表.

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 />';
}

推荐答案

您可能有兴趣查看以下演示文稿:

You may be interested in checking out the following presentation:

• 使用MySQL进行地理/空间搜索 ¹ 通过亚历山大·鲁宾

• Geo/Spatial Search with MySQL¹ by Alexander Rubin

作者介绍了如何在MySQL中使用 Haversine公式来按接近度和限制进行排序您的搜索到定义的范围.他还介绍了如何使用纬度和经度列上的传统索引来避免对此类查询进行全表扫描.

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版本

这篇关于通过接近特定纬度/经度的顺序表(使用MySQL + PHP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！