Skip site navigation (1) Skip section navigation (2)

Re: my float function returns NaN

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: my float function returns NaN
Date: 2012-04-07 16:24:56
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
On 04/07/12 10:54, Tom Lane wrote:
> Nathaniel Trellice<naptrel(at)yahoo(dot)co(dot)uk>  writes:
>> My guess would be that due to rounding errors, the argument passed to
>> acos() is outwith the interval [-1,1] and acos() is only defined
>> within this interval (for reals).
> Yeah.  It's not helping any that you've defined geo_deg2rad() to do its
> arithmetic in float (that is float4) precision.  Making it work in
> float8 would probably improve matters.  Still, I agree that switching to
> a more numerically stable distance calculation would be a good idea.

Thanks Nathaniel & Tom:

Using float8 rather than float4 for deg2rad() made no difference to 
results; I'll continue to use float8 anyway.

I've read about haversine being more accurate and more expensive 
(cycles), but I haven't been able to find anything that says "how" much 
the accuracy is affected.  My queries are typically between points less 
than 100km apart and rounding error of 1km is acceptable.  When points 
are farther apart; larger error is acceptable.  The extra decimal digits 
provided in my example (which seems to be causing the problems with NaN) 
just happen to be what's provided to me in database.  The NaN result is 
causing problems when I ORDER BY distance since NaN is treated more like 
infinity rather than zero.

Checking bounds of ACOS() does resolve the NaN issue; however, now I 
have a performance question.  If I code the function using "LANGUAGE 
SQL" is the optimizer smart enough to know that distA (see below) should 
only be calculated once?  It might be easier to read my function if I 
present the PHP code used to create it...

$R = "6371.0";
$lat1="geo_deg2rad($1[0])"; $lat2="geo_deg2rad($2[0])";
$lng1="geo_deg2rad($1[1])"; $lng2="geo_deg2rad($2[1])";
$distA="sin($lat1)*sin($lat2) + cos($lat1)*cos($lat2) * cos($lng2-$lng1)";

$distB="CASE WHEN $distA > 1 THEN 1 WHEN $distA < -1 THEN -1 ELSE $distA 

$dist_2="$R * acos( $distB )";

Otherwise I will need to rewrite in something like plpgsql (which I have 
not done before now).

In response to

pgsql-novice by date

Next:From: Jean MAURICEDate: 2012-04-07 19:19:39
Subject: Hello, I am a newbie on Postgres !
Previous:From: Tom LaneDate: 2012-04-07 14:54:22
Subject: Re: my float function returns NaN

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group