I'm having some difficulty finding the sum of a large number of rasters in PostGIS. Basically I have nearly 1 million polygons which I converted to rasters in a PostGIS table. I now want to overlay all of them and create a single output raster which is the sum of all their pixel weights.
I have tried using something like
SELECT ST_Union(rast, 'SUM') FROM public.my_raster_table
This seemed to work to work well on a small data set, but the pixel values in the output were not what I expected. Many hundreds of these rasters overlap, and so I expected the range of pixels to be around 0-500. Instead they only ranged from 1-6. In other words, if seems that if you try to sum more than a handful of rasters with this method, data is lost.
When I tried the method on the full set of data, it ran extremely fast (<2 seconds), which seems impossible for adding nearly 1 million rasters. The output appeared to be completely flat; i.e., pixel values ranged from NaN to 1. What is going on?
Update: Apparently some of the rasters I was using were unexpectedly huge (e.g., entire western half of United states at a pixel size of ~1/3 mi. It is clear that these are what was causing the server disconnect, though it's still unclear why. I'm also not sure what to do about it, except to filter out the really gigantic ones.