generic areal apportionment of average SQL script

areal average.sql

/*
	generic areal apportionment of average
*/

--table1.id
--table1.geom
--table1.value

--table2.geom
--table2.weight

WITH dat AS ( 
	SELECT
		table1.id,
		-- sum( value * weight * intersection )
		SUM( table2.value * table2.weight * ST_Area(ST_Intersection(table1.geom,table2.geom)) )
		-- / sum( intersection * weight )
		/
		SUM( ST_Area(ST_Intersection(table1.geom,table2.geom)) * table2.weight ) AS newval
	FROM table1 JOIN table2 ON ST_Intersects(table1.geom,table2.geom)
	-- exclude missing data
	--WHERE table2.value != -999 AND table2.weight != -999
	GROUP BY table1.id
)
UPDATE table1 SET value = dat.newval
FROM dat WHERE dat.id = table1.id