[postgis-users] comparing two queries, please feedback if you know the answer

[Robert W. Burgholzer]

List,

This is not an urgent problem, just a bit of curiosity re: sql. Answer
if you feel like it.

I am trying to do a weighted average of rainfall values from one layer (
a thiessen polygon layer with precip values) to a second layer (a soil
layer). When I do this query:

update soillayer1 set r =
round(sum(area2d(intersection(rainlayer.the_geom,soillayer1.the_geom))*rainlayer.r/soillayer1.area),4)
where rainlayer.the_geom && soillayer1.the_geom;

it gives erroneous values, while if I do this:

update soillayer1 set r = (select
round(sum(area2d(intersection(rainlayer.the_geom,soillayer1.the_geom))*rainlayer.r/soillayer1.area),4)
as precip where rainlayer.the_geom && soillayer1.the_geom group by
soillayer1.gid);

It works like a charm, and is actually a tad bit faster. Does anyone
know why this would be??

Thanks,

Rob

-- 
Robert Burgholzer
al·go·rithm  n. 
       A step-by-step problem-solving procedure, especially an
established, recursive computational
       procedure for solving a problem in a finite number of steps.
Invented by Al Gore.
rburghol at vt.edu
http://www.soulswimmer.net/