在laravel 5中通过纬度和经度与其他联接一起获得 [英] 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;            
}

这使我可以获取邮政编码纬度和经度. 但是我不知道如何根据dog_addresses表中存在的经度和纬度列按位置获取狗，该表已与dogs表连接在一起.我该怎么做?

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?

因此，如果我的dog_addresses表具有纬度"和经度"列.

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

所以狗:

id | user_id | dog_name | age

dog_addresses:

dog_addresses:

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

因此，对于我的查询，我需要获得所有犬种，其中育种ID为1，但我想内部加入视频，以便获得所有视频信息和地址信息，但我也想对由以下项返回的狗的顺序进行排序基于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.

我很困惑.我发现了:

( 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

推荐答案

下面的代码将能够以您想要的方式检索结果.内嵌代码说明.

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
}

这篇关于在laravel 5中通过纬度和经度与其他联接一起获得的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！