CODEWITHSUNDEEP
cakephpcakephp-3.0latitude-longitudequery-builder
Nick
Nick

Reputation: 59

How to specify a condition to retrieve data by radius (lat, lon)?

In cakephp 2, I was able to use a virtualField for this, but seems impossible in 3. I have been struggling with this for two days without luck from the internet or the cakephp manual. I get no errors, but I do get a blank return.

My code in my controller looks like this:

if (isset($this->request->data['location']) && (isset($this->request->data['radius']))){
        $radius = $this->request->data['radius'];
        $location = $this->request->data['location'];
        $address = $location; // Google HQ
        $HttpSocket = new Client();
        $geocode = $HttpSocket->get('http://maps.google.com/maps/api/geocode/json?address='.$address.'&sensor=false');
        $geocode = $geocode->json;
        if ($geocode['status'] == "OK"){ 
        $lat = $geocode['results'][0]['geometry']['location']['lat'];
        $lon = $geocode['results'][0]['geometry']['location']['lng']; 

$R = 6371;  // earth's mean radius, km  

// first-cut bounding box (in degrees)
$maxLat = $lat + rad2deg($radius/$R);
$minLat = $lat - rad2deg($radius/$R);

// compensate for degrees longitude getting smaller with increasing latitude
$maxLon = $lon + rad2deg($radius/$R/cos(deg2rad($lat)));
$minLon = $lon - rad2deg($radius/$R/cos(deg2rad($lat)));

        $conditions[] = ['Venues.lat' => "'BETWEEN '".$minLat."' AND '".$maxLat."'"];
        $conditions[] = ['Venues.lon' => "'BETWEEN :'".$minLon."' AND :'".$maxLon."'"];
       }

 $this->paginate =[ 
        'limit' => 10,
        'order' => ['Quads.date' => 'asc'],
        'conditions' => $conditions,
        'contain' => [
            'Performers' => ['Genres'],
            'Users' => ['Profiles'],
            'Venues' => ['fields' => [
                    'name',
                    'id',
                    'verified',
                    'address1',
                    'city',
                    'zip_code'], 'States'=>['Countries']],
            'Categories',
            'Likes' => ['Users' => ['Profiles']]]];

    $quads = $this->paginate();

Upvotes: 0

Views: 116

Answers (1)

ndm
ndm

Reputation: 60493

Impossible is (nearly) nothing. The old virtual fields concept is gone, right, the new ORM is flexible enough so that this isn't necessary anymore.

Your problem is that you are defining the conditions the wrong way, what you are doing there by specifying key => value sets, is creating ordinary operator conditions, where the value is going to be escaped/casted according to the column type. In case you really don't receive any errors, I'd assume that the lat/lan columns are of a numeric type, so your BETWEEN ... strings do end up as numbers, and the conditions will look something like

Venus.lat = 0 AND Venus.lon = 0

Also note that you are creating a nested array, ie 

[
    ['keyA' => 'value'],
    ['keyB' => 'value']
]

and while this works, you may run into further problems in case you're not aware of it, so you'd better stick with

[
    'keyA' => 'value',
    'keyB' => 'value'
]

unless there's actually a technical reason to use nested conditions.

tl;dr use expressions

That being said, you can use expressions to build the proper conditions, like

$conditions[] = $this->Quads->Venues
    ->query()->newExpr()->between('Venues.lat', $minLat, $maxLat);

$conditions[] = $this->Quads->Venues
    ->query()->newExpr()->between('Venues.lon', $minLon, $maxLon);

This will safely create proper conditions like

Venus.lat BETWEEN a AND b AND Venus.lon BETWEEN x AND Y

Note that it is advised to create the expressions via the table that holds the columns (VenuesTable in this case), as you'd otherwise have to manually specify the column type (see the fourth argument of QueryExpression::between()) in order for the correct casting/escaping to be applied!

See also

Upvotes: 1

Related Questions