implementing haversine distance query based on lng and lat

Question

I have run into a problem when developing this.

I have this scope function implemented that I run on the model like

Listing::closest($lat, $lng)->paginate(5);

public function scopeClosest($query, $lat, $lng, $distance = 0, $units = 'km')
{

    switch ( $units ) {
        case 'miles':
            //radius of the great circle in miles
            $gr_circle_radius = 3959;
        break;
        case 'km':
            //radius of the great circle in kilometers
            $gr_circle_radius = 6371;
        break;
    }

    return $query->selectRaw(
        '*, ( '.$gr_circle_radius.' * acos( cos( radians('.$lat.') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('.$lng.') ) + sin( radians('.$lat.') ) * sin( radians( lat ) ) ) ) AS distance'
    )->havingRaw("distance < ?", [10] );

}

However I run into this error that I do not now how to fix

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from listings having distance < 10)

It seems that laravel is running 2 queries, this is how it looks if I do not use havingRaw()

array:2 [▼
  0 => array:3 [▼
    "query" => "select count(*) as aggregate from `listings`"
    "bindings" => []
    "time" => 0.48
  ]
  1 => array:3 [▼
    "query" => "select *, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance from `listings` limit 5 offset 0"
    "bindings" => []
    "time" => 0.97
  ]
]

When I use havingRaw it seems laravel applies it to the first query and ofcourse it will fail. But why does it apply it to the first query and not the second one?

Answer 1

This is what I did in my case .. I need to query distance which were in 5 kms radius . I used whereRaw along with paginate

$vtl = VehicleTrackerLog::whereRaw("(
                                    111.1111 
                                    *DEGREES(ACOS(COS(RADIANS('-33.873415'))
                                    * COS(RADIANS(lat))
                                    * COS(RADIANS('151.227538' - lng))+ SIN (RADIANS('-33.873415'))         
                                    * SIN(RADIANS(lat))))<=5)"
                                    )->paginate(5);

If you want in Miles simply multiply by 69.041236 instead of 111.111