So I'm trying to find the closest points to another particular point within a certain distance, and I'm using the haversine formula for this. The raw query itself is:
SELECT id,
( 3959 * acos( cos( radians(input_lat) ) * cos( radians( map_loc_lat ) ) * cos( radians( map_loc_long ) - radians(input_long) ) + sin( radians(input_lat) ) * sin( radians( map_loc_lat ) ) ) )
AS distance FROM posts HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
Where input_lat
and input_long
are my predefined coordinates that I want my center searching point to begin from, and map_loc_lat
and map_loc_long
are the posts
' points.
I'm trying to use Eloquent's selectRaw()
method to do a portion of the heavy lifting, like so:
$query->selectRaw('id, ( 6371 * acos( cos( radians(?) ) * cos( radians( map_loc_lat ) )
* cos( radians( ? ) - radians(?) ) + sin( radians(?) )
* sin( radians( map_loc_lat ) ) ) ) AS distance', )
But I'm confused on how I should specify the question marks to indicate my input latitude and longitude using Eloquent, plus how would I continue chaining on my order by clause and my limits?
Used the same sql in one of my projects. Did not find any other solution instead of using DB::raw()
and select()
Here's example :
$distance = Input::get('distance', 0.1);
$lat = Input::get('latitude');
$lng = Input::get('longitude');
$select="drops.*,(6371 * acos( cos( radians({$lat}) ) * cos( radians( drops.latitude ) ) * cos( radians( drops.longitude ) - radians({$lng}) ) + sin( radians({$lat}) ) * sin( radians(drops.latitude) ) )) AS distance";
$drops_query = DB::table('drops')->select(DB::raw($select))
->addSelect('users.login as username')
->leftJoin('users', 'users.id', '=', 'drops.user_in')
->where('drops.latitude','<>','')
->where('drops.longitude','<>','');
$drops_query = $drops_query->having('distance', '<=', $distance)->orderBy('distance','DESC');
$drops = $drops_query->take($limit)->get();
This approach let me append to this complex query some additional filtering, pagination and etc.