Python中无法导入ZSTD压缩GeoTIFF至PostGIS的问题求助

我最近在尝试用Python把ZSTD压缩的GeoTIFF导入PostGIS时遇到了报错，想请大家帮忙看看问题出在哪：

问题描述 #

• 未压缩的GeoTIFF可以正常导入，但导入ZSTD压缩的版本时，会抛出错误：RASTER_fromGDALRaster: Could not open bytea with GDAL. Check that the bytea is of a GDAL supported format
• 我不确定是GDAL不支持ZSTD压缩的GeoTIFF，还是我的代码逻辑有问题？

我的代码 #

import psycopg2
from rasterio.io import MemoryFile

db_params = {
    "dbname": "test_db",
    "user": "postgres",
    "password": "demo",
    "host": "localhost",
    "port": "5432"
}
table_name = "species_UZ"
raster_column_name = 'species'

try:
    conn = psycopg2.connect(**db_params)
    cursor = conn.cursor()

    file_path='my_raster.tif'
    with rasterio.open(file_path) as src:
        raster_data = src.read()
        profile = src.profile

        # Convert to PostGIS-compatible format (e.g., GeoTIFF in memory)
        with MemoryFile() as memfile:
            with memfile.open(**profile) as dst:
                dst.write(raster_data)
            gdal_raster_bytes = memfile.read()

    create_table_sql = f"""
    SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';
    CREATE TABLE IF NOT EXISTS {table_name} (
        id SERIAL PRIMARY KEY,
        {raster_column_name} RASTER
    );
    """
    cursor.execute(create_table_sql)

    insert_sql = f"INSERT INTO {table_name} ({raster_column_name}) VALUES (ST_FromGDALRaster(%s));"
    cursor.execute(insert_sql, (gdal_raster_bytes,))

    conn.commit()
    print(f"Raster successfully imported into {table_name}.")

except Exception as e:
    print(f"Error: {e}")
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

问题分析与解决方案 #

首先明确：GDAL是支持ZSTD压缩GeoTIFF的，但你的代码里有几个关键细节没处理好，导致PostGIS的GDAL无法识别字节流。下面是调整方案：

1. 单独启用GDAL驱动，确保配置生效

你原来把SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';和CREATE TABLE放在同一个SQL语句里，可能导致驱动启用的配置没有在会话中生效。需要单独执行这个设置并提交：

# 先启用所有GDAL驱动，单独执行并提交
cursor.execute("SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';")
conn.commit()

2. 避免不必要的MemoryFile重写，保留原始压缩信息

你用rasterio读取后再写入MemoryFile的步骤是多余的，这个过程可能会丢失ZSTD的压缩参数，或者生成的字节流不符合PostGIS GDAL的预期格式。直接读取原文件的二进制内容，或者用rasterio的read_bytes()方法获取原始GDAL兼容字节流即可：

# 方案1：直接读取原文件二进制
with open(file_path, 'rb') as f:
    gdal_raster_bytes = f.read()

# 方案2：如果需要用rasterio预处理，用read_bytes()获取原始字节流
# with rasterio.open(file_path) as src:
#     gdal_raster_bytes = src.read_bytes()

3. 修正后的完整代码

import psycopg2
import rasterio

db_params = {
    "dbname": "test_db",
    "user": "postgres",
    "password": "demo",
    "host": "localhost",
    "port": "5432"
}
table_name = "species_UZ"
raster_column_name = 'species'

try:
    conn = psycopg2.connect(**db_params)
    cursor = conn.cursor()

    # 单独启用GDAL所有驱动，确保会话生效
    cursor.execute("SET postgis.gdal_enabled_drivers = 'ENABLE_ALL';")
    conn.commit()

    file_path='my_raster.tif'
    # 直接读取原文件二进制，保留完整压缩信息
    with open(file_path, 'rb') as f:
        gdal_raster_bytes = f.read()

    # 创建表（无需重复设置驱动）
    create_table_sql = f"""
    CREATE TABLE IF NOT EXISTS {table_name} (
        id SERIAL PRIMARY KEY,
        {raster_column_name} RASTER
    );
    """
    cursor.execute(create_table_sql)

    # 插入数据
    insert_sql = f"INSERT INTO {table_name} ({raster_column_name}) VALUES (ST_FromGDALRaster(%s));"
    cursor.execute(insert_sql, (gdal_raster_bytes,))

    conn.commit()
    print(f"Raster successfully imported into {table_name}.")

except Exception as e:
    print(f"Error: {e}")
    # 出错时回滚事务
    if conn:
        conn.rollback()
finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

额外检查项

• 确认版本兼容性：PostGIS 3.0+ 搭配 GDAL 3.1+ 才支持ZSTD压缩GeoTIFF，检查你的PostGIS和GDAL版本是否达标；
• 验证原文件有效性：用gdalinfo my_raster.tif命令行工具查看文件，如果GDAL能正常解析，说明文件本身没问题。

内容来源于stack exchange