I am able to retrieve raw raster data from a PostGIS raster in a PostgreSQL database using SQL Alchmey. I can get the raw data directly from the database. Now I would like to create a geoTIFF file from the raw data. Here is the raw raster data from the database.


enter image description here

The above image is a screenshot of the database table that I am accessing through SQL Alchemy. Is there a way that I can create a geotiff from this raw data using gdal in python? I have looked for a similar workflow, but couldn't find any examples on creating a tiff directly from raw raster data.


You can query for TIFF data directly:

The query:

    ST_AsTIFF(ST_Union(rast), 'LZW') as tiff,
FROM {dataset_name}
WHERE ST_Intersects(ST_SetSRID(ST_GeomFromText('{region_string}'), '4326'), rast)

Then you can use GDAL or RasterIO to write it to a file.

(You may even be able to just dump the binary direct to a file.)

  • Ah, sorry. That's a WKT representation of an area. This example gets all the raster chunks that intersect a polygon. (Hence the need for ST_Union) – Alex Leith Jul 14 '17 at 1:12

Thank you Alex for pointing me in the right direction. But I did not have to clip the raster. I am trying to upload the data from postgis database to geotiff using the requests module. For that purpose the following code did the trick.

 import psycopg2
 sql = """SELECT ST_AsGDALRaster(rast, 'GTiff') as tiff FROM {0}.{1} WHERE id = {2}""".format(schema,table,date)
 data = cur.fetchall()
 actual_data = data[0][0] #This can passed as the data variable in the requests.put function

I am going to provide a separate answer because although it may seem obvious to some familiar with PostGIS, GDAL, and rasterio libraries, I had a heck of a time actually getting the raster loaded in to my python environment.

From start to finish, here's what works for me:

import sqlalchemy
import rasterio
from rasterio.io import MemoryFile
url = sqlalchemy.engine.url.URL(drivername='postgresql+psycopg2',
engine = sqlalchemy.create_engine(url)
sql = ("SELECT ST_AsGDALRaster(rast, 'GTiff') AS tiff "
       "{0}.{1} WHERE rid = 1".format('public', 'raster_name')
with engine.connect() as cnxn:
    result = cnxn.execute(sql)
with MemoryFile(result.fetchall()[0][0].tobytes()) as memfile:
    ds = memfile.open()


{'driver': 'GTiff',
 'dtype': 'uint16',
 'nodata': 0.0,
 'width': 40,
 'height': 40,
 'count': 9,
 'crs': CRS.from_epsg(32613),
 'transform': Affine(20.0, 0.0, 697920.0, 0.0, -20.0, 3984120.0)}

And to save file to current directory:

with rasterio.open('output.tif', 'w', **ds.profile) as dst:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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