samedi 30 juillet 2016

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://ift.tt/1Ot6dhd" . $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



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire