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

Last updated
2 years ago.

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

Last updated
2 years ago.

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!

Last updated
2 years ago.

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

Last updated
2 years ago.

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

Last updated
2 years ago.

dsamojlenkosaid: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!

Last updated
2 years ago.

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

Last updated
8 years ago.

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

Last updated
8 years ago.

Sign in to participate in this thread!

The Laravel portal for problem solving, knowledge sharing and community building.

The community

© 2024 Laravel.io - All rights reserved.