get by latitude and longitude in laravel 5 with other joins get by latitude and longitude in laravel 5 with other joins laravel laravel

get by latitude and longitude in laravel 5 with other joins


Below code would be able to retrieve the results in the way you wanted. Code explanation in-line.

if($request->input("postcode")) {    $curl = curl_init();    curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);    curl_setopt($curl, CURLOPT_URL, "http://api.postcodes.io/postcodes/" . $request->input('postcode'));    $result = json_decode(curl_exec($curl));    curl_close($curl);    $postcode_lat = $result->result->latitude;    $postcode_long = $result->result->longitude;       $query = DB::table('dogs');    //Join statement responsible for retrieving dogs addresses based on latitude and longitude in address table.    $query->join(DB::raw('(SELECT  dog_id, (                              3959 * acos (                              cos ( radians($postcode_lat) )                              * cos( radians( lat ) )                              * cos( radians( long ) - radians($postcode_long) )                              + sin ( radians($postcode_lat) )                              * sin( radians( lat ) )                            )                           )AS distance from dog_addresses) as dog_addresses'), function ($join){            $join->on('dogs.id', '=', 'dog_addresses.dog_id')        });     $query->leftJoin('dog_videos','dogs.id','=','dogs_videos.dog_id');    $query->leftJoin('dogs_breeds','dogs.breed_id','=','dogs_breeds.id');    if($request->input("breed") && $request->input("breed") != "" && $request->input("breed") != "any")    {        $breed = Dog_Breed::where("breed_name", $request->input("breed"))->first();        $query->where('dogs.breed_id', $breed->id);    }    $results = $query->orderBy('dog_addresses.distance', 'ASC') //Ordering the results in ascending order by calculated distance                     ->limit(20) //Limiting the results to 20 . Can be changed or removed according to your needs                    ->get(); //Retrieving the results}


You can try the GLength function of mysql:

SELECT     *,  GLength(LineString(GeomFromText('POINT(latPosCode,lngPosCode)'),GeomFromText('POINT(latInDataBase,lngInDataBase)'),)) AS DISTANCEFROM dogs           ORDER BY DISTANCE