Laravel (Illuminate) query builder scope to list neighboring locations within a given distance from a given location

laravel-nearby-locations-query-scope.php

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

Did you end up getting this working with pagination? It works except for the aggregate count(*) that Laraval does when you use the built-in pagination functions.

@snipe sorry, I built this for a small proof of concept project and did not want to lose it to the ether. I am not certain if it functions with the pagination controls in Laravel. Have you learned more about its compatibility? Are there changes that are needed?

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.

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