I'm attempting to intersect Australian Census boundary polygons with the 1" SRTM DEM (10.5bn points across Australia), and output the result as a single raster image on the fly.
The DEM is stored in Postgres 9.2 x64 (with PostGIS 2.0) as 600x600 tiles with no overlap (i.e it's a mosaic)
I've hit the known performance issue in PostGIS 2.0 using ST_Union to merge the intersecting full and partial raster tiles into a single record.
For example: intersecting a 2,500 sq km polygon takes < 0.5s, which is excellent. But merging the resulting 18 rows into a single raster takes ~2 minutes.
Is there any other function in PostGIS 2.0 to allow for tiles to be merged without having to do any comparison between the overlapping pixels (which don't exist here as it's a mosaiced dataset)?
Plan B is to use gdal_merge in Python, but I'd prefer to keep this in the database for operational reasons.
I've tried "ST_FasterUnion", thanks to this excellent post by Pierre Racine, but haven't experienced a significant improvement in performance
I'm aware of ST_Union performance improvements coming in PostGIS 2.1, but don't have a spare dev environment to deploy and test.
Postgres has already been optimised for my machine and has plenty of RAM and buffers to utilise.