mysql order 排序按照经纬度距离来排序

"ACOS(SIN(( ".$latitude." * 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180 ) +COS(( ".$latitude."* 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) *COS(( ".$longitude."* 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380 asc"

latitude

longitude

是数据库字段 改成你对应的即可

tp5完整代码

模型

    /**
     * [拼团列表]
     * @Pasa吴
     * @DateTime 2018-07-25T17:05:07+0800
     * @param    string                   $longitude            [经度排序]
     * @param    string                   $latitude             [纬度排序]
     * @param    integer                  $psize                [每页显示数量]
     * @param    integer                  $page                 [页数]
     */
    static function lists(,$longitude='',$latitude='',$psize=10,$page=1){
        $map = [];
        $order = '';
        $field = "";
        if(!empty($longitude) and !empty($latitude)){
            $map['longitude']=array('neq','');
            $map['latitude']=array('neq','');
            $order = "ACOS(SIN(( ".$latitude." * 3.1415) / 180 ) *SIN((latitude * 3.1415) / 180 ) +COS(( ".$latitude."* 3.1415) / 180 ) * COS((latitude * 3.1415) / 180 ) *COS(( ".$longitude."* 3.1415) / 180 - (longitude * 3.1415) / 180 ) ) * 6380 asc";
        }
        $lists = self::where($map)->field($field)->order($order)->paginate($psize, false, ['page' => $page]);
        foreach($lists as $k=>$v){
                $distance = getDistance($latitude,$longitude,$v['latitude'],$v['longitude']);
                $lists[$k]['distance'] = sprintf('%.2f',$distance) . ' km';
        }
        return $lists;
    }

公共函数

common.php

function rad($d)
{
    return $d * 3.1415926535898 / 180.0;
}

function getDistanceNone ( $lat1 , $lng1 , $lat2 , $lng2 )
{
    $EARTH_RADIUS = 6378.137;
    $radLat1      = rad( $lat1 );
    //echo $radLat1;
    $radLat2 = rad( $lat2 );
    $a       = $radLat1 - $radLat2;
    $b       = rad( $lng1 ) - rad( $lng2 );
    $s       = 2 * asin( sqrt( pow( sin( $a / 2 ) , 2 ) + cos( $radLat1 ) * cos( $radLat2 ) * pow( sin( $b / 2 ) , 2 ) ) );
    $s       = $s * $EARTH_RADIUS;
    $s       = round( $s * 10000 );

    return $s;
}

function getDistance ( $lat1 , $lng1 , $lat2 , $lng2 )
{
    $s = getDistanceNone( $lat1 , $lng1 , $lat2 , $lng2 );
    $s = $s / 10000;
    return $s;
}