Waiting for PostGIS 2.1: Easier Multi-band Raster Union syntax

Things change quickly in PostGIS land. Bborie Park has been really hard at work beefing up the PostGIS raster functionality. Just today he committed a change that makes multi-band union raster much easier to write. Recall in last example, Faster Raster Union. I showed this lengthy syntax to get back a mulit-band raster after unioning a multiband raster. This is because in prior versions, the unadorned: ST_Union(rast) was really short-hand for ST_Union(rast,1) or ST_Union(rast,1,'LAST'). This has always driven me nuts. Well today Bborie changed the behavior to be more intuitive. It's a breaking change if you have more than one band in your raster, but honestly I think a behavior people naively assumed anyway. So ST_Union(rast), now means, if you have more than one band in your raster, all your bands will be in the union operation.

So in short, to get: , we can simply write this query:

SELECT ST_Union(rast)
FROM (SELECT  ST_Clip(rast , geom) As rast
	FROM test_100_100 
           CROSS JOIN 
     ST_Buffer(ST_GeomFromText('POINT(231234.436173996 887025.024778253)',26986)
    ,50, 'quad_segs=2') As geom
WHERE ST_Intersects(rast,  geom ) ) As foo

The LAST, FIRST, etc. band number stuff is still useful for many use cases. I'll try to explain that in subsequent articles. They really come into play when we start talking about overlapping raster tiles and then you might also want to combine these with the ORDERED AGG feature supported in PostgreSQL 9.0+ to better control the order of raster tile processing.

Again this feature is available in latest windows experimental buildbot builds. To use just copy over same named folders into your EnterpriseDb Windows install and CREATE EXTENSION postgis VERSION "2.1.0SVN";. Copying over all the files will make 2.1.0SVN the default so CREATE EXTENSION postgis; will do the same. I advice, if you want to work with both 2.0.1 and 2.1.0SVN, you should explicitly specify the version in your CREATE EXTENSION statements so you know what you are getting. Regardless though your geos wil be upgrade to 3.4.0dev (which seems to work fine for me in production) for both 2.0.1 and 2.1.0SVN so you may just want to use on a testing instance.