sql - An invalid floating point operation occurred when checking longitude and latitudes -

sql - An invalid floating point operation occurred when checking longitude and latitudes -

i know there lot of questions mine asked. looked @ them, can't seem resolve problem. i'm bad in math :s.

in c# i'm using sqlquery cities in range. works of time, except cities.

for example, if inquire nearby cities of city postalcode 2060. error. when inquire city code 2000, returns 2000 , 2060.

2060: lat = 51.2293515000 long = 4.4279883000 2000: lat = 51.2198771000 long = 4.4011356000

this query:

return base.database.database.sqlquery<city>( "select * [dbo].[city] @p0 >= (((acos(sin((@p1*pi()/180)) * sin(([latitude]*pi()/180))+cos((@p1*pi()/180)) * cos(([latitude]*pi()/180)) * cos(((@p2- [longitude])*pi()/180))))*180/pi())*60*1.1515*1.609344)" , radius, latitude, longitude);

can explain how can changed works "cities" , reason if error noob in math?

thank you

the problem statement within acos() gets rounding error somtimes when compare cities themselves, , statement becomes on 1, causes error.

you need cap value max 1 somehow. here rather ugly solution:

return base.database.database.sqlquery<city>( "select * [dbo].[city] @p0 >= (((acos((select min(v) (values (1), (sin((@p1*pi()/180)) * sin(([latitude]*pi()/180))+cos((@p1*pi()/180)) * cos(([latitude]*pi()/180)) * cos(((@p2- [longitude])*pi()/180)))) x(v))))*180/pi())*60*1.1515*1.609344)" , radius, latitude, longitude);

i'm not sure, gets smaller -1 well, case same error. you'd have cap value between -1 , 1.

sql sql-server math latitude-longitude