0

In my script I try to filter the selected data by its distance to the user. To achieve that I use this query which shall return every post in the radius of 10 kilometers:

$query = "SELECT * FROM Table WHERE ((rad2deg(acos((sin(deg2rad($actlat))*sin(deg2rad(lat))) + (cos(deg2rad($actlat))*cos(deg2rad(lat))*cos(deg2rad($actlon-lon))))))*111.13384) < 10";

The problem I‘m facing now is that I get this error returned: 

Uncaught mysqli_sql_exception: FUNCTION database_name.rad2deg does not exist

So I tried it another way where the function works as it should:

$test = rad2deg(x);
echo $test;

Why can‘t I use the rad2deg function in my query?

Mike Kng
  • 255
  • 2
  • 11
  • 1
    rad2deg is a php function not a mysql one. – Roland Starke Sep 28 '18 at 11:18
  • Perform your calculations in PHP and assign the result to a variable, then use that in your query. – iainn Sep 28 '18 at 11:20
  • Oh okay, that‘s helpful! But the variables `lat` and `lon` are other values from the respective entry. How can I access them from outside the query like that? – Mike Kng Sep 28 '18 at 11:24
  • MySQL has native support for all the functions you need here, you'll mostly just need to change the names and you can run this purely in SQL. See https://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html – iainn Sep 28 '18 at 11:27
  • https://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points might also be useful, if you can make use of the native spatial types – iainn Sep 28 '18 at 11:28

1 Answers1

3

You can use the degrees() mysql function that converts radian values to degrees and the radians() function to convert from degrees to radians:

$query = "SELECT * FROM Table WHERE ((degrees(acos((sin(radians($actlat))*sin(radians(lat))) + (cos(radians($actlat))*cos(radians(lat))*cos(radians($actlon-lon))))))*111.13384) < 10";
nacho
  • 5,280
  • 2
  • 25
  • 34