One of the features coming in PostgreSQL 9.5 is the triumvirate GROUPING SETS, CUBE, and ROLLUP nicely covered in Bruce's recent slide deck. The neatest thing about PostgreSQL development is that when improvements happen, they don't just affect the core, but can be taken advantage of by extensions, without even lifting a finger. Such is the case with these features.
One of the things I was curious about with these new set of predicates is Would they work with any aggregate function?. I assumed they would, so decided to put it to the test, by using it with PostGIS ST_Union function (using PostGIS 2.2.0 development). This feature was not something the PostGIS Development group planned on supporting, but by the magic of PostgreSQL, PostGIS accidentally supports it. The grouping sets feature is particularly useful if you want to aggregate data multiple times, perhaps for display using the same dataset. It allows you to do it with a single query that in other PostgreSQL versions would require a UNION query. This is a rather boring example but hopefully you get the idea.
Example of GROUPING SETS with ST_Union spatial aggregate
SELECT COALESCE('County ' || countyfp, 'State ' || statefp) As region,
ST_Union(the_geom) As geom, count(*) AS cnt
FROM cousub
GROUP BY GROUPING SETS (countyfp, statefp)
ORDER BY countyfp, statefp;
Output looks like:
region | geom | count
------------+-----------------------------------+-------
County 001 | 0103000020AD10000001000000EB00... | 3
County 003 | 0103000020AD10000001000000B307... | 5
County 005 | 0103000020AD100000010000005902... | 7
County 007 | 0103000020AD100000010000006F07... | 16
County 009 | 0106000020AD100000020000000103... | 9
State 44 | 0106000020AD100000020000000103... | 40
(6 rows)
Note in the example, I use COALESCE for labeling because in the GROUPING SETS will force only one of the columns to be filled in that is for that group and the rest will be NULL.
PostGIS in Action
About the Authors
Consulting
