my float function returns NaN

Поиск
Список
Период
Сортировка
От Frank Bax
Тема my float function returns NaN
Дата
Msg-id 4F7FA671.3000304@sympatico.ca
обсуждение исходный текст
Ответы Re: my float function returns NaN  (Nathaniel Trellice <naptrel@yahoo.co.uk>)
Список 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 по дате отправления:

Предыдущее
От: mephysto
Дата:
Сообщение: Re: Partitioned tables and triggers
Следующее
От: Nathaniel Trellice
Дата:
Сообщение: Re: my float function returns NaN