Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)?

Notes:

  • 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.

share|improve this question
add comment

1 Answer

Sadly, no. Not in 2.0. 2.1 should provide significant performance improvement over 2.0.

No matter what you attempt in the database with 2.0, you are limited by the number of times the raster objects are serialized/deserialized.

PostGIS 2.1 should be going beta in the next week or two (hopefully sooner than later).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.