laravel Laravel中两点之间的Haversine距离计算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37876166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Haversine distance calculation between two points in Laravel
提问by Robke22
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.latitudeand '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');
}
}
回答by Ohgodwhy
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.
这是我对它的实现。我选择提前为我的查询设置别名，这样我就可以利用Pagination. 此外，您需要明确选择要从查询中检索的列。将它们添加到->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 latitudeandlongitude.
您可以将此范围应用于任何带有latitude和 的模型longitude。
Replace the $location->latitudewith your latitudethat you wish to search against, and replace the $location->longitudewith the longitude that you wish to search against.
更换$location->latitude你的latitude，你想对搜索，并替换$location->longitude与您希望搜索对经度。
Replace the model.latitudeand model.longitudewith the Models you wish to find around the $locationbased on the distance defined in the $radius.
根据 中定义的距离，将model.latitude和替换为model.longitude您希望在 周围找到的模型。$location$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 公式，但是如果您需要分页，则无法使用您提供的代码。
Hopefully this helps.
希望这会有所帮助。
回答by Sergio E. Diaz
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
回答by version 2
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;
}
}
回答by martinstoeckli
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).
我认为您需要的是构建 join的查询构建器。通过连接，您可以在查询中使用两个表的字段。目前您正在使用具有预先加载的关系，这将预加载相关用户，但它们不能在 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 一步计算半正弦公式，这不能真正提高性能，而且在我看来，查询可能变得难以维护。这就是我会做的事情：
- Calculate an envelope with minimum/maximum of latitude and longitude, it should be a bit bigger than your search radius.
- Make a fast query with a join of product and user, and just check whether the user location is inside this envelope.
- 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.
- 计算具有最小/最大纬度和经度的包络，它应该比您的搜索半径大一点。
- 使用产品和用户的连接进行快速查询，只需检查用户位置是否在此信封内。
- 对于结果列表的每个元素，使用 PHP（不是 SQL）计算精确的半正弦距离，删除半径之外的行，并相应地对列表进行排序。
回答by K-Alex
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();
回答by PHP Worm...
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, groupByas per your requirement.
你也可以使用orderby，groupBy按您的要求。
回答by jibril90
I got a solution in Laravel.
我在 Laravel 中找到了解决方案。
public function near($myLon, $myLat, $areaLon, $areaLat)
{
$this->applyCriteria();
$this->applyScope();
$results = $this->model->select(DB::raw("SQRT(
POW(69.1 * (latitude - " . $myLat . "), 2) +
POW(69.1 * (" . $myLon . " - longitude) * COS(latitude / 57.3), 2)) AS distance, SQRT(
POW(69.1 * (latitude - " . $areaLat . "), 2) +
POW(69.1 * (" . $areaLon . " - longitude) * COS(latitude / 57.3), 2)) AS area"), "YOUR_TABLE.*")->get();
$this->resetModel();
$this->resetScope();
return $this->parserResult($results);
}
The answer is in Miles, you will have to replace YOUR_TABLE with the name of your database table. Thank you hope it helps
答案是在 Miles 中，您必须将 YOUR_TABLE 替换为您的数据库表的名称。谢谢希望有帮助
