Rasterio: Suggestion: conversion to PostGIS raster

[mangecoeur]

Currently, it seems the recommended way to get raster data into PostGIS is through the raster2pgsql utility, which means that if you already have raster data in rasterio and you want to get it into PostGIS, you have to save it to a file and re-load it using a CLI tool. This is a bit silly considering that raster2pgsql is already a python script that basically marshals numpy arrays to a suitable format.

I suggest incorporating parts of raster2pgsql into rasterio, adding a function that produces data in a format suitable for loading to Postgis, stopping short of adding any SQL functionality since that is well supported by other tools. Something like:

data= rasterio.io.to_pgsql_binary(raster_data)
# Setup connection, cursor with psycopg2
cur.execute("INSERT INTO test VALUES (%s)", (psycopg2.Binary(data),))

[mangecoeur]

For reference (and maybe as a simpler solution could be added to docs), it's possible to hack this using postgis st_fromgdalraster and in-memory file to which you write a geotiff:

with io.BytesIO() as membytes, psycopg2.connect(POSTGRES_URI) as conn:
    with rasterio.open(membytes, 'w', 
                                         driver='GTiff', 
                                         height=data.shape[0],
                                         width=data.shape[1],
                                         crs=crs,
                                         transform=affine,
                                         count=1,
                                         dtype=data.dtype
                                        ) as dataset:
        dataset.write(data, 1)
    with conn.cursor() as cur:
        cur.execute("INSERT INTO test VALUES (FromGDALRaster(%s))",
                    (psycopg2.Binary(membytes.getvalue()),))

[sgillies]

@mangecoeur that's a nice example! Using rasterio's io.MemoryFile class will let you avoid an extra copy of the data:

from rasterio.io import MemoryFile

with MemoryFile() as memfile, psycopg2.connect(POSTGRES_URI) as conn:

    with memfile.open(membytes, 'w', **kwargs) as dataset:
        dataset.write(data, 1)

    with conn.cursor() as cur:
        cur.execute("INSERT INTO test VALUES (FromGDALRaster(%s))",
                    (psycopg2.Binary(memfile.read()),))

I won't add a PostGIS specific example to the docs. It's another variant of the existing examples.

[mangecoeur]

Thanks - I didn't understand from the docs that MemoryFile would accept all the arguments of rasterio.open . I would suggest at least mentioning somewhere that MemoryFile can be used for postgis upload (e.g. somewhere including the line cur.execute("INSERT INTO test VALUES (FromGDALRaster(%s))", (psycopg2.Binary(memfile.read()),)) ), because it's not very obvious.