rt_raster_to_gdal: Could not load the output GDAL driver
As for the first error with ST_AsTIFF, you need to enable your GDAL drivers, which by default are not enabled for PostGIS 2.1. See the manual on ways to do this. For instance, I have an environment variable set up on a Windows computer with:
POSTGIS_GDAL_ENABLED_DRIVERS=GTiff PNG JPEG GIF XYZ DTED USGSDEM AAIGrid
which can be confirmed with PostGIS with:
SELECT short_name, long_name
FROM ST_GDALDrivers();
PostGIS to Numpy
You can export the output to a virtual memory GeoTIFF file for GDAL to read into a Numpy array. For hints on virtual files used in GDAL, see this blog post.
import os
import psycopg2
from osgeo import gdal
# Adjust this to connect to a PostGIS database
conn = psycopg2.connect(...)
curs = conn.cursor()
# Make a dummy table with raster data
curs.execute("""\
SELECT ST_AsRaster(ST_Buffer(ST_Point(1, 5), 10), 10, 10, '8BUI', 1) AS rast
INTO TEMP mytable;
""")
# Use a virtual memory file, which is named like this
vsipath = '/vsimem/from_postgis'
# Download raster data into Python as GeoTIFF, and make a virtual file for GDAL
curs.execute("SELECT ST_AsGDALRaster(rast, 'GTiff') FROM mytable;")
gdal.FileFromMemBuffer(vsipath, bytes(curs.fetchone()[0]))
# Read first band of raster with GDAL
ds = gdal.Open(vsipath)
band = ds.GetRasterBand(1)
arr = band.ReadAsArray()
# Close and clean up virtual memory file
ds = band = None
gdal.Unlink(vsipath)
print(arr) # this is a 2D numpy array
Shows a rasterised buffered point.
[[0 0 0 1 1 1 1 0 0 0]
[0 1 1 1 1 1 1 1 1 0]
[0 1 1 1 1 1 1 1 1 0]
[1 1 1 1 1 1 1 1 1 1]
[1 1 1 1 1 1 1 1 1 1]
[1 1 1 1 1 1 1 1 1 1]
[1 1 1 1 1 1 1 1 1 1]
[0 1 1 1 1 1 1 1 1 0]
[0 1 1 1 1 1 1 1 1 0]
[0 0 0 1 1 1 1 0 0 0]]
Note that I used a 'GTiff' format in the example, but other formats might be better suited. For instance, if you have a large raster that needs to be transferred across a slow internet connection, try using 'PNG' to compress it.