laravel 如何使用 Eloquent 查询两个坐标之间的距离
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42821816/
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
How to query distances between two coordinates with Eloquent
提问by KeizerBridge
I know this has been ask many times. But I didn't figure out to make it according to my needs.
我知道这已经被问过很多次了。但是我没有想出根据我的需要来制作它。
I need to query the nearest users from another user.
Basically, I have a userstable this table has a one to onerelation with the users_locationstable which has a latitude and a longitude field.
我需要从另一个用户查询最近的用户。基本上，我有一个users表，该表one to one与users_locations具有纬度和经度字段的表有关系。
So I've seen this https://laravel.io/forum/04-23-2014-convert-this-geolocation-query-to-query-builder-for?page=1and this may be the best solution.
所以我看到了这个 https://laravel.io/forum/04-23-2014-convert-this-geolocation-query-to-query-builder-for?page=1这可能是最好的解决方案。
But my basic query is :
但我的基本查询是：
\App\Model\User::whereNotIn('id', $ids)
->where('status', 1)
->whereHas('user_location', function($q) use ($lat, $lng, $radius) {
/** This is where I'm stuck to write the query **/
})->select('id', 'firstname')
->get();
I don't figure out how to implement the solution in this case.
在这种情况下，我不知道如何实施解决方案。
Thank you in advance for your help
预先感谢您的帮助
EDITTo be more clear: I need to get the users that are in a 5 kilometers radius.
编辑更清楚：我需要获得半径 5 公里内的用户。
回答by KeizerBridge
I found the solution, thanks to EddyTheDove and Ohgodwhy.
感谢 EddyTheDove 和 Ohgodwhy，我找到了解决方案。
So this is it:
所以就是这样：
\App\Model\User::whereNotIn('id', $ids)
->where('status', 1)
->whereHas('user_location', function($q) use ($radius, $coordinates) {
$q->isWithinMaxDistance($coordinates, $radius);
})->select('id', 'firstname')
->get();
And in my UserLocationModel I have this local scope
在我的UserLocation模型中，我有这个本地范围
public function scopeIsWithinMaxDistance($query, $coordinates, $radius = 5) {
$haversine = "(6371 * acos(cos(radians(" . $coordinates['latitude'] . "))
* cos(radians(`latitude`))
* cos(radians(`longitude`)
- radians(" . $coordinates['longitude'] . "))
+ sin(radians(" . $coordinates['latitude'] . "))
* sin(radians(`latitude`))))";
return $query->select('id', 'users_id', 'cities_id')
->selectRaw("{$haversine} AS distance")
->whereRaw("{$haversine} < ?", [$radius]);
}
The original answer by Ohgodwhy is here: Haversine distance calculation between two points in Laravel
Ohgodwhy的原始答案在这里： Laravel中两点之间的Haversine距离计算
EDIT
编辑
Another way to perform it with stored functions in MySQL:
在 MySQL 中使用存储函数执行它的另一种方法：
DELIMITER $$
DROP FUNCTION IF EXISTS haversine$$
CREATE FUNCTION haversine(
lat1 FLOAT, lon1 FLOAT,
lat2 FLOAT, lon2 FLOAT
) RETURNS FLOAT
NO SQL DETERMINISTIC
COMMENT 'Returns the distance in degrees on the Earth
between two known points of latitude and longitude'
BEGIN
RETURN DEGREES(ACOS(
COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) *
COS(RADIANS(lon2) - RADIANS(lon1)) +
SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
));
END$$
DELIMITER ;
I multiply by 111.045 to convert the result in km. (I'm not sure that this value is right, I found many others values not far from this one so if someone have precision about it, it could be nice)
我乘以 111.045 以将结果转换为公里。（我不确定这个值是否正确，我发现许多其他值离这个值不远，所以如果有人对它有精确性，那可能会很好）
Original article: https://www.plumislandmedia.net/mysql/stored-function-haversine-distance-computation/
原文：https: //www.plumislandmedia.net/mysql/stored-function-haversine-distance-computation/
Then using eloquent:
然后使用 eloquent：
\App\Model\User::whereNotIn('id', $ids)
->where('status', 1)
->whereHas('user_location', function($q) use ($radius, $coordinates) {
$q->whereRaw("111.045*haversine(latitude, longitude, '{$coordinates['latitude']}', '{$coordinates['longitude']}') <= " . $radius]);
})->select('id', 'firstname')
->get();
