-
-
Save kimmellj/3913489 to your computer and use it in GitHub Desktop.
DELIMITER ;; | |
/*!50003 CREATE*/ /*!50020 DEFINER=`foobar`@`localhost`*/ /*!50003 FUNCTION `distance`( lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE ) RETURNS DOUBLE | |
DETERMINISTIC | |
BEGIN | |
RETURN 3956 * 2 * ASIN(SQRT(POWER(SIN((lat1 - lat2) * pi()/180 / 2), 2) + COS(lat1 * pi()/180) * COS(lat2 * pi()/180) * POWER(SIN((lon1 - lon2) * pi()/180 / 2), 2))); | |
END */;; |
in Miles
how can i implement this using laravel,have tried it so many times but its outputting this error
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'long) / 2),2) ))) as distance, (6371 * 2 * ASIN(SQRT(POWER(SIN((6.63484 - lat) /' at line 1 (SQL: select *, (6371 * 2 * ASIN(SQRT(POWER(SIN((6.63484 - lat) / 2),2) + COS(6.63484) * COS(lat) * POWER(SIN((3.33647 - long) / 2),2) ))) as distance, (6371 * 2 * ASIN(SQRT(POWER(SIN((6.63484 - lat) / 2),2) + COS(6.63484) * COS(lat) * POWER(SIN((3.33647 - long) / 2),2) ))) as distance from users
where user_type
= Agency having distance
<= 10 order by distance
asc)
my code
$lat = Auth::user()->lat;
$long = Auth::user()->long;
$radius = 10;
$raw = DB::raw('(6371 * 2 * ASIN(SQRT(POWER(SIN(('.$lat.' - lat) / 2),2) + COS('.$lat.') * COS(lat) * POWER(SIN(('.$long.' - long) / 2),2) ))) as distance');
$location = DB::table('users')->select('*', $raw)
->addSelect($raw)->where('user_type', 'Agency')
->orderBy('distance', 'ASC')
->having('distance', '<=', $radius)->get();
return response()->json($location);
while lat and long are column in my user tables
in km ?