Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Loads raster data from POSTGIS directly to numpy.array using rasterio
import psycopg2
from rasterio.io import MemoryFile
conn = pscopg2.connect("<connection string>")
cur = conn.cursor()
# ensure that the GTiff driver is available,
# see https://postgis.net/docs/postgis_gdal_enabled_drivers.html
cur.execute('''
SET postgis.gdal_enabled_drivers TO 'GTiff';
SELECT ST_AsGDALRaster(rast, 'GTiff') FROM raster.table;
''')
for row in cur:
rast = row[0].tobytes()
with MemoryFile(rast).open() as dataset:
data_array = dataset.read()
print(data_array)
@fralc
Copy link

fralc commented Jun 5, 2020

Thanks for this snippet.
In order to retrieve a tiled raster as a whole I used ST_Union:

cur.execute('''
  SET postgis.gdal_enabled_drivers TO 'GTiff';
  WITH temp as (
    SELECT ST_Union(rast,1) As file_rast
    FROM raster.table
  )
  SELECT ST_AsGDALRaster(file_rast, 'GTiff') FROM temp;
 ''')

@ThomasG77
Copy link

ThomasG77 commented May 14, 2021

FYI, there is a typo e.g

conn = pscopg2.connect("<connection string>")

should be

conn = psycopg2.connect("<connection string>")

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment