I have city and postal_code tables linked by city_postal_code through a
city_id and postal_code_id. The postal_codes have latitude/longitude,
the cities don't. I want to set the city lat/long to the average of the
associated postal codes (abstract for a minute on whether that actually
makes sense from a geographical perspective), so I have a statement:
update city set latitude = city2.lat from (select c.city_id, avg(pc.latitude) as lat from city c left join
city_postal_codecpc using (city_id) left join postal_code pc using (postal_code_id) group by c.city_id) city2
where city2.city_id = city.city_id
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:
select c.latitude, avg(pc.latitude), c.latitude-avg(pc.latitude) as diff from city c left join city_postal_code cpc
using(city_id) left join postal_code pc using (postal_code_id) group by c.city_id,c.latitude having
avg(pc.latitude)!= c.latitude
latitude | avg | diff
-----------------+---------------------+---------------------36.709374333333 | 36.7093743333333333 |
-0.000000000000333341.078385733333| 41.0783857333333333 | -0.000000000000333331.576437888889 | 31.5764378888888889 |
0.000000000000111142.666669666667| 42.6666696666666667 | 0.000000000000333335.104581166667 | 35.1045811666666667 |
0.000000000000333331.263006142857| 31.2630061428571429 | -0.000000000000142938.805648772727 | 38.8056487727272727 |
-0.0000000000002727
...
An additional question -- is the UPDATE above written as cleanly as
possible (I am not very confident on my understanding of UPDATE-SET-FROM
syntax)?
