如何在Python中快速获取PostGIS海量数据集的Geometry类型数据

问题背景 #

我在PostGIS中有一张包含约400万条记录的表，原Python脚本通过ST_AsText将几何列转换为WKT字符串读取，得到的geometry列是object类型，后续转换为Geometry类型耗时超1小时。

原读取代码：

import pandas.io.sql as psql
import psycopg2
from shapely import wkt
import geopandas

query = "SELECT id, ST_AsText(geom) as geometry FROM <table>"

def postgresql_conn(user, postgres_password, host, port, database):
    connection = psycopg2.connect(user=user, password=postgres_password, host=host, port=port, database=database)
    return connection

conn = postgresql_conn(user, postgres_password, host, port, postgresql_database)
df = psql.read_sql(query, conn)
print(df.dtypes)

原转换代码：

df['geometry'] = geopandas.GeoSeries.from_wkt(df['geometry'])
df= geopandas.GeoDataFrame(df, geometry='geometry')

1. 直接从数据库读取Geometry类型（最优方案） #

不要用ST_AsText转换为WKT文本格式，改用geopandas.read_postgis方法，它会直接处理PostGIS原生几何类型（通过二进制EWKB传输，比文本格式快数倍），无需后续转换。

修改后的代码：

import psycopg2
import geopandas

def postgresql_conn(user, postgres_password, host, port, database):
    return psycopg2.connect(
        user=user, 
        password=postgres_password, 
        host=host, 
        port=port, 
        database=database
    )

# 直接查询原生geom列，无需转换为WKT
query = "SELECT id, geom FROM <table>"

conn = postgresql_conn(user, postgres_password, host, port, postgresql_database)
# 直接读取为GeoDataFrame，geometry列自动识别为Geometry类型
gdf = geopandas.read_postgis(query, conn, geom_col='geom')

print(gdf.dtypes)

若需自定义几何列名，可在查询中重命名后指定：

SELECT id, geom AS geometry FROM <table>

对应修改read_postgis参数：geopandas.read_postgis(query, conn, geom_col='geometry')

2. 高效批量转换WKT的备用方案 #

若因特殊原因必须使用WKT格式，可通过分块读取+分块转换减少内存占用、提升速度：

import pandas as pd
import pandas.io.sql as psql
import psycopg2
import geopandas

query = "SELECT id, ST_AsText(geom) as geometry FROM <table>"

def postgresql_conn(user, postgres_password, host, port, database):
    return psycopg2.connect(
        user=user, 
        password=postgres_password, 
        host=host, 
        port=port, 
        database=database
    )

conn = postgresql_conn(user, postgres_password, host, port, postgresql_database)

# 设置分块大小，每次读取10万条记录
chunk_size = 100000
chunks = []

for chunk in psql.read_sql(query, conn, chunksize=chunk_size):
    # 对单个分块进行WKT转换
    chunk['geometry'] = geopandas.GeoSeries.from_wkt(chunk['geometry'])
    chunks.append(chunk)

# 合并所有分块为最终GeoDataFrame
gdf = geopandas.GeoDataFrame(pd.concat(chunks), geometry='geometry')
print(gdf.dtypes)

分块处理避免一次性加载400万条数据到内存，降低资源消耗的同时提升转换效率。

内容的提问来源于stack exchange，提问作者Manish Shegokar