Haversine distance calculation between two points in Laravel

Question

I'm working on a Laravel application in which I need to find all the products within a certain radius of the user's coordinates. Products have a one-to-many relationship with users so that users can have multiple products. I've found that the haversine algorithm can calculate the distance between two points, but I can't seem to make it work.

I've got the following query.

Controller

$latitude = 51.0258761;
$longitude = 4.4775362;
$radius = 20000;

$products = Product::with('user')
->selectRaw("*,
            ( 6371 * acos( cos( radians(" . $latitude . ") ) *
            cos( radians(user.latitude) ) *
            cos( radians(user.longitude) - radians(" . $longitude . ") ) + 
            sin( radians(" . $latitude . ") ) *
            sin( radians(user.latitude) ) ) ) 
            AS distance")
->having("distance", "<", $radius)
->orderBy("distance")
->get();

I've set the radius to 20000 for testing purposes, and it appears all products have a distance of 5687,... The problem seems to be that the latitude and longitude of the products are stored in the User table, but I'm not sure how I can access those in my query. I've tried user.latitude and 'user->latitude', but nothing seems to work.

Product model

class Product extends Model
{
    protected $fillable =
        [
            'soort',
            'hoeveelheid',
            'hoeveelheidSoort',
            'prijsPerStuk',
            'extra',
            'foto',
            'bio'
        ];

    public function User()
    {
        return $this->belongsTo('App\User');
    }

    public $timestamps = true;
}

User model

use Illuminate\Auth\Authenticatable;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Auth\Passwords\CanResetPassword;
use Illuminate\Foundation\Auth\Access\Authorizable;
use Illuminate\Contracts\Auth\Authenticatable as AuthenticatableContract;
use Illuminate\Contracts\Auth\Access\Authorizable as AuthorizableContract;
use Illuminate\Contracts\Auth\CanResetPassword as CanResetPasswordContract;

class User extends Model implements AuthenticatableContract,
                                    AuthorizableContract,
                                    CanResetPasswordContract
{
    use Authenticatable, Authorizable, CanResetPassword;

    protected $table = 'users';

    protected $fillable = 
        [
        'firstName', 
        'lastName', 
        'adres',
        'profilepic',
        'description', 
        'longitude',
        'latitude',
        'email', 
        'password'
    ];

    protected $hidden = ['password', 'remember_token'];

    public function product()
    {
        return $this->hasMany('App\Product');
    }
}

Ohgodwhy · Answer 1 · 2016-06-17T10:34:39.057

23

This was my implementation of it. I've chosen to alias my query out ahead of time, this way I can take advantage of Pagination. Furthermore, you need to explicitly select the columns that you wish to retrieve from the query. add them at the ->select(). Such as users.latitude, users.longitude, products.name, or whatever they may be.

I have created a scope which looks something like this:

public function scopeIsWithinMaxDistance($query, $location, $radius = 25) {

     $haversine = "(6371 * acos(cos(radians($location->latitude)) 
                     * cos(radians(model.latitude)) 
                     * cos(radians(model.longitude) 
                     - radians($location->longitude)) 
                     + sin(radians($location->latitude)) 
                     * sin(radians(model.latitude))))";
     return $query
        ->select() //pick the columns you want here.
        ->selectRaw("{$haversine} AS distance")
        ->whereRaw("{$haversine} < ?", [$radius]);
}

You can apply this scope to any model with a latitude andlongitude.

Replace the $location->latitude with your latitude that you wish to search against, and replace the $location->longitude with the longitude that you wish to search against.

Replace the model.latitude and model.longitude with the Models you wish to find around the $location based on the distance defined in the $radius.

I know you have a functioning Haversine formula, but if you need to Paginate you can't use the code you've supplied.

Hopefully this helps.

edited Jun 17 '16 at 10:34

answered Jun 17 '16 at 09:04

Ohgodwhy

47,967
9
74
103

hi thank you for your answer, I am wondering if radius is in kilometers or miles? – niko craft Oct 26 '16 at 17:40
1

@Maxlight This would be for KM. For miles, you'd use `3961` instead of `6371`. – Ohgodwhy Oct 27 '16 at 18:00
Great i needed km – niko craft Oct 27 '16 at 18:02
`distance` column is not included in the result. how do I add that in the result? – imrealashu Dec 07 '17 at 13:52
@imrealashu Add it to the `select()`, we alias `{$haversine} as distance` so you can `select('distance')` – Ohgodwhy Dec 07 '17 at 20:57
I tried that but that didn't work saying `distance` column doesn't exist. However I have `distance` in the raw query so it was available without even adding it in `select()` method. @Ohgodwhy – imrealashu Dec 08 '17 at 08:51
@imrealashu Yep, that's true! My fault on that one. – Ohgodwhy Dec 08 '17 at 17:59
Hi @Ohgodwhy I am not knowing much about laravel or raw query is your answer safe enough? and yet some article told me its not safe to pass variable to the raw query – Aslam H Feb 25 '20 at 13:25
note: if you have the error saying `distance is not available` or something like that: its not working for postgres. – nutzt Apr 13 '22 at 09:53

score 3 · Answer 2 · answered Apr 25 '18 at 10:03

Create this function in your Model

 public static function getNearBy($lat, $lng, $distance,
                                             $distanceIn = 'miles')
        {
            if ($distanceIn == 'km') {
                $results = self::select(['*', DB::raw('( 0.621371 * 3959 * acos( cos( radians('.$lat.') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('.$lng.') ) + sin( radians('.$lat.') ) * sin( radians(lat) ) ) ) AS distance')])->havingRaw('distance < '.$distance)->get();
            } else {
                $results = self::select(['*', DB::raw('( 3959 * acos( cos( radians('.$lat.') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('.$lng.') ) + sin( radians('.$lat.') ) * sin( radians(lat) ) ) ) AS distance')])->havingRaw('distance < '.$distance)->get();
            }
            return $results;
        }

And you can use orderby, groupBy as per your requirement.

What're these numbers "0.621371 * 3959" refer to? as I understand we should put 6371 in case of the distance is "km" and 637100 in case the distance is "meters" and 3961 in case the distance is "miles" because it's the radius of Earth in each one (km - miles - meters). can you explain please why you use these numbers instead? (3959 in miles) and (0.621371 in km) — Abdlrahman Saber, May 07 '22 at 19:29
@AbdlrahmanSaber 1 km = 0.621371 mi so it's just in case we need distance in Km. — PHP Worm..., May 09 '22 at 08:39

score 2 · Answer 3 · answered Jun 17 '16 at 10:47

Using Haversine method, you can calculate distance between two points using this function. It works but I don't know how to implement this in Laravel. Thought of sharing this anyway.

$lat1 //latitude of first point
$lon1 //longitude of first point 
$lat2 //latitude of second point
$lon2 //longitude of second point 
$unit- unit- km or mile

function point2point_distance($lat1, $lon1, $lat2, $lon2, $unit='K') 
    { 
        $theta = $lon1 - $lon2; 
        $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
        $dist = acos($dist); 
        $dist = rad2deg($dist); 
        $miles = $dist * 60 * 1.1515;
        $unit = strtoupper($unit);

        if ($unit == "K") 
        {
            return ($miles * 1.609344); 
        } 
        else if ($unit == "N") 
        {
        return ($miles * 0.8684);
        } 
        else 
        {
        return $miles;
      }
    }

score 1 · Answer 4 · answered Jun 17 '16 at 08:05

1

If you are willing to use an external package instead, I suggest the infinitely useful PHPGeo library. I used it on a project that relied on these exact calculations, and it worked just fine. It saves you writing the calculations yourself from scratch and is tested to work.

https://github.com/mjaschen/phpgeo

Here is the documentation for Harvesine: https://phpgeo.marcusjaschen.de/#_distance_between_two_coordinates_haversine_formula

answered Jun 17 '16 at 08:05

Sergio E. Diaz

396
3
15

Thanks, but the haversine formula I used doesn't seem to be the problem. I just can't seem to figure out how I can access the latitude and longitude of the products which is stored in the owner (user) of the product, and not in the product itself – Robke22 Jun 17 '16 at 08:27
Perfect! This proved to be a simple and effective solution for my case. :-) – ankush981 Dec 25 '17 at 06:36

martinstoeckli · Answer 5 · 2016-06-17T11:46:20.337

I think what you need is the query builder to build a join. With a join you have the fields of both tables available in your query. Currently you are using relationships with eager loading, this will preload the related users, but they cannot be used inside the SQL (Laravel will actually execute 2 queries).

Anyway I wouldn't try to calculate the haversine formula in one step with SQL, this cannot be really performant, and the query could become difficult to maintain in my opinion. This is what i would do instead:

Calculate an envelope with minimum/maximum of latitude and longitude, it should be a bit bigger than your search radius.
Make a fast query with a join of product and user, and just check whether the user location is inside this envelope.
For each element of the resulting list calculate the exact haversine distance with PHP (not SQL), delete rows which are outside the radius, and sort the list accordingly.

score 0 · Answer 6 · answered Oct 31 '16 at 13:03

This is a code I am using:

            $ownerLongitude = $request['longitude'];
            $ownerLatitude = $request['latitude'];
            $careType = 1;
            $distance = 3;

            $raw = DB::raw(' ( 6371 * acos( cos( radians(' . $ownerLatitude . ') ) * 
 cos( radians( latitude ) ) * cos( radians( longitude ) - radians(' . $ownerLongitude . ') ) + 
    sin( radians(' . $ownerLatitude . ') ) *
         sin( radians( latitude ) ) ) )  AS distance');
            $cares = DB::table('users')->select('*', $raw)
        ->addSelect($raw)->where('type', $careType)
        ->orderBy('distance', 'ASC')
        ->having('distance', '<=', $distance)->get();

score 0 · Answer 7 · answered May 05 '20 at 18:25

I got a solution in Laravel.

    public function near($myLon, $myLat, $areaLon, $areaLat)
{
    $this->applyCriteria();
    $this->applyScope();

    $results = $this->model->select(DB::raw("SQRT(
        POW(69.1 * (latitude - " . $myLat . "), 2) +
        POW(69.1 * (" . $myLon . " - longitude) * COS(latitude / 57.3), 2)) AS distance, SQRT(
        POW(69.1 * (latitude - " . $areaLat . "), 2) +
        POW(69.1 * (" . $areaLon . " - longitude) * COS(latitude / 57.3), 2)) AS area"), "YOUR_TABLE.*")->get();

    $this->resetModel();
    $this->resetScope();

    return $this->parserResult($results);
}

The answer is in Miles, you will have to replace YOUR_TABLE with the name of your database table. Thank you hope it helps

Haversine distance calculation between two points in Laravel

7 Answers7

Linked