David G David G - 3 months ago 24
MySQL Question

get by latitude and longitude in laravel 5 with other joins

This is quite a complicated one for me. I have a route that is a search - which works quite well, except I am unable to search by postcode to find the nearest to the lat and long of a given postcode. That is, I can work out the lat and long, but I am not sure how to integrate it to my existing query. This query is the search query without postcodes:

$query = DB::table('dogs');
$query->leftJoin('dog_addresses','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->get();


I have something to add to the query to get the latitude and longitude of the postcode:

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


This lets me get my postcode latitute and longitude.
But I don't then know how to get the dogs by location based on the lat and long columns present in the dog_addresses table, which is joined to the dogs table. How do I do this?

So if my dog_addresses table has the columns Lat and Long.

So dogs:

id | user_id | dog_name | age


dog_addresses:

id | dog_id | address_line_1 | town | postcode | lat | long


So for my query I need to get all dogs, where bred ID is 1, but I want to inner join videos so I can get all video information and addresses information, but I also want to sort the order of dogs returned by how close they are to my inputted postcode, based on lat and long.

I'm very confused. I found this:

( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance


But I'm still not sure how to integrate it, or what use it is for me.
Please help

Answer

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 retieving 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) 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
}