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?

what is your requirement, can you please explain a bit? Do you want to get closest 5 locations based on given latitude and longitude? — sumit, Feb 01 '17 at 22:39
[You may enjoy my answer here](http://stackoverflow.com/a/37877412/1206267). — Ohgodwhy, Feb 01 '17 at 23:45
If it was me, I'd just run the query and let the user decide if the result should be displayed in km, miles, leagues, chains, or whatever - in JavaScript — Strawberry, Feb 02 '17 at 00:22

sumit · Accepted Answer · 2017-02-01T23:08:06.983

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

edited Feb 01 '17 at 23:08

answered Feb 01 '17 at 22:59

sumit

15,003
12
69
110

I haven't tried this yet but it looks great, to also offer miles as unit what do I substitute 111.1111 with? – niko craft Feb 01 '17 at 23:02
For miles, divide km by 1.609344 – sumit Feb 01 '17 at 23:04
so "<= 5/1.609344"? – niko craft Feb 01 '17 at 23:05

implementing haversine distance query based on lng and lat

1 Answers1