SQL error for great circle calculation

Question

I was searching stackoverflow and got an answer to my question, but when I tried to implement it with my code I got weird errors.

here is the code:

SELECT `zip_code`, (6371 * acos(cos(radians($latitude)) * cos(radians(`lat`)) * cos(radians(`long`) - radians($longitude)) + sin(radians($latitude)) * sin(radians(`lat`)))) AS `distance`
FROM `places`
HAVING `distance` < $within
ORDER BY `distance` ASC
LIMIT 10;

I get the following error:

Incorrect parameter count in the call to native function 'radians'

I use MySQL version 5.1.44

Is it possible to have NULLS in the fields. I think a null value passed to the function might return this error — Sparky, Dec 25 '11 at 18:46
Was this launched from a shell script? Were both `$latitude` and `$longitude` given values? — Jonathan Leffler, Dec 25 '11 at 19:09
Are the data in your latitude and longitude columns formatted with commas as separators? e.g. 53,789 degrees as opposed to 53.879 If so, they may look to the radians function as if you are passing two arguments separated by a comma instead of one decimal value. — TetonSig, Dec 25 '11 at 19:19
Have you echoed the SQL to standard output before it is executed? This should show what the SQL engine is seeing, and therefore what it is objecting to. — Jonathan Leffler, Dec 25 '11 at 22:00

score 3 · Accepted Answer · answered Dec 25 '11 at 19:28

From the links I found, this is usually caused by passing degree arguments to the radians function with commas separating the whole number from the decimal part of the degree value.

53,779 degrees longitude 4,566 degrees latitude

This looks like two arguments (separated by a comma) to the radians function and it throws the error you see.

The solution is to do some on the fly modification to format your degree arguments with period as the separator instead of commas.

SQL error for great circle calculation

1 Answers1