CakePHP 从数据库查询最近纬度经度

问题描述

在 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

努力将两者结合起来.

更新

我已经添加了

class Sighting extends Entity {
    public $_virtual = ['distance'];
    //rest of class...
}

所以现在 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']);

结果:Column not found: 1054 Unknown column 'Sightings.distance' in 'where子句'

不确定是否可以使用 CakePHP v2 而不是 v3?

Not sure if it's possible CakePHP v2 as opposed to v3?

推荐答案

cake 3 中不再有 virtualFields，但您仍然可以为计算字段创建别名

there are no more virtualFields in cake 3 but you still can create an alias for your calculated field

按照@ndm 的建议，您最好绑定 $latitude 和 $longitude 以防止 SQL 注入

As suggested by @ndm you'd better bind $latitude and $longitude to prevent SQL injections

$distanceField = '(3959 * acos (cos ( radians(:latitude) )
    * cos( radians( Sightings.latitude ) )
    * cos( radians( Sightings.longitude )
    - radians(:longitude) )
    + sin ( radians(:latitude) )
    * sin( radians( Sightings.latitude ) )))';

使用where

$sightings = $this->Sightings->find()
    ->select([
        'distance' => $distanceField
    ])
    ->where(["$distanceField < " => $distance])
    ->bind(':latitude', $latitude, 'float')
    ->bind(':longitude', $longitude, 'float')
    ->contain(['Photos', 'Tags']);

使用拥有

$sightings = $this->Sightings->find()
    ->select([
        'distance' => $distanceField
    ])
    ->having(['distance < ' => $distance])
    ->bind(':latitude', $latitude, 'float')
    ->bind(':longitude', $longitude, 'float')
    ->contain(['Photos', 'Tags']);

这篇关于CakePHP 从数据库查询最近纬度经度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！