Date: Sun, 1 Jan 2006 18:27:41 -0700
From: Michael Fuhr <mike@fuhr.org>
To: George Pavlov <gpavlov@mynewplace.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: avg() with floating-point types
Message-ID: <20060102012741.GA70122@winnie.fuhr.org>
References: 
 <CCB89282FCE1024EA3DCE687A96A516403AB93C2@ehost010-6.exch010.intermedia.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CCB89282FCE1024EA3DCE687A96A516403AB93C2@ehost010-6.exch010.intermedia.net>

On Sun, Jan 01, 2006 at 04:40:18PM -0800, George Pavlov wrote:
> The datatype of both city.latitude and postal_code.latitude is
> number(16,12).
> 
> This works, but I would like to understand why there is sometimes a
> discrepancy between avg(pc.latitude) and what actually gets inserted
> into the city table -- is it the usual floating-point discrepancy or is
> there something I can do about it? E.g. after the above update:

You've declared the numeric column as (16,12) so the inserted values
are rounded to the 12th decimal place.  Notice that that's where the
values start to differ:

>     latitude     |         avg         |        diff         
> -----------------+---------------------+---------------------
>  36.709374333333 | 36.7093743333333333 | -0.0000000000003333

Is such a difference significant to your application?  The distance
discrepancy is on the order of tens of nanometers, which seems
absurdly precise.  With lat/lon coordinates five places after the
decimal point is about meter-precision, which is probably more than
adequate for whatever you're doing.

Incidentally, if you're doing anything geospatial then you might
want to check out PostGIS:

http://www.postgis.org/

If you look around you should be able to find shapefiles with points
or polygons for cities and postal codes.

-- 
Michael Fuhr