CakePHP查询距数据库最近的经度经度 [英] CakePHP query closest latitude longitude from database
问题描述
在CakePHP(v3)应用程序中,如何根据传递的lat lng值检索最接近的结果?
In a CakePHP (v3) application, how can I retrieve the closest results based on passed lat lng values?
我想让他们回到原生CakePHP实体,所以像这样:
I'd like to have them back as native CakePHP entities, so something like this:
public function closest($lat, $lng) {
$sightings = //records within given lat lng
$this->set(compact('sightings'));
$this->set('_serialize', ['sightings']);
}
我知道这个SQL的工作原理:
I know this SQL works:
SELECT *,
( 3959 * acos( cos( radians(50.7) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-1.8) ) + sin( radians(50.7) ) * sin( radians( latitude ) ) ) ) AS distance
FROM sightings
HAVING distance < 10
ORDER BY distance
LIMIT 0 , 20
。
UPDATE
我已添加
class Sighting extends Entity {
public $_virtual = ['distance'];
//rest of class...
}
code> distance 显示在我的json输出中(值为null,如现在预期的,但我觉得这是一个步骤)。
So now the distance
is showing up in my json output (with the value of null as would be expected right now, but I feel it's a step at lease).
我看过这里: http:// www .mrthun.com / 2014/11/26 / search-distance-cakephp / 这似乎是我想实现的,所以假设这样:
I've taken a look here: http://www.mrthun.com/2014/11/26/search-distance-cakephp/ which seems to be what I'm trying to achieve so assumed something like this:
$latitude = 51.145;
$longitude = -1.45;
$distance = 100;
$this->Sightings->virtualFields
= array('Sightings.distance'
=> '(3959 * acos (cos ( radians('.$latitude.') )
* cos( radians( Sightings.latitude ) )
* cos( radians( Sightings.longitude )
- radians('.$longitude.') )
+ sin ( radians('.$latitude.') )
* sin( radians( Sightings.latitude ) )))');
$sightings = $this->Sightings->find('all', [
'conditions' => ['Sightings.distance <' => $distance]
]);
$this->set(compact('sightings'));
$this->set('_serialize', ['sightings']);
结果:未找到列:1054未知列'Sightings.distance'不能确定CakePHP v2是否可能与v3相反?
Results in: Column not found: 1054 Unknown column 'Sightings.distance' in 'where clause'
推荐答案
蛋糕3中没有更多的虚拟字段,但您仍然可以为计算字段创建别名
通过@ndm你最好绑定 $ latitude
和 $ longitude
以防止SQL注入
there are no more virtualFields in cake 3 but you still can create an alias for your calculated field
As suggested by @ndm you'd better bind $latitude
and $longitude
to prevent SQL injections
使用位置
using where
使用
$sightings = $this->Sightings->find()
->select([
'distance' => $distanceField
])
->having(['distance < ' => $distance])
->bind(':latitude', $latitude, 'float')
->bind(':longitude', $longitude, 'float')
->contain(['Photos', 'Tags']);
这篇关于CakePHP查询距数据库最近的经度经度的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!