I'd be interested in seeing how that would be put together in query builder too, but this way also works.

https://gist.github.com/darkness/11229162

that's how we did it too:

    $buildings = Building::select(
                DB::raw("*,
                              ( 6371 * acos( cos( radians(?) ) *
                                cos( radians( lat ) )
                                * cos( radians( lon ) - radians(?)
                                ) + sin( radians(?) ) *
                                sin( radians( lat ) ) )
                              ) AS distance"))
                ->having("distance", "<", "?")
                ->orderBy("distance")
                ->setBindings([$lat, $lon, $lat,  $radius])
                ->get();

Thanks you both! I am gonna use dsamojlenkos as it seems to work ok for now. I'll have to test it in a more rural area to see how it performs against the original one later.

Saving the first one as a backup!

Mine searches a radius in kms - just note that you'll want to change 6371 to 3959 if you're using miles.

I saw that formula posted a while back when looking for distance stuff, and I thought the same as you. When I realized I didn't want to do the work to put it into a laravel friendly query, I pulled this package. There is a distance function inside. It utilizes The Leagues geotools packages

https://github.com/toin0u/Geotools-laravel

Edit: I use this function to get lat/lng of a location where I only have the other info. *Could probably play code golf on it a bit, but...it does work

public function get_lat_long($address, $city, $state){
    $fullAddress = $address." ".$city." ".$state;
    $urlAddress = str_replace(" ", "+",$fullAddress);
    $url = "http://maps.google.com/maps/api/geocode/json?address=";;
    $url = $url.$urlAddress;
    $url = $url."&sensor=false&region=US";
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_PROXYPORT, 3128);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
    $response = curl_exec($ch);
    curl_close($ch);
    $response_a = json_decode($response);
    $result['lat'] = $response_a->results[0]->geometry->location->lat;
    $result['lng'] = $response_a->results[0]->geometry->location->lng;
    return $result;
}

dsamojlenko said:

that's how we did it too:

   $buildings = Building::select(
               DB::raw("*,
                             ( 6371 * acos( cos( radians(?) ) *
                               cos( radians( lat ) )
                               * cos( radians( lon ) - radians(?)
                               ) + sin( radians(?) ) *
                               sin( radians( lat ) ) )
                             ) AS distance"))
               ->having("distance", "<", "?")
               ->orderBy("distance")
               ->setBindings([$lat, $lon, $lat,  $radius])
               ->get();

This is working very nicely, I checked it against zip-codes.com and all the values are accurate.

Thank you!

I had problems with getting this working. I had to many bindings when outputting the query with toSql. Problem was the setBindings function default sets the binding with the 'where' key in the $this->bindings array. Because the having also needed a binding Laravel puts in another binding with value '?'.

To solve my query became:

        return Building::
            select(DB::raw("*, " .
                "( " .
                    "6371 * " . 
                    "acos( " .
                        "cos( radians(?) ) * " .
                        "cos( radians( lat ) ) * " .
                        "cos( " .
                            "radians( lon ) - radians(?)" .
                        ") + " .
                        "sin( radians(?) ) * " .
                        "sin( radians( lat ) ) " .
                    ")" .
                ") AS distance"))
           ->having("distance", "<", $radius)
           ->orderBy("distance")
           ->setBindings([ $lat, $lon, $lat ], 'select');

why do i keep getting

"Unknown column 'distance' in 'having clause'"

$meetings = Meeting::select(\DB::raw("*, " .
                        "( " .
                        "6371 * " .
                        "acos( " .
                        "cos( radians(?) ) * " .
                        "cos( radians( lat ) ) * " .
                        "cos( " .
                        "radians( lng ) - radians(?)" .
                        ") + " .
                        "sin( radians(?) ) * " .
                        "sin( radians( lat ) ) " .
                        ")" .
                        ") AS distance"))
                        ->having("distance", "<", '25')
                        ->orderBy("distance")
                        ->setBindings([ $lat, $lng, $lat ], 'select')
                        ->paginate(20);

turns out its the paging! how can i correct that!?