Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

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

Last updated 2 years ago.
0

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.
0

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.
0

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.
0

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;
}
Last updated 2 years ago.
0

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!

Last updated 2 years ago.
0

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.
0

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.
0

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

0

Sign in to participate in this thread!

Eventy

Your banner here too?

Jameron jameron Joined 16 Apr 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

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

© 2024 Laravel.io - All rights reserved.