How do I query by geolocation in Laravel 5?

Question

I am looking to add a geolocation attribute to my "Candidate" Eloquent model. I want to find Candidates based on their location. For instance, find all Candidates within 20 miles of San Francisco. What should my Eloquent model look like to store a Candidate's location in the database?

How do I query for Candidates based on their location? I am using Laravel 5.3.

Answer 1

Personally, I have achieved this previously by storing a set of latitude and longitude coordinates.

You can use something called the Haversine formula to calculate "as the crow flies" distance between a specified set of coordinates and in your case the set of candidates.

Here's an example of such a query. This one, for instance calculates "the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate."

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance 
FROM markers 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20;

Originally found here.

This could quite easily be adapted to suit your needs, if you were to add lat/lng to your Candidate model.

SELECT *, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) 
* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance
FROM candidates
ORDER BY distance 
LIMIT 0, 20;

You could probably implement this as some kind of query scope, e.g. (Not tested and has minimal input sanitization)

public function scopeClosestTo($query, $lat, $lng)
{
    return $query->orderByRaw(
        '(3959 * acos(cos(radians(' . floatval($lat) . ')) * cos(radians(lat)) * cos(radians(lng) - radians(' . floatval($lng) . ')) + sin(radians(' . intval($lat) . ')) * sin(radians(lat)))) ASC'
    );
}

Note: 3959 in this case is the Earth's radius in miles and the formula will calculate distances in miles. If you need distances to be kilometers, change 3959 to 6371. Thanks to @Lionel for pointing this out in the comments.