PHP排序最近的坐标

问题描述

我在PHP网络服务中有一个包含经度和纬度的MySQL表.我只想向用户发送5个最接近的坐标.我写了一种方法来计算从坐标到用户在POST请求中发送的坐标之间的距离，但是我不确定如何对它进行排序，只发回一些.

I have a MySQL table in a PHP webservice containing longitude and latitude.I want to send the user only the, let's say, 5 closest coordinates.I wrote the method which calculates a distance from coordinates to the ones the user sent in the POST request, but I'm not sure on how to sort it and only send back a few.

这是距离方法:

function  distance($longToCompare,$latToCompare) {
    $dlong = $request_long - $longToCompare;
    $dlat = $request_lat - $latToCompare;
    $a = pow(sin($dlat/2)) + cos($latToCompare)*cos($request_lat)*pow(sin($dlong/2));
    $c = 2*atan2(sqrt($a),sqrt(1-$a));
    return 6373*$c;
}

并且用户当前获得了整个数据库(目前，虽然开发的数据库很小，但将来可能会很大)

and the user currently gets the whole DB (for now, while developing it's small, but in the future it could be rather big)

$q = mysql_query("SELECT * FROM Coordinates");
$coordinates = array ();
while ($e = mysql_fetch_assoc($q)) {
    $coordinates[] = $e;
}
print (json_encode($coordinates));

有人能指出我正确的方向吗?我对PHP相当陌生，我知道我可以使用uasort创建自定义排序，但是我不确定如何使用该距离函数来使用它.

Can anyone point me to the right direction? I'm rather new to PHP, I know I can create a custom sorting using uasort, but i'm not quite sure on how to use it using this distance function.

使用@Norse的解决方案，当前查询为:

Using @Norse 's solution, the current query is:

$request_long = $_POST['longitude'];
$request_lat = $_POST['latitude'];
    $km = 0.5;
        $query = "SELECT *,
    ( 6373 * acos( cos( radians('$request_lat') ) *
    cos( radians( latitude ) ) *
    cos( radians( longitude ) -
    radians('$request_long') ) +
    sin( radians('$request_lat') ) *
    sin( radians( latitude ) ) ) )
    AS distance FROM Coordinates HAVING distance < '$km' ORDER BY distance ASC LIMIT 0, 5";
        $coordinates = array ();
        while ($e = mysql_fetch_assoc($query)) {
            $coordinates[] = $e;
        }
        print (json_encode($coordinates));

推荐答案

使用Google的算法:

Using Google's algorithm:

$lon = //your longitude
$lat = //your latitude
$miles = //your search radius

$query = "SELECT *,
( 3959 * acos( cos( radians('$lat') ) *
cos( radians( latitude ) ) *
cos( radians( longitude ) -
radians('$lon') ) +
sin( radians('$lat') ) *
sin( radians( latitude ) ) ) )
AS distance FROM yourtable HAVING distance < '$miles' ORDER BY distance ASC LIMIT 0, 5"

此查询中的

latitude和longitude将作为您的经/纬列名称.

latitude and longitude in this query are going to be your lat/lon column names.

这篇关于PHP排序最近的坐标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！