1

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.

|improve this question
  • What is your ultimate goal with unioning? If it's to do something like get the sum of pixel values, then doing ST_Union is not the best way. ST_Union will collapse overlapping pixels, taking the last pixel value for overlapping pixels is the default behavior – LR1234567 Nov 10 '16 at 21:36
  • What about ST_Union(rast, 'SUM')? – Pierre Racine Nov 10 '16 at 21:48
  • Sorry, I meant to write ST_Union(rast, 'SUM'). Will correct in example. – AJSmyth Nov 10 '16 at 23:36
  • What if you start small just trying to ST_Union() a subset of them adding a WHERE clause on some column? – Pierre Racine Nov 11 '16 at 14:09
  • I was successful using a where clause to do a small subset. I also discovered I had some really huge rasters in the mix by mistake. Filtering those out got me a little further. Still not working on the full dataset, though. I ahve read that ST_union works in O(n^2) time, which suggests it just won't work well on hundreds of thousands of rasters. – AJSmyth Nov 15 '16 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.