How to query distances between two coordinates with Eloquent

Question

I know this has been ask many times. But I didn't figure out to make it according to my needs.

I need to query the nearest users from another user. Basically, I have a users table this table has a one to one relation with the users_locations table which has a latitude and a longitude field.

So I've seen this https://laravel.io/forum/04-23-2014-convert-this-geolocation-query-to-query-builder-for?page=1 and this may be the best solution.

But my basic query is :

\App\Model\User::whereNotIn('id', $ids)
               ->where('status', 1)
               ->whereHas('user_location', function($q) use ($lat, $lng, $radius) {
                    /** This is where I'm stuck to write the query **/
             })->select('id', 'firstname')
               ->get();

I don't figure out how to implement the solution in this case.

Thank you in advance for your help

EDIT To be more clear: I need to get the users that are in a 5 kilometers radius.

see: http://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula — Serge, Mar 15 '17 at 23:01
Well yes thank you, but I already have this function, I need to do this with MySQL because I have more than 50000 users and in this case I should get all of these users then calculate distance and finally keep all users that are close enough, and this could take too much resources.. — KeizerBridge, Mar 15 '17 at 23:13
You should really use a spatial index, however you could do it directly in the query like in the example in this answer: http://stackoverflow.com/questions/8994718/mysql-longitude-and-latitude-query-for-other-rows-within-x-mile-radius — Serge, Mar 15 '17 at 23:30
Did you check this thread? http://stackoverflow.com/questions/37876166/haversine-distance-calculation-between-two-points-in-laravel — EddyTheDove, Mar 16 '17 at 00:05
Oh this is perfect ! exactly what I needed, thank you EddyTheDove. — KeizerBridge, Mar 16 '17 at 01:17

KeizerBridge · Accepted Answer · 2017-06-26T19:21:01.007

I found the solution, thanks to EddyTheDove and Ohgodwhy.

So this is it:

\App\Model\User::whereNotIn('id', $ids)
           ->where('status', 1)
           ->whereHas('user_location', function($q) use ($radius, $coordinates) { 
                  $q->isWithinMaxDistance($coordinates, $radius);
         })->select('id', 'firstname')
           ->get();

And in my UserLocation Model I have this local scope

public function scopeIsWithinMaxDistance($query, $coordinates, $radius = 5) {

    $haversine = "(6371 * acos(cos(radians(" . $coordinates['latitude'] . ")) 
                    * cos(radians(`latitude`)) 
                    * cos(radians(`longitude`) 
                    - radians(" . $coordinates['longitude'] . ")) 
                    + sin(radians(" . $coordinates['latitude'] . ")) 
                    * sin(radians(`latitude`))))";

    return $query->select('id', 'users_id', 'cities_id')
                 ->selectRaw("{$haversine} AS distance")
                 ->whereRaw("{$haversine} < ?", [$radius]);
}

The original answer by Ohgodwhy is here: Haversine distance calculation between two points in Laravel

EDIT

Another way to perform it with stored functions in MySQL:

    DELIMITER $$
DROP FUNCTION IF EXISTS haversine$$

CREATE FUNCTION haversine(
        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) RETURNS FLOAT
    NO SQL DETERMINISTIC
    COMMENT 'Returns the distance in degrees on the Earth
             between two known points of latitude and longitude'
BEGIN
    RETURN DEGREES(ACOS(
              COS(RADIANS(lat1)) *
              COS(RADIANS(lat2)) *
              COS(RADIANS(lon2) - RADIANS(lon1)) +
              SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))
            ));
END$$

DELIMITER ;

I multiply by 111.045 to convert the result in km. (I'm not sure that this value is right, I found many others values not far from this one so if someone have precision about it, it could be nice)

Original article: https://www.plumislandmedia.net/mysql/stored-function-haversine-distance-computation/

Then using eloquent:

\App\Model\User::whereNotIn('id', $ids)
       ->where('status', 1)
       ->whereHas('user_location', function($q) use ($radius, $coordinates) { 
            $q->whereRaw("111.045*haversine(latitude, longitude, '{$coordinates['latitude']}', '{$coordinates['longitude']}') <= " . $radius]);
     })->select('id', 'firstname')
       ->get();

How to query distances between two coordinates with Eloquent

1 Answers1

Linked