oh SQL you crazy nutjob

crazysql.sql

SELECT intersects.band, (CASE WHEN within.avg IS NULL OR within.area IS NULL THEN intersects.avg ELSE (((intersects.avg * intersects.area)   (within.avg * within.area)) / (intersects.area   within.area)) END) AS total FROM ( SELECT band, avg((ST_SummaryStats(ST_AsRaster((intersection).geom, scalex, scaley, NULL, NULL, ARRAY['32BSI'], ARRAY[(intersection).val]))).mean), SUM(area) AS area FROM ( SELECT band, (ST_Intersection(the_geom, rast, band)) AS intersection, ST_ScaleX(rast) AS scalex, ST_ScaleY(rast) AS scaley, ST_Area(the_geom) AS area FROM forest_intact, ( SELECT ST_GeomFromText('MULTIPOLYGON(((18.511962890625 5.386335689520536,14.864501953125 2.141834969768584,18.775634765625 0.0769042737833478,22.510986328125 -0.5383221578577078,26.114501953125 0.8239462091017685,26.553955078125 3.3269862108134998,22.423095703125 6.085935520826564,18.511962890625 5.386335689520536)))',4326) AS the_geom) foo, ( SELECT 1 AS band UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) bar WHERE ST_Intersects(rast, the_geom, band) AND ST_Within(rast, the_geom) = false) bar GROUP BY band) intersects LEFT JOIN ( SELECT band, avg((ST_SummaryStats(ST_AsRaster((intersection).geom, scalex, scaley, NULL, NULL, ARRAY['32BSI'], ARRAY[(intersection).val]))).mean), SUM(area) AS area FROM ( SELECT band, (ST_Intersection(the_geom, rast, band)) AS intersection, ST_ScaleX(rast) AS scalex, ST_ScaleY(rast) AS scaley, ST_Area(rast) AS area FROM forest_intact, ( SELECT ST_GeomFromText('MULTIPOLYGON(((18.511962890625 5.386335689520536,14.864501953125 2.141834969768584,18.775634765625 0.0769042737833478,22.510986328125 -0.5383221578577078,26.114501953125 0.8239462091017685,26.553955078125 3.3269862108134998,22.423095703125 6.085935520826564,18.511962890625 5.386335689520536)))',4326) AS the_geom) foo, ( SELECT 1 AS band UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) bar WHERE ST_Within(rast, the_geom)) foo GROUP BY band) within ON intersects.band = within.band