Re: avg() with floating-point types

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