my float function returns NaN
От | Frank Bax |
---|---|
Тема | my float function returns NaN |
Дата | |
Msg-id | 4F7FA671.3000304@sympatico.ca обсуждение исходный текст |
Ответы |
Re: my float function returns NaN
|
Список | pgsql-novice |
I have a function to calculate distance between two points. When asking for distance between two points that are actually the same point; my application (which rounds to only very few decimal places), might return zero or NaN depending on decimal accuracy of point. I am able to reproduce the problem with the following SQL statements. CREATE OR REPLACE FUNCTION geo_deg2rad(float) RETURNS float AS $$ SELECT ($1 * pi()::float / 180::float) $$ LANGUAGE SQL IMMUTABLE CREATE OR REPLACE FUNCTION geo_dist(point,point) RETURNS float AS $$ SELECT 6371.0 * acos( sin(geo_deg2rad($1[0]))*sin(geo_deg2rad($2[0])) + cos(geo_deg2rad($1[0]))*cos(geo_deg2rad($2[0])) * cos(geo_deg2rad($2[1])-geo_deg2rad($1[1])) ) $$ LANGUAGE SQL IMMUTABLE create table geo (p point); insert into geo values (point(44.85051666667,-79.5405)); insert into geo values (point(44.850516667,-79.5405)); select p,geo_dist(p,p) from geo; p | geo_dist ---------------------------+---------------------- (44.85051666667,-79.5405) | NaN (44.850516667,-79.5405) | 0.000134258785931453 Why does the point with more decimal accuracy result in NaN? If its not easy to "fix" my function; is the a function that can convert NaN to zero (like coalesce(p,0) would convert null to zero)? PostgreSQL 9.0.4 on x86_64-unknown-openbsd5.0, compiled by GCC cc (GCC) 4.2.1 20070719 , 64-bit
В списке pgsql-novice по дате отправления: