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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
