CODEWITHSUNDEEP
phpmysqlsqllaravelhaversine
Robke22
Robke22

Reputation: 111

Haversine distance calculation between two points in Laravel

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

Upvotes: 11

Views: 27653

Answers (6)

jibril90
jibril90

Reputation: 99

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

Upvotes: 0

K-Alex
K-Alex

Reputation: 380

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();

Upvotes: 1

martinstoeckli
martinstoeckli

Reputation: 24101

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:

  1. Calculate an envelope with minimum/maximum of latitude and longitude, it should be a bit bigger than your search radius.
  2. Make a fast query with a join of product and user, and just check whether the user location is inside this envelope.
  3. 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.

Upvotes: 0

version 2
version 2

Reputation: 1059

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;
      }
    }

Upvotes: 3

Ohgodwhy
Ohgodwhy

Reputation: 50787

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.

Upvotes: 27

Sergio Mattei
Sergio Mattei

Reputation: 414

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

Upvotes: 1

Related Questions