php - 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('AppUser');
    }

    public $timestamps = true;
}

User model

use IlluminateAuthAuthenticatable;
use IlluminateDatabaseEloquentModel;
use IlluminateAuthPasswordsCanResetPassword;
use IlluminateFoundationAuthAccessAuthorizable;
use IlluminateContractsAuthAuthenticatable as AuthenticatableContract;
use IlluminateContractsAuthAccessAuthorizable as AuthorizableContract;
use IlluminateContractsAuthCanResetPassword 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('AppProduct');
    }
}

Answer 1

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.