When you read through the PostGIS documentation carefully, you might find a really short section on using psql to export rasters. Basically, psql does not provide the functionality to easily output binary data, so the approach here is a bit hackish, and relies on the large object support in PostgreSQL (it is worth noting, though, that large object support is considered obsolete in the PgSQL documentation). The steps required to export a raster this way are as follows:
- Create a large object
- Output the raster data as bytea
- Open the large object and write the output bytea
- Export the large object
- Unlink it to clean the resources
First, let's make this work in interactive psql mode. Type the following in psql, once connected to our mastering_postgis database:
select loid, lowrite(lo_open(loid, 131072), gtiff) as bytesize from ( select lo_create(0) as loid, ST_AsGDALRaster(rast, 'GTiff') as gtiff from ( select ST_Union(rast) as rast...