PostGIS’s new raster support has been incredibly useful to me, but problems can be hard to debug. One of my favorite techniques is saving out intermediate images so I can see what’s going on, so I sat down and tried to figure out how to do that in PostGres. I found some handy functions like ST_AsPng(), ST_AsJpeg() and ST_AsTiff() to convert rasters to bitmap images in memory, but was surprised that none of the examples showed how to save the results as files from the pgsql client. As it turns out, that’s because it’s very awkward to do! I spent some time Googling, and managed to piece together a solution from various different sources, so here’s a trail of breadcrumbs for anyone else hitting the same problem.

The key problem is that the postgres client has no way to write out plain-old untouched binary data. You might think that the BINARY modifier to COPY would do the trick, but that actually writes out a header and other fields as well as the data we want. It turns out that your best bet is to encode the data as hexadecimal text, and then use an external program to convert it back to the raw binary data you want. Here’s the recipe:

First, in the psql client run this (with the table and file names changed as appropriate):

COPY (SELECT encode(ST_AsPNG(raster), ‘hex’) AS png FROM table_with_raster) TO ‘/tmp/myimage.hex’;

If you cat the /tmp/myimage.hex file, you’ll see a stream of hex numbers in text, like this: “89504e470d0a1a0a0000…“. You’ll now need to open up a normal unix terminal and run the xxd command to stitch the text back into a binary file:

xxd -p -r /tmp/sf_colors.hex > /tmp/sf_colors.png

Voila! You now have a file representing your raster, so you can actually see what’s going on with your data. Any suggestions on how to improve this are very welcome in the comments section, it doesn’t seem like it should be this hard.