logo标签列表

按照纬度/经度对MySQL查询结果进行排序

phpmysql
16

我的数据库中的每个用户都有他们的纬度和经度存储在两个字段（lat，lon）中。

每个字段的格式为：

lon | -1.403976 
lat | 53.428691

如果用户在100英里范围内搜索其他用户，则为了计算适当的纬度/经度范围（$lat和$lon是当前用户的值），我执行以下操作：

$R = 3960;  // earth's mean radius
$rad = '100';
// first-cut bounding box (in degrees)
$maxLat = $lat + rad2deg($rad/$R);
$minLat = $lat - rad2deg($rad/$R);
// compensate for degrees longitude getting smaller with increasing latitude
$maxLon = $lon + rad2deg($rad/$R/cos(deg2rad($lat)));
$minLon = $lon - rad2deg($rad/$R/cos(deg2rad($lat)));

$maxLat=number_format((float)$maxLat, 6, '.', '');
$minLat=number_format((float)$minLat, 6, '.', '');
$maxLon=number_format((float)$maxLon, 6, '.', '');
$minLon=number_format((float)$minLon, 6, '.', '');

我可以执行以下查询：

$query = "SELECT * FROM table WHERE lon BETWEEN '$minLon' AND '$maxLon' AND lat BETWEEN '$minLat' AND '$maxLat'";

这个很好用，我用一个函数在输出阶段计算并显示用户之间的实际距离，但我想在查询阶段按距离递减或递增排序结果。

有没有办法做到这一点？

5个回答
44

还记得毕达哥拉斯吗？

$sql = "SELECT * FROM table 
    WHERE lon BETWEEN '$minLon' AND '$maxLon' 
      AND lat BETWEEN '$minLat' AND '$maxLat'
    ORDER BY (POW((lon-$lon),2) + POW((lat-$lat),2))";

技术上讲，那是距离的平方而不是实际距离，但由于你只是用它进行排序，所以这并不重要。

这使用平面距离公式，在小距离上应该很好。

然而：

如果您想更精确或使用更长的距离，请使用这个以弧度表示的大圆距离公式：

dist = acos[ sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lng1-lng2) ]

要以真实单位而不是弧度获取距离，请将其乘以地球半径。但出于排序目的，这并非必要。

MySQL计算引擎默认假定纬度和经度为弧度，因此如果它以度数存储（很可能如此），则需要将每个值乘以pi/180，约为0.01745：

$sf = 3.14159 / 180; // scaling factor
$sql = "SELECT * FROM table 
    WHERE lon BETWEEN '$minLon' AND '$maxLon' 
      AND lat BETWEEN '$minLat' AND '$maxLat'
    ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";

或者甚至：

$sf = 3.14159 / 180; // scaling factor
$er = 6350; // earth radius in miles, approximate
$mr = 100; // max radius
$sql = "SELECT * FROM table 
    WHERE $mr >= $er * ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))
    ORDER BY ACOS(SIN(lat*$sf)*SIN($lat*$sf) + COS(lat*$sf)*COS($lat*$sf)*COS((lon-$lon)*$sf))";
1
该查询没有考虑到经度是从格林威治子午线东西测量的这一事实，因此它将返回两个靠近180度子午线且彼此接近的地方之间非常长的距离。 - nitro2k01
@Dan - 我强烈建议你查看我下面的答案。使用 @Blazemonger 的 BETWEEN 并不总是足够准确，因为它查询的是一个正方形区域而不是从你的位置到半径的距离。 - lukeocodes
1
添加了一个更精确的公式，借鉴自维基百科。 - Blazemonger
您也是位绅士 - 非常感谢 - 看起来直接使用就非常有效。 - Dan
2
这很有趣且富有教育意义。 :-) - Blazemonger
显示剩余5条评论
8

仅使用 SELECT * FROM Table WHERE lat between $minlat and $maxlat 无法获得足够准确的结果。

查询距离的正确方式是使用弧度坐标。

<?php
  $sql = "SELECT * FROM Table WHERE acos(sin(1.3963) * sin(Lat) + cos(1.3963) * cos(Lat) * cos(Lon - (-0.6981))) * 6371 <= 1000";

以下是一个方便的参考链接 - http://janmatuschek.de/LatitudeLongitudeBoundingCoordinates

例如：

<?php
  $distance = 100;
  $current_lat = 1.3963;
  $current_lon = -0.6981;
  $earths_radius = 6371;

  $sql = "SELECT * FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance";

如果您想进行按顺序排列并显示距离的操作：

<?php
  $distance = 100;
  $current_lat = 1.3963;
  $current_lon = -0.6981;
  $earths_radius = 6371;

  $sql = "SELECT *, (acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius) as distance FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance ORDER BY acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius DESC";

为了@Blazemonger和避免疑点，我进行了编辑 :) 如果你想使用度数而不是弧度:

<?php
  $current_lat_deg = 80.00209691585;
  $current_lon_deg = -39.99818366895;
  $radians_to_degs = 57.2957795;

  $distance = 100;
  $current_lat = $current_lat_deg / $radians_to_degs;
  $current_lon = $current_lon_deg / $radians_to_degs;
  $earths_radius = 6371;

  $sql = "SELECT *, (acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius) as distance FROM Table T WHERE acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius <= $distance ORDER BY acos(sin($current_lat) * sin(T.Lat) + cos($current_lat) * cos(T.Lat) * cos(T.Lon - ($current_lon))) * $earths_radius DESC";

你可以轻松地将此内容封装成一个类，该类可从上述提供的信息中接受弧度或度数。
Luke，感谢你的回答，但对我来说有点困惑 - 你能否在正确的位置使用我的$max和$min变量进行编辑？我很不愿意这样问，但是，你知道的 :) - Dan
1
他举了一个例子 lat | 53.428691 -- 这个数字太大了，不可能是弧度。 - Blazemonger
@LukeOliff？我在谈论弧度，而不是角度。 - Blazemonger
@Blazemonger 它可以轻松修改以处理弧度。1弧度=57.2957795度。 - lukeocodes
1
嘿 @SaschaGrindau，地球半径和搜索半径以公里为单位。希望这可以帮到你。 - lukeocodes
显示剩余6条评论
6

以下是给出正确结果的公式（与上面的解决方案不同）。通过使用谷歌地图“测量距离”功能进行了确认（直接距离，不是交通距离）。

SELECT
    *,
    ( 3959 * acos( cos( radians(:latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( latitude ) ) ) ) AS `distance`
FROM `locations`
ORDER BY `distance` ASC

:latitude和:longitude是PDO函数的占位符。如果需要，您可以将它们替换为实际值。latitude和longitude是列名。

3959是地球半径（单位：英里）；输出的distance也将以英里为单位。要将其更改为公里，请将3959替换为6371。

这与我的回答和@Blazemonger的回答完全相同。公式的顺序不同，但除此之外，您已经使用了PDO（当OP没有使用时），并通过在MYSQL中使用弧度函数来强制使用弧度。更不用说，声称其他答案是误导性的，并随意投票否决，这不符合SO的精神。 - lukeocodes
1
谢谢您的评论。再次感谢您和其他人提供的查询，但是它们给出了误导性的值 - 幸好我进行了双重检查。因此，我翻阅了我的旧代码档案，并找到了当时对我有效的查询。所以这只是另一个（有效的）答案，是的，我投了反对票，因为其他查询对我没有用。很抱歉我看不出有什么问题。 - ᴍᴇʜᴏᴠ
很奇怪。使用我的数据，查询得出相同的答案。我想看看你的数据以比较结果！肯定有非常奇怪的事情正在发生。 - lukeocodes
同样的问题，上面的答案给我奇怪的结果。这个看起来对我比较准确。 - Sascha Grindau
1

以上答案都无法正确跨越格林威治子午线。哈弗辛公式：

  // 6371 is the Earth's radius in km
  6371 * 2 * ASIN(SQRT( 
     POWER(SIN((lat - abs(:latitude)) * pi()/180 / 2), 2) 
      + COS(lat * pi()/180 ) * COS(abs(:latitude) * pi()/180) 
      * POWER(SIN((lon - :longitude) *  pi()/180 / 2), 2) 
  )) as distance

我从这里获取的代码，在类似问题的答案中被引用，可以使用。
0

不会按照平面距离（不考虑地球曲率）给您排序结果，但对于小半径应该可以解决。

SELECT * from table where lon between '$minLon' and '$maxLon' and lat between '$minLat' and '$maxLat' order by (abs(lon-$lon)/2) + (abs(lat-$lat)/2);
橙色，谢谢你，喜欢Blaze的答案，这个可以处理一些不合适的地方 - Blaze的似乎有一个或两个不太合适。无论如何都点赞了。感谢您的回答。 - Dan
1
"/2" 是多余的，因为你只是在比较大小，而额外的括号也是多余的，因为加法已经比除法具有更高的优先级。 - nitro2k01
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接