Dw1-nl Dw1-nl - 1 month ago 5
MySQL Question

GEO location query PHP and SQL with Laravel

I'm currently working on a campaign/advertisement script in Laravel 5.2. I'm having a table with ads, for example: Ad name, Location (lat/long), Radius (+10km).

Now I have a user location (lat/long). I want to see if he is in the radius of any ad and show the ad to him.

I have this:

public function scopeAdsInLocation($query, $from_latitude, $from_longitude)
{
$query = CampaignModel::
where(\DB::raw('111.1111 * DEGREES(ACOS(COS(RADIANS(' . $from_latitude . ')) * COS(RADIANS(campaigns.loc_lat)) * COS(RADIANS(' . $from_longitude .' -
campaigns.loc_long)) + SIN(RADIANS(' . $from_latitude . ')) * SIN(RADIANS(campaigns.loc_lat))))'), '<=', 'campaigns.loc_radius')
->get();
return $query;
}


I call it like this:

$ads = CampaignModel::adsInLocation(51.191320, 5.987772);
var_dump($ads);


This code works, but only if I set the radius to a fixed value. So when I replace campaigns.loc_radius with '100' it works. But with the radius each campaign has it doesn't seem to do the job. Do you know maybe why? Or have a solution for this.

Answer

In your where(), you can use

\DB::raw("`campaigns`.`loc_radius`")

So your query will be as:

public function scopeAdsInLocation($query, $from_latitude, $from_longitude)
{
    $query = CampaignModel::
    where(\DB::raw('111.1111 * DEGREES(ACOS(COS(RADIANS(' . $from_latitude . ')) * COS(RADIANS(campaigns.loc_lat)) * COS(RADIANS(' . $from_longitude .' -
        campaigns.loc_long)) + SIN(RADIANS(' . $from_latitude . ')) * SIN(RADIANS(campaigns.loc_lat))))'), '<=', \DB::raw("`campaigns`.`loc_radius`"))
        ->get();
    return $query;
}