如何在此查询中获取以KM为单位的距离 [英] How to get Distance in KM in this query

问题描述

$salons = Salon::select('salons.*')
    ->selectRaw('( 6371* acos( cos( radians(?) ) *
                   cos( radians( lat ) )
                   * cos( radians( lng ) - radians(?)
                   ) + sin( radians(?) ) *
                   sin( radians( lat ) ) )
                 ) AS distance', [$lat, $lng, $lat])
    ->havingRaw("distance < 25")
    ->where("category_Id" , "=" , $id)
    ->get();

这个查询给了我这个

"distance": 0.05205731665026305, I want distance like this 1.2KM

我尝试但未找到解决方法

i try but did not find get solution

推荐答案

更好的方法是使用PHP. SQL计算非常昂贵.在某些原始计数中，差异可以是30秒与0.04秒；)

Better way will be to use PHP. SQL calculations are expensive. In certain raw counts difference can be 30sec vs 0.04 sec ;)

public function scopeDistance($query, $from_latitude, $from_longitude, $distance)
    {
        $between_coords = \App\Services\PlaceServices::calcCoordinates($from_longitude, $from_latitude, $distance);

        return $query
            ->where(function ($q) use ($between_coords) {
                $q->whereBetween('places.longitude', [$between_coords['min']['lng'], $between_coords['max']['lng']]);
            })
            ->where(function ($q) use ($between_coords) {
                $q->whereBetween('places.latitude', [$between_coords['min']['lat'], $between_coords['max']['lat']]);
            });
    }

和calcCoodinates()

and calcCoodinates()

public static function calcCoordinates($longitude, $latitude, $radius = 20)
    {
        $lng_min = $longitude - $radius / abs(cos(deg2rad($latitude)) * 69);
        $lng_max = $longitude + $radius / abs(cos(deg2rad($latitude)) * 69);
        $lat_min = $latitude - ($radius / 69);
        $lat_max = $latitude + ($radius / 69);

        return [
            'min' => [
                'lat' => $lat_min,
                'lng' => $lng_min,
            ],
            'max' => [
                'lat' => $lat_max,
                'lng' => $lng_max,
            ],
        ];
    }

然后只需使用YourModel::distance($lat, $lon, $km)->get()

这篇关于如何在此查询中获取以KM为单位的距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！