在公里范围内搜索用户不起作用

I have script where I want to search users within given kilometers from specific city. User in database has city, longitude and latitude. User 1 has NYC, user 2 has Washington DC. I can give distance even 1000km and it doesn;t work. Why?

$user = User::with('user_data');
if ($request->has('city')) {
    $user->whereHas('user_data', function($query) use ($request) {
       return $query->where('residence', $request->city);
   });
}

if ($request->has('city')) {

    $latitude = app('geocoder')->geocode($request->city)->get()->first()->getCoordinates()->getLatitude(); 
    $longitude = app('geocoder')->geocode($request->city)->get()->first()->getCoordinates()->getLongitude();

    $user->whereHas('user_data', function($query) use ($request, $latitude, $longitude) {

        return $query->whereRaw("( 6371 * acos ( cos ( radians(".$latitude.") ) * cos( radians( residence_latitude ) ) * cos( radians( residence_longitude ) - radians(".$longitude.") ) + sin ( radians(".$latitude.") ) * sin( radians( residence_latitude ) ) ) <= ". $request->distance .")");
   });
}

DB

json

It looks like your distance calculation formula is based on the Haversine formula.

We can start to test the distance calculation formula with the sample data provided.

SELECT ( 6371 * acos ( cos ( radians(53.3364746) ) * cos( radians( 53.4285438 ) ) * cos( radians( 14.5528116 ) - radians(15.05037771) ) + sin ( radians(53.3364746) ) * sin( radians( 53.4285438 ) ) )

This returns

3534.1964590146517

(see http://sqlfiddle.com/#!9/72188/8 )

According to the calculator provided by the National Hurricane Center website ( http://www.nhc.noaa.gov/gccalc.shtml ) the answer (rounded to the nearest whole number) should be:

35

So I think simply you need to add one more bit to your formular to divide your answer by 100, in order to get it in kilometres:

SELECT ( 6371 * acos ( cos ( radians(53.3364746) ) * cos( radians( 53.4285438 ) ) * cos( radians( 14.5528116 ) - radians(15.05037771) ) + sin ( radians(53.3364746) ) * sin( radians( 53.4285438 ) ) ) / 100

See http://sqlfiddle.com/#!9/72188/9 for an updated version.

It now returns

35.34196459014652

which is the correct answer.

Translated to your PHP, you simply need to change one line as follows:

return $query->whereRaw("( 6371 * acos ( cos ( radians(".$latitude.") ) * cos( radians( residence_latitude ) ) * cos( radians( residence_longitude ) - radians(".$longitude.") ) + sin ( radians(".$latitude.") ) * sin( radians( residence_latitude ) ) ) / 100 <= ". $request->distance .")");