Outputting raster using psql

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