Laravel中两点之间的Haversine距离计算

I'm working on a laravel appliciation in which I need to find all the products that are within a certain radius of the user's coordinates. Products have a one to many relationship with users, so users can have multiple products.

我正在开发一个laravel应用程序，我需要找到所有在用户坐标的某个半径范围内的产品。产品与用户有一对多的关系，因此用户可以拥有多种产品。

I've found that the haversine formula can calculate the distance between two points, but I can't seem to make it work.

我发现，哈斯金公式可以计算出两点之间的距离，但我似乎无法使它起作用。

I've got the following query in my controller:

我的控制器中有以下查询：

$latitude = 51.0258761;
$longitude = 4.4775362;
$radius = 20000;

$products = Product::with('user')
->selectRaw("*,
            ( 6371 * acos( cos( radians(" . $latitude . ") ) *
            cos( radians(user.latitude) ) *
            cos( radians(user.longitude) - radians(" . $longitude . ") ) + 
            sin( radians(" . $latitude . ") ) *
            sin( radians(user.latitude) ) ) ) 
            AS distance")
->having("distance", "<", $radius)
->orderBy("distance")
->get();

I've set the radius to 20000 for testing purposes and it appears all products have a distance of 5687,.. The problem seems to be that the latitude and longitude of the products are stored in the User table, but I'm not sure how I can access those in my query. I've tried user.latitude and 'user->latitude' but nothing seems to work.

我将半径设置为20000用于测试目的，看起来所有产品的距离都是5687，..问题似乎是产品的纬度和经度都存储在User表中，但我不确定我如何访问我的查询中的那些。我已经尝试过user.latitude和'user-> latitude'，但似乎没有任何效果。

Here are my models:

这是我的模特：

Product model

产品型号

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $fillable =
        [
            'soort',
            'hoeveelheid',
            'hoeveelheidSoort',
            'prijsPerStuk',
            'extra',
            'foto',
            'bio'


        ];

    public function User()
    {
        return $this->belongsTo('App\User');
    }

    public $timestamps = true;
}

User model

用户模型

namespace App;

use Illuminate\Auth\Authenticatable;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Auth\Passwords\CanResetPassword;
use Illuminate\Foundation\Auth\Access\Authorizable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;
use Illuminate\Contracts\Auth\Access\Authorizable as AuthorizableContract;
use Illuminate\Contracts\Auth\CanResetPassword as CanResetPasswordContract;

class User extends Model implements AuthenticatableContract,
                                    AuthorizableContract,
                                    CanResetPasswordContract
{
    use Authenticatable, Authorizable, CanResetPassword;

    protected $table = 'users';

    protected $fillable = 
        [
        'firstName', 
        'lastName', 
        'adres',
        'profilepic',
        'description', 
        'longitude',
        'latitude',
        'email', 
        'password'
    ];

    protected $hidden = ['password', 'remember_token'];

    public function product()
    {
        return $this->hasMany('App\Product');
    }
}

6 个解决方案

#1

This was my implementation of it. I've chosen to alias my query out ahead of time, this way I can take advantage of Pagination. Furthermore, you need to explicitly select the columns that you wish to retrieve from the query. add them at the ->select(). Such as users.latitude, users.longitude, products.name, or whatever they may be.

这是我的实施。我提前选择别名查询，这样我就可以利用分页。此外，您需要显式选择要从查询中检索的列。将它们添加到 - > select（）。例如users.latitude，users.longitude，products.name或者它们可能是什么。

I have created a scope which looks something like this:

我创建了一个看起来像这样的范围：

public function scopeIsWithinMaxDistance($query, $location, $radius = 25) {

     $haversine = "(6371 * acos(cos(radians($location->latitude)) 
                     * cos(radians(model.latitude)) 
                     * cos(radians(model.longitude) 
                     - radians($location->longitude)) 
                     + sin(radians($location->latitude)) 
                     * sin(radians(model.latitude))))";
     return $query
        ->select() //pick the columns you want here.
        ->selectRaw("{$haversine} AS distance")
        ->whereRaw("{$haversine} < ?", [$radius]);
}

You can apply this scope to any model with a latitude andlongitude.

您可以将此范围应用于具有纬度和经度的任何模型。

Replace the $location->latitude with your latitude that you wish to search against, and replace the $location->longitude with the longitude that you wish to search against.

将$ location->纬度替换为您要搜索的纬度，并将$ location-> longitude替换为您要搜索的经度。

Replace the model.latitude and model.longitude with the Models you wish to find around the $location based on the distance defined in the $radius.

使用您希望在$ location附近找到的模型替换model.latitude和model.longitude，具体取决于$ radius中定义的距离。

I know you have a functioning Haversine formula, but if you need to Paginate you can't use the code you've supplied.

我知道你有一个正常运作的Haversine公式，但如果你需要Paginate你不能使用你提供的代码。

Hopefully this helps.

希望这会有所帮助。

#2

If you are willing to use an external package instead, I suggest the infinitely useful PHPGeo library. I used it on a project that relied on these exact calculations, and it worked just fine. It saves you writing the calculations yourself from scratch and is tested to work.

如果您愿意使用外部包，我建议使用无限有用的PHPGeo库。我在一个依赖于这些精确计算的项目上使用它，它工作得很好。它可以帮助您自己从头开始编写计算并进行测试。

https://github.com/mjaschen/phpgeo

https://github.com/mjaschen/phpgeo

Here is the documentation for Harvesine: https://phpgeo.marcusjaschen.de/#_distance_between_two_coordinates_haversine_formula

以下是Harvesine的文档：https：//phpgeo.marcusjaschen.de/#_distance_between_two_coordinates_haversine_formula

#3

Using Haversine method, you can calculate distance between two points using this function. It works but I don't know how to implement this in Laravel. Thought of sharing this anyway.

使用Haversine方法，您可以使用此函数计算两点之间的距离。它有效，但我不知道如何在Laravel中实现它。无论如何分享这个想法。

$lat1 //latitude of first point
$lon1 //longitude of first point 
$lat2 //latitude of second point
$lon2 //longitude of second point 
$unit- unit- km or mile

function point2point_distance($lat1, $lon1, $lat2, $lon2, $unit='K') 
    { 
        $theta = $lon1 - $lon2; 
        $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
        $dist = acos($dist); 
        $dist = rad2deg($dist); 
        $miles = $dist * 60 * 1.1515;
        $unit = strtoupper($unit);

        if ($unit == "K") 
        {
            return ($miles * 1.609344); 
        } 
        else if ($unit == "N") 
        {
        return ($miles * 0.8684);
        } 
        else 
        {
        return $miles;
      }
    }   

#4

I think what you need is the query builder to build a join. With a join you have the fields of both tables available in your query. Currently you are using relationships with eager loading, this will preload the related users, but they cannot be used inside the SQL (Laravel will actually execute 2 queries).

我认为您需要的是构建连接的查询构建器。通过连接，您可以在查询中使用两个表的字段。目前您正在使用与热切加载的关系，这将预加载相关用户，但它们不能在SQL内部使用（Laravel实际上将执行2个查询）。

Anyway I wouldn't try to calculate the haversine formula in one step with SQL, this cannot be really performant, and the query could become difficult to maintain in my opinion. This is what i would do instead:

无论如何，我不会尝试使用SQL一步计算hasrsine公式，这可能不是真正的高效，并且在我看来，查询可能变得难以维护。这就是我要做的事情：

1. Calculate an envelope with minimum/maximum of latitude and longitude, it should be a bit bigger than your search radius.
2. 计算具有纬度和经度的最小值/最大值的包络，它应该比搜索半径大一点。
3. Make a fast query with a join of product and user, and just check whether the user location is inside this envelope.
4. 使用产品和用户的连接进行快速查询，只需检查用户位置是否在此信封内。
5. For each element of the resulting list calculate the exact haversine distance with PHP (not SQL), delete rows which are outside the radius, and sort the list accordingly.
6. 对于结果列表的每个元素，使用PHP（而不是SQL）计算精确的半径距离，删除半径之外的行，并相应地对列表进行排序。

#5

This is a code I am using:

这是我正在使用的代码：

            $ownerLongitude = $request['longitude'];
            $ownerLatitude = $request['latitude'];
            $careType = 1;
            $distance = 3;

            $raw = DB::raw(' ( 6371 * acos( cos( radians(' . $ownerLatitude . ') ) * 
 cos( radians( latitude ) ) * cos( radians( longitude ) - radians(' . $ownerLongitude . ') ) + 
    sin( radians(' . $ownerLatitude . ') ) *
         sin( radians( latitude ) ) ) )  AS distance');
            $cares = DB::table('users')->select('*', $raw)
        ->addSelect($raw)->where('type', $careType)
        ->orderBy('distance', 'ASC')
        ->having('distance', '<=', $distance)->get();

#6

Create this function in your Model

在模型中创建此功能

 public static function getNearBy($lat, $lng, $distance,
                                             $distanceIn = 'miles')
        {
            if ($distanceIn == 'km') {
                $results = self::select(['*', DB::raw('( 0.621371 * 3959 * acos( cos( radians('.$lat.') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('.$lng.') ) + sin( radians('.$lat.') ) * sin( radians(lat) ) ) ) AS distance')])->havingRaw('distance < '.$distance)->get();
            } else {
                $results = self::select(['*', DB::raw('( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('.$lng.') ) + sin( radians('.$lat.') ) * sin( radians(lat) ) ) ) AS distance')])->havingRaw('distance < '.$distance)->get();
            }
            return $results;
        }

And you can use orderby, groupBy as per your requirement.

您可以根据您的要求使用orderby，groupBy。