From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | George Pavlov <gpavlov(at)mynewplace(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: avg() with floating-point types |
Date: | 2006-01-02 01:27:41 |
Message-ID: | 20060102012741.GA70122@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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:
If you look around you should be able to find shapefiles with points
or polygons for cities and postal codes.
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Splivalo | 2006-01-02 13:26:14 | PostgreSQL and uuid/guid |
Previous Message | Tom Lane | 2006-01-02 01:25:50 | Re: avg() with floating-point types |