Level 50
It's not really an answer to your question - but if you are doing this kind of thing you might be interested in Postgres's GIS facilities : http://postgis.net/
Sep 18, 2015
5
Level 1
Haversine query using Eloquent
Hi, I need some help with eloquent relationships. I have a 'User' table having two columns: 'latitude' and 'longitude' A 'User' is owner of many 'Products', so in my 'Product' model I have:
public function owner()
{
return $this->belongsTo('App\User', 'user_id');
}
I need a method that returns all the products of the owners that are positioned within a certain distance, which I calc using the params lat, lng, and radius, using the haversine formula. I could do it using the query builder:
public static function findByLatLng( $lat, $lng, $radius = 1) {
$products = DB::table('products')
->select('products.*', DB::raw("( 6371 * acos( cos( radians($lat) ) *
cos( radians( latitude ) )
* cos( radians( longitude ) - radians($lng)
) + sin( radians($lat) ) *
sin( radians( latitude ) ) )
) AS distance"))
->join('users', function($join)
{
$join->on('products.user_id', '=', 'users.id');
})
->having("distance", "<", $radius)
->get();
}
My problem is that I need the same but returning a Eloquent Model, to use in my views...I'm trying to do:
$products = Product::with(['owner' => function($query) use ($latitude, $longitude, $radius) {
$query->selectRaw('( 6371 * acos( cos( radians(?) ) *
cos( radians( latitude ) )
* cos( radians( longitude ) - radians(?)
) + sin( radians(?) ) *
sin( radians( latitude ) ) )
) AS distance', [$latitude, $longitude])
->havingRaw("distance < ?", [$radius]);
}])->get();
But I get the following sql error: 'Invalid parameter number' I appreciate any help!
Level 3
$products = Product::with(['owner' => function($query) use ($latitude, $longitude, $radius) {
$query->selectRaw('( 6371 * acos( cos( radians(?) ) *
cos( radians( latitude ) )
* cos( radians( longitude ) - radians(?)
) + sin( radians(?) ) *
sin( radians( latitude ) ) )
) AS distance', [$latitude, $longitude])
->havingRaw("distance < ?", [$radius]);
}])->get();
Change in
$products = Product::with(['owner' => function($query) use ($latitude, $longitude, $radius) {
$query->selectRaw('( 6371 * acos( cos( radians(?) ) *
cos( radians( latitude ) )
* cos( radians( longitude ) - radians(?)
) + sin( radians(?) ) *
sin( radians( latitude ) ) )
) AS distance', [$latitude, $longitude,$latitude])
->havingRaw("distance < ?", [$radius]);
}])->get();
You gave two parameters, but you use three, $latitude two times.
1 like
Level 1
Thanks man ! Very simple mistake!
I fixed it, but just realized that this is not working as I expected, because is given me ALL Products, and I need only the products that the owner is located até that distance...
So I have not figured out how to do this.
Level 1
I've just solved!
$products = Product::select('products.*')
->join('users', function($join)
{
$join->on('products.user_id', '=', 'users.id');
})
->selectRaw('( 6371 * acos( cos( radians(?) ) *
cos( radians( latitude ) )
* cos( radians( longitude ) - radians(?)
) + sin( radians(?) ) *
sin( radians( latitude ) ) )
) AS distance', [$latitude, $longitude, $latitude])
->havingRaw("distance < ?", [$radius])
->get();
Thanks!
4 likes
Level 5
We have a similar need for something like this however our location data is stored on a relation. How would I construct a similar query using a related column?
We have posts which have a location such as $post->location->latitude. I would want to fetch all posts within a certain distance of the users location. We currently have all of this set with ElasticSearch but I am looking at how to fall back to Eloquent and MySQL in the case ElasticSearch goes down.
Any Ideas?
Please or to participate in this conversation.