From: Michael Fuhr <mike@fuhr.org>
To: George Pavlov <gpavlov@mynewplace.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: avg() with floating-point types
Date: Sun, 1 Jan 2006 18:27:41 -0700
Message-ID: <20060102012741.GA70122@winnie.fuhr.org> (raw)
In-Reply-To: <CCB89282FCE1024EA3DCE687A96A516403AB93C2@ehost010-6.exch010.intermedia.net>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, gpavlov@mynewplace.com
Subject: Re: avg() with floating-point types
In-Reply-To: <20060102012741.GA70122@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox