my float function returns NaN

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: my float function returns NaN
Date: 2012-04-07 02:29:05
Message-ID: 4F7FA671.3000304@sympatico.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Nathaniel Trellice 2012-04-07 08:32:35 Re: my float function returns NaN
Previous Message mephysto 2012-04-06 08:06:12 Re: Partitioned tables and triggers