While working with a DB of about 135,000 rasters my co-worker Frank and I discovered that despite coming from a reference grid, some of the rasters weren’t properly aligned. Although we had a reference shapefile containing the correct tile positions, PostGIS doesn’t have an easy way to snap to a reference geometry. So instead Frank used
ST_SnapToGrid and the upper left corner of the first tile as the reference. This worked great, all of the rasters are now properly aligned, and unionizing any of the rasters returns the correct result.
However some of the rasters in the DB contain only null values – this is because we cut up the original tiles into small pieces to optimize intersections (i.e. finding which tile a geometry belongs to). There is no good reason to store these rasters and detecting that a raster contains only null values is pretty straightforward with
ST_BandIsNoData. In our case we decided to drop the empty rasters after we corrected the alignment. This had an unintended consequence.
ST_BandIsNoData function does not actually check whether the band contains only no data values, instead it checks a flag. This is a good thing because, especially for large rasters, it saves the time of looking at each pixel value. But this flag can also be wrong!
In our case
ST_SnapToGrid set the BandIsNoData flag to false for every raster. Since we already knew some rasters contains only null values, it was easy for us to spot this (there wasn’t anything to drop from the table). Fortunately PostGIS lets you explicitly check the values (
ST_BandIsNoData(raster, true)) and you can them update them (
ST_SetBandIsNoData). Once you know what to look for the problem is easy to solve but it is unfortunate that PostGIS did not respect the BandIsNoData flags.