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
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 |