CakePHP query closest latitude longitude from database

Question

In a CakePHP (v3) application, how can I retrieve the closest results based on passed lat lng values?

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']);
}

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

Struggling to combine the two.

UPDATE

I've added

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

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).

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']);

Results in: Column not found: 1054 Unknown column 'Sightings.distance' in 'where clause'

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

That's my method so far, but I'm not doing any of the distance stuff, I'm unsure how to insert that sql into cakes way of doing it. public function findClosest($lat, $lng) { $sightings = $this->Sightings->find('all') ->where(['Sightings.created >' => new Time('-12 hour')]) ->limit(5); $this->set(compact('sightings')); $this->set('_serialize', ['sightings']); } — Kyle Goslan, Jul 19 '16 at 16:13
Tip: http://www.dereuromark.de/2012/06/12/geocoding-with-cakephp/ — mark, Jul 21 '16 at 19:11
No, thats also for 3.x as you can see if you read it :) It will link to https://github.com/dereuromark/cakephp-geo which is a cake 3 plugin. It's also mentioned in the well known [awesome cakephp list](https://github.com/FriendsOfCake/awesome-cakephp), which should always be your first stop before doing any other research. — mark, Jul 25 '16 at 14:16
This will cause the database engine to read every record and calculate the distance for every point in the entire database. You should compute a bounding box, eliminate all of the completely out of range points with > and < comparisons on latitude and longitude, then, for all the ones inside that box, do your distance calculation and `HAVING` filter them. — doug65536, Jul 27 '16 at 19:46

arilia · Answer 1 · 2016-07-25T07:47:15.127

10

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

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

using where

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

using having

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

edited Jul 25 '16 at 07:47

answered Jul 22 '16 at 09:03

arilia

9,373
2
20
44

Please make sure to either explicitly cast or quote `$latitude` and `$longitude` in this example, or to use bindings, as otherwise this is a possible SQL injection vulnerability! – ndm Jul 23 '16 at 13:04
@ndm you are right. I will improve my answerin the next days – arilia Jul 23 '16 at 13:06
This is really close to what I need, it's selecting the correct rows, but it only seems to contain the distance value, is there a way to hydrate the entities as "Normal"? – Kyle Goslan Jul 27 '16 at 13:39
You Can select all the fields you need of course. The manuale explains how to select all the fields of the model – arilia Jul 27 '16 at 13:41
1

try `->autoFields(true)` to select all the fields – arilia Jul 27 '16 at 14:13
There are still virtualFields in cake 3 – Chibueze Opata Jul 28 '16 at 07:08
No, there aren't (see this [**link**](http://book.cakephp.org/3.0/en/appendices/orm-migration.html#no-afterfind-event-or-virtual-fields)). They have been replaced by virtual properties (that you can't use in a query) or by custom finders. But you can't define a virtualField as a model's property as you used to do in cake2 – arilia Jul 28 '16 at 09:38

score 0 · Answer 2 · answered Jul 19 '16 at 16:45

0

You need to use ConnectionManager here.

For example:

use Cake\Datasource\ConnectionManager;  // Mention this first, preferably on top of the page.

$connection = ConnectionManager::get('default');

$results = $connection
  ->execute('SELECT * FROM articles WHERE id = :id', ['id' => 1])
  ->fetchAll('assoc');

You could try and set up your query in this fashion. It will definitely work.

Use this as a reference:

SELECT Statements with CakePHP 3

answered Jul 19 '16 at 16:45

Indrasis Datta

8,692
2
14
32

That does work, thanks. Is there any way to include the ['contain' => ['Photos', 'Tags'] objects along with it or would I have to write the join? This is basically the exact query...I just need to filter by the lat/lng public function index($lat, $lng) { $sightings = $this->Sightings->find('all', ['contain' => ['Photos', 'Tags']]) ->where(['Sightings.created >' => new Time('-12 hour')]) ->order(['Sightings.created' => 'ASC']) ->limit(5); $this->set(compact('sightings')); $this->set('_serialize', ['sightings']); } – Kyle Goslan Jul 19 '16 at 17:19
I think using join would be a better option. When you're using SELECT this way, it won't recognize the model associations, and therefore the usage of contain is skeptical. – Indrasis Datta Jul 19 '16 at 17:30
It's kind of really close. I'm rendering the output as json and the problem is that the related tables are no longer displaying as related json objects. So for example the `id` in the join table is overwriting the `id` from the main primary table queried. This looks really close, https://gist.github.com/SwabTheDeck/0056d6785377d86deaa8 possibly looks like it's for cake 2.0 and I'm not sure how to implement it, and suggestion? – Kyle Goslan Jul 19 '16 at 21:50

score 0 · Answer 3 · answered Jul 28 '16 at 07:22

There are several approaches to achieve your result.

1. Raw Query

Instead of struggling with the ORM, you could perform a raw query?

$conn = ConnectionManager::get('my_connection');

And then you can run a custom query like:

$results = $conn->execute('MY RAW SQL HERE');

2. Raw Query w/Eager loading

If you want to run a query and eager load the relations, you will need to use the contains method like:

$query = $articles->find('all', ['contain' => ['Authors', 'Comments']]);

3. Using Virtual Fields

If you want to use virtual fields like in your example above, you need to add it in your entity model class like:

protected function _getDistance()
{
    //access model members using $this->_properties e.g.
    //return $this->_properties['latitude'] . '  ' .
    //    $this->_properties['longitude'];
}

This should ensure your distance is no longer null in your json.

And you could use with the distance method from CakephpGeo library to construct a query

public function distance(array $pointX, array $pointY) {
    $res = $this->calculateDistance($pointX, $pointY);
    return ceil($res);
}

public static function calculateDistance($pointX, $pointY) {
    $res = 69.09 * rad2deg(acos(sin(deg2rad($pointX['lat'])) * sin(deg2rad($pointY['lat']))
        + cos(deg2rad($pointX['lat'])) * cos(deg2rad($pointY['lat'])) * cos(deg2rad($pointX['lng']
        - $pointY['lng']))));
    return $res;
}

4. Raw PHP

Lastly, and not recommended, you could retrieve all the info you need and use PHP to narrow down your results. Painful, slow and not recommended.

Soubhagya Kumar Barik · Answer 4 · 2018-08-23T06:34:02.980

finally i got the solution for this. below is the working example

$distance = 25;
$start_latitude = 12.920479;
$start_longitude = 77.670547;

$contents = $this->Sightings->find()->select(['latitude', 'longitude', 'distance' => 'SQRT(
    POW(69.1 * (latitude - '.$start_latitude.'), 2) +
    POW(69.1 * ('.$start_longitude.' - longitude) * COS(latitude / 57.3), 2))'])->having(["distance < " => $distance]);

output

   {"lat": xx.920479,
    "lng": xx.670547,
    "distance": "0"
   }

Kyle Goslan · Answer 5 · 2016-09-03T13:23:52.023

-1

People might want to look at the CakePHP GeoDistance Plugin:

CakePHP-GeoDistance is a CakePHP 3 behavior for querying geocoded data based on cartographic distance using the spherical cosine law. It is great for 'find my nearest X' or 'find Y near me' type queries.

https://github.com/chris48s/cakephp-geodistance

Did the job.

edited Sep 03 '16 at 13:23

answered Sep 03 '16 at 13:05

Kyle Goslan

10,748
7
26
41

CakePHP query closest latitude longitude from database

5 Answers5

1. Raw Query

2. Raw Query w/Eager loading

3. Using Virtual Fields

4. Raw PHP

Linked