Skip to content

Instantly share code, notes, and snippets.

@stevenmaguire
Last active March 28, 2020 22:40
Show Gist options
  • Save stevenmaguire/3ada3f73f1ad03356cf5 to your computer and use it in GitHub Desktop.
Save stevenmaguire/3ada3f73f1ad03356cf5 to your computer and use it in GitHub Desktop.
Laravel (Illuminate) query builder scope to list neighboring locations within a given distance from a given location
<?php
/**
* Query builder scope to list neighboring locations
* within a given distance from a given location
*
* @param Illuminate\Database\Query\Builder $query Query builder instance
* @param mixed $lat Lattitude of given location
* @param mixed $lng Longitude of given location
* @param integer $radius Optional distance
* @param string $unit Optional unit
*
* @return Illuminate\Database\Query\Builder Modified query builder
*/
public function scopeDistance($query, $lat, $lng, $radius = 100, $unit = "km")
{
$unit = ($unit === "km") ? 6378.10 : 3963.17;
$lat = (float) $lat;
$lng = (float) $lng;
$radius = (double) $radius;
return $query->having('distance','<=',$radius)
->select(DB::raw("*,
($unit * ACOS(COS(RADIANS($lat))
* COS(RADIANS(latitude))
* COS(RADIANS($lng) - RADIANS(longitude))
+ SIN(RADIANS($lat))
* SIN(RADIANS(latitude)))) AS distance")
)->orderBy('distance','asc');
}
@kz
Copy link

kz commented Feb 20, 2015

I see the same query all around the web, but I'm wondering whether it's possible to optimise this further? I don't see it possible to cache a query if you have many users with different longitudes and latitudes. This seems pretty heavy if you have >100 venues in the database.

@imjonos
Copy link

imjonos commented Mar 28, 2020

Ok 5 years ))) But I changed it like this

public function scopeDistance($query, $lat, $lng, $radius = 100, $unit = "km")
    {

        $unit = ($unit === "km") ? 6378.10 : 3963.17;
        $lat = (float) $lat;
        $lng = (float) $lng;
        $radius = (double) $radius;
        $sql =  "($unit * ACOS(COS(RADIANS($lat))
                * COS(RADIANS(latitude))
                * COS(RADIANS($lng) - RADIANS(longitude))
                + SIN(RADIANS($lat))
                * SIN(RADIANS(latitude))))";

        return $query->whereRaw($sql.'<='.$radius)
            ->select(DB::raw("*, $sql AS distance")
            )->orderBy('distance','asc');
    }

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment