mysql - 获取用户与纬度的距离和经度
问题描述
首先,我想向您展示当前的数据库结构。有两个表:
用户
- ID
- 姓名
- location_id
地点
- ID
- 城市
- 纬度
- 液化天然气
现在我想实现一个 API 来获取附近的用户和他们的距离。喜欢附近的用户:
{{name:user1,distance:5km},(name:user2,distance:7km)}
这是我的位置模型:
public function scopeIsWithinMaxDistance($query, $rsine, $radius = 5)
{
return $query->selectRaw("*,{$rsine} AS distance")
->whereRaw("{$rsine} < ?"[$radius])->orderBy('distance');
}
public static function rsine($coordinates)
{
return '(6371 * acos(cos(radians(' . $coordinates['latitude'] . '))
* cos(radians(`lat`))
* cos(radians(`lng`)
- radians(' . $coordinates['longitude'] . '))
+ sin(radians(' . $coordinates['latitude'] . '))
* sin(radians(`lat`))))';
}
public static function convertKMToMiles($dist)
{
return $dist * 1.609344;
}
我的控制器:
$coordinates = ['latitude' => '28.392200', 'longitude' => '77.320801'];
$rsine = Location::rsine($coordinates);
$radius = Location::convertKMToMiles(5);
$users = User::with(['location' => function ($query) use ($rsine)
{
$query->selectRaw("*, {$rsine} AS distance");}])
->whereHas('location',function ($query) use ($radius, $rsine)
{$query->isWithinMaxDistance($rsine, $radius);})->get();
输出:语法错误
"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*,(6371 * acos(cos(radians(28.392200)) \n * cos(radians(`lat`)) \n * cos(rad' at line 1 (SQL: select * from `users` where exists (select *, *,(6371 * acos(cos(radians(28.392200)) \n * cos(radians(`lat`)) \n * cos(radians(`lng`) \n - radians(77.320801)) \n + sin(radians(28.392200)) \n * sin(radians(`lat`)))) AS distance from `locations` where `users`.`location_id` = `locations`.`id` and (6371 * acos(cos(radians(28.392200)) \n * cos(radians(`lat`)) \n * cos(radians(`lng`) \n - radians(77.320801)) \n + sin(radians(28.392200)) \n * sin(radians(`lat`)))) < 8.04672 order by `distance` asc) and `users`.`deleted_at` is null)",
"exception": "Illuminate\\Database\\QueryException"
解决方案
推荐阅读
- python-3.x - Python 3.9:学习asyncio遇到的问题
- javascript - 如何使用 javascript 从 mysql 数据库中获取数据并将其放入数组中?
- python - 如何使用外键 django 显示来自父模型的数据
- typescript - 从具有键和值的接口创建对象 - 打字稿
- go - 如果您知道要返回的类型,您还会使用输出参数吗?
- html - 为什么我的 CSS 在移动设备上与在狭窄的桌面浏览器上看起来不同,我该如何更改?
- git - 如何使用另一个更活跃的 github 项目的文件来处理我的 git 项目?
- python - Google Map API 拒绝请求
- python - 连接相同列数但行数不同的 55 个数据场,并用零填充缺失值
- azure-devops - 如何从 Azure Devops 导出变量组