I'd be interested in seeing how that would be put together in query builder too, but this way also works.
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®ion=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);
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community