2

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.

0100000100000000000000D03F000000000000D0BF0000000000804140000000000000F43F00000000000000000000000000000000E6100000010001004A003C1CC600000000

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.

3

You can query for TIFF data directly:

The query:

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

|improve this answer|||||
  • 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
2

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)
 cur.execute(sql)
 data = cur.fetchall()
 actual_data = data[0][0] #This can passed as the data variable in the requests.put function
|improve this answer|||||

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.