如何高效实现数据库IO操作，提升数据读写效率？

第9章：数据读写——数据库 IO 在企业级 GIS 应用和大规模空间数据管理中，空间数据库是不可或缺的基础设施。GeoPandas 提供了与 PostGIS、Spatialite 等空间数据库的无缝集成，支持直接从数据库读取空间数据，也支持将处理结果写回数据库。本章将详细介绍 GeoPandas 的数据库 IO 功能。 9.1 空间数据库概述 9.1.1 什么是空间数据库 空间数据库是在传统关系型数据库的基础上，添加了空间数据类型和空间查询功能的数据库系统。它能够存储、索引和查询地理空间数据。 9.1.2 常见空间数据库 数据库 说明 空间扩展 开源 PostgreSQL + PostGIS 最强大的开源空间数据库 PostGIS ✅ SQLite + Spatialite 轻量级嵌入式空间数据库 SpatiaLite ✅ MySQL 内置基本空间功能 内置 ✅ Oracle Spatial 企业级空间数据库 Oracle Spatial ❌ SQL Server 微软空间数据库 内置 ❌ DuckDB + Spatial 新兴的分析型空间数据库 Spatial 扩展 ✅ 9.1.3 PostGIS 简介 PostGIS 是 PostgreSQL 数据库的空间扩展，是目前最功能完善、使用最广泛的开源空间数据库。 PostGIS 的核心能力： 存储和索引各种几何类型（点、线、面、多几何等） 空间关系查询（包含、相交、邻近等） 空间分析函数（缓冲区、叠加、最近邻等） 坐标系转换 拓扑分析 栅格数据支持 3D 和 4D 几何支持 -- PostGIS 基本操作示例 -- 创建空间表 CREATE TABLE cities ( id SERIAL PRIMARY KEY, name VARCHAR(100), population INTEGER, geom GEOMETRY(Point, 4326) ); -- 创建空间索引 CREATE INDEX idx_cities_geom ON cities USING GIST (geom); -- 插入数据 INSERT INTO cities (name, population, geom) VALUES ('北京', 21710000, ST_SetSRID(ST_MakePoint(116.407, 39.904), 4326)); -- 空间查询 SELECT name, population FROM cities WHERE ST_DWithin( geom, ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326), 0.1 ); 9.1.4 Spatialite 简介 Spatialite 是 SQLite 的空间扩展，是一个轻量级的嵌入式空间数据库，无需安装服务器即可使用。 # Spatialite 的优势： # - 零配置，单文件数据库 # - 无需安装数据库服务器 # - 适合桌面应用和小型项目 # - 可用于测试和原型开发 9.2 数据库连接配置 9.2.1 SQLAlchemy Engine GeoPandas 使用 SQLAlchemy 作为数据库连接的抽象层。首先需要创建一个数据库引擎（Engine）。 from sqlalchemy import create_engine # PostgreSQL/PostGIS 连接 engine = create_engine( "postgresql://username:password@hostname:5432/database_name" ) # 连接字符串格式： # postgresql://用户名:密码@主机:端口/数据库名 9.2.2 连接字符串格式 from sqlalchemy import create_engine # 1. 基本连接 engine = create_engine("postgresql://user:pass@localhost:5432/mydb") # 2. 使用 psycopg2 驱动（明确指定） engine = create_engine("postgresql+psycopg2://user:pass@localhost:5432/mydb") # 3. 使用 psycopg（v3）驱动 engine = create_engine("postgresql+psycopg://user:pass@localhost:5432/mydb") # 4. 带额外参数的连接 engine = create_engine( "postgresql://user:pass@localhost:5432/mydb", echo=False, # 不打印 SQL 语句 pool_size=5, # 连接池大小 max_overflow=10, # 最大溢出连接数 pool_timeout=30, # 连接超时 pool_recycle=1800 # 连接回收时间（秒） ) 9.2.3 使用环境变量管理凭据 import os from sqlalchemy import create_engine # 从环境变量读取数据库配置 DB_USER = os.environ.get("DB_USER", "postgres") DB_PASS = os.environ.get("DB_PASS", "password") DB_HOST = os.environ.get("DB_HOST", "localhost") DB_PORT = os.environ.get("DB_PORT", "5432") DB_NAME = os.environ.get("DB_NAME", "gis_db") engine = create_engine( f"postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}" ) 9.2.4 使用 psycopg 直接连接 import psycopg2 # 直接使用 psycopg2 连接 conn = psycopg2.connect( host="localhost", port=5432, dbname="gis_db", user="postgres", password="password" ) # 注意：read_postgis() 也支持直接传入 psycopg2 连接 # 但推荐使用 SQLAlchemy Engine 9.2.5 SQLite/Spatialite 连接 from sqlalchemy import create_engine # SQLite 连接（无 Spatialite 扩展） engine = create_engine("sqlite:///my_database.db") # Spatialite 连接需要特殊处理 import sqlite3 def load_spatialite(dbapi_conn, connection_record): """加载 Spatialite 扩展""" dbapi_conn.enable_load_extension(True) dbapi_conn.load_extension("mod_spatialite") from sqlalchemy import event engine = create_engine("sqlite:///my_spatial_db.db") event.listen(engine, "connect", load_spatialite) 9.3 read_postgis() 详解 9.3.1 基本语法 geopandas.read_postgis( sql, # SQL 查询语句或表名 con, # 数据库连接（SQLAlchemy Engine 或连接字符串） geom_col="geom", # 几何列名称 crs=None, # 坐标参考系统 index_col=None, # 用作索引的列 coerce_float=True, # 是否将数值转为浮点数 parse_dates=None, # 日期列解析 params=None, # SQL 参数 chunksize=None # 分块读取大小 ) 9.3.2 基本读取 import geopandas as gpd from sqlalchemy import create_engine # 创建连接 engine = create_engine("postgresql://user:pass@localhost:5432/gis_db") # 方法1：读取整张表 gdf = gpd.read_postgis( "SELECT * FROM provinces", con=engine, geom_col="geom" ) print(f"读取 {len(gdf)} 条记录") # 方法2：使用表名（自动生成 SELECT *） gdf = gpd.read_postgis( "provinces", con=engine, geom_col="geom" ) 9.3.3 指定几何列 # 当表中有多个几何列时，需要明确指定 gdf = gpd.read_postgis( "SELECT id, name, geom, centroid_geom FROM regions", con=engine, geom_col="geom" # 指定使用哪个几何列 ) # 如果几何列名不是默认的 "geom" gdf = gpd.read_postgis( "SELECT * FROM my_table", con=engine, geom_col="the_geom" # 或 "geometry"、"shape" 等 ) 9.3.4 指定 CRS # 方法1：从数据库自动获取 CRS（如果表定义了 SRID） gdf = gpd.read_postgis( "SELECT * FROM provinces", con=engine, geom_col="geom" # CRS 会从 geometry_columns 表中自动获取 ) print(gdf.crs) # 自动识别 # 方法2：显式指定 CRS gdf = gpd.read_postgis( "SELECT * FROM provinces", con=engine, geom_col="geom", crs="EPSG:4326" # 显式指定 ) 9.3.5 使用 SQL 查询进行过滤 # 属性过滤 gdf = gpd.read_postgis( """ SELECT name, population, area_km2, geom FROM cities WHERE population > 5000000 ORDER BY population DESC """, con=engine, geom_col="geom" ) # 空间过滤（使用 PostGIS 函数） gdf = gpd.read_postgis( """ SELECT c.name, c.population, c.geom FROM cities c WHERE ST_Intersects( c.geom, ST_MakeEnvelope(115, 39, 117, 41, 4326) ) """, con=engine, geom_col="geom" ) # 空间连接查询 gdf = gpd.read_postgis( """ SELECT c.name AS city_name, p.name AS province_name, c.population, c.geom FROM cities c JOIN provinces p ON ST_Within(c.geom, p.geom) WHERE p.name = '广东省' """, con=engine, geom_col="geom" ) 9.3.6 参数化查询 # 使用参数化查询防止 SQL 注入 from sqlalchemy import text # 方法1：使用 params 参数 gdf = gpd.read_postgis( text("SELECT * FROM cities WHERE province = :prov AND population > :pop"), con=engine, geom_col="geom", params={"prov": "广东省", "pop": 1000000} ) # 方法2：使用 SQLAlchemy text() 的 bindparams from sqlalchemy import text, bindparam sql = text( "SELECT * FROM cities WHERE name = :name" ).bindparams(bindparam("name", type_=str)) gdf = gpd.read_postgis(sql, con=engine, geom_col="geom", params={"name": "广州"}) 9.4 to_postgis() 详解 9.4.1 基本语法 GeoDataFrame.to_postgis( name, # 目标表名 con, # 数据库连接 schema=None, # 数据库 schema if_exists="fail", # 表已存在时的行为 index=True, # 是否写入索引 index_label=None, # 索引列名 chunksize=None, # 分块写入大小 dtype=None # 列类型映射 ) 9.4.2 基本写入 import geopandas as gpd from sqlalchemy import create_engine engine = create_engine("postgresql://user:pass@localhost:5432/gis_db") # 写入新表 gdf.to_postgis("cities", con=engine) # 写入时会自动： # 1. 创建表结构 # 2. 设置几何列类型和 SRID # 3. 写入数据 9.4.3 if_exists 参数 # 表已存在时的处理策略 # "fail"（默认）：如果表存在则报错 gdf.to_postgis("cities", con=engine, if_exists="fail") # "replace"：删除旧表，创建新表 gdf.to_postgis("cities", con=engine, if_exists="replace") # "append"：追加到已有表 gdf.to_postgis("cities", con=engine, if_exists="append") 9.4.4 指定 schema # 写入到特定 schema gdf.to_postgis("cities", con=engine, schema="public") gdf.to_postgis("cities", con=engine, schema="gis_data") # 确保 schema 存在 from sqlalchemy import text with engine.connect() as conn: conn.execute(text("CREATE SCHEMA IF NOT EXISTS gis_data")) conn.commit() 9.4.5 索引控制 # 写入索引（默认） gdf.to_postgis("cities", con=engine, index=True) # 不写入索引 gdf.to_postgis("cities", con=engine, index=False) # 自定义索引列名 gdf.to_postgis("cities", con=engine, index=True, index_label="gid") 9.4.6 dtype 参数 — 自定义列类型 from sqlalchemy import Integer, String, Float from geoalchemy2 import Geometry # 显式指定列类型 gdf.to_postgis( "cities", con=engine, dtype={ "name": String(100), "population": Integer, "area_km2": Float, "geometry": Geometry("POINT", srid=4326) } ) 9.4.7 分块写入 # 对于大数据集，使用分块写入以控制内存和事务大小 gdf.to_postgis( "large_table", con=engine, chunksize=10000, # 每次写入 10000 行 if_exists="replace" ) 9.4.8 写入后创建空间索引 from sqlalchemy import text # 写入数据 gdf.to_postgis("cities", con=engine, if_exists="replace") # 手动创建空间索引（如果没有自动创建） with engine.connect() as conn: conn.execute(text( "CREATE INDEX IF NOT EXISTS idx_cities_geometry " "ON cities USING GIST (geometry)" )) conn.commit() # 分析表以更新统计信息 with engine.connect() as conn: conn.execute(text("ANALYZE cities")) conn.commit() 9.5 Spatialite 读写 9.5.1 创建 Spatialite 数据库 import sqlite3 # 创建数据库并加载 Spatialite 扩展 conn = sqlite3.connect("my_spatial.db") conn.enable_load_extension(True) try: conn.load_extension("mod_spatialite") except Exception as e: print(f"加载 Spatialite 扩展失败: {e}") print("请确保已安装 libspatialite") # 初始化空间元数据表 conn.execute("SELECT InitSpatialMetaData(1)") conn.commit() 9.5.2 使用 GeoPandas 读取 Spatialite import geopandas as gpd from sqlalchemy import create_engine, event import sqlite3 # 创建引擎并加载 Spatialite 扩展 engine = create_engine("sqlite:///my_spatial.db") @event.listens_for(engine, "connect") def connect(dbapi_connection, connection_record): dbapi_connection.enable_load_extension(True) dbapi_connection.load_extension("mod_spatialite") # 读取数据 gdf = gpd.read_postgis( "SELECT *, AsText(geometry) FROM cities", con=engine, geom_col="geometry" ) # 或者直接使用 read_file() 读取 Spatialite gdf = gpd.read_file("my_spatial.db", layer="cities") 9.5.3 使用 GeoPandas 写入 Spatialite # 方法1：使用 to_file()（推荐，更简单） gdf.to_file("output.db", driver="SQLite", layer="cities") # 方法2：通过 GeoPackage 格式（GeoPackage 本身就是 SQLite） gdf.to_file("output.gpkg", layer="cities") # 方法3：使用 SQLAlchemy（需要更多配置） # 需要 GeoAlchemy2 支持 9.5.4 Spatialite vs GeoPackage 特性 Spatialite GeoPackage 基础 SQLite + SpatiaLite 扩展 SQLite（OGC 标准） 需要扩展 是（mod_spatialite） 否 空间函数 丰富（类似 PostGIS） 有限 互操作性 一般 优秀（OGC 标准） GeoPandas 支持 通过 SQLAlchemy 原生支持 推荐场景 需要复杂空间查询 数据存储和交换 9.6 GeoAlchemy2 集成 9.6.1 GeoAlchemy2 简介 GeoAlchemy2 是 SQLAlchemy 的地理空间扩展，提供了 ORM 模式下的空间数据操作能力。 # 安装 # pip install geoalchemy2 from sqlalchemy import Column, Integer, String from sqlalchemy.orm import declarative_base from geoalchemy2 import Geometry Base = declarative_base() class City(Base): __tablename__ = "cities" id = Column(Integer, primary_key=True) name = Column(String(100)) population = Column(Integer) geom = Column(Geometry("POINT", srid=4326)) 9.6.2 使用 ORM 创建表 from sqlalchemy import create_engine from sqlalchemy.orm import Session engine = create_engine("postgresql://user:pass@localhost:5432/gis_db") # 创建所有表 Base.metadata.create_all(engine) # 插入数据 with Session(engine) as session: city = City( name="北京", population=21710000, geom="SRID=4326;POINT(116.407 39.904)" ) session.add(city) session.commit() 9.6.3 ORM 空间查询 from sqlalchemy.orm import Session from geoalchemy2.functions import ST_DWithin, ST_GeomFromText from geoalchemy2 import WKTElement with Session(engine) as session: # 查找距离某点 0.1 度范围内的城市 center = WKTElement("POINT(116.4 39.9)", srid=4326) cities = session.query(City).filter( ST_DWithin(City.geom, center, 0.1) ).all() for city in cities: print(f"{city.name}: 人口 {city.population}") 9.6.4 将 ORM 查询结果转为 GeoDataFrame import geopandas as gpd from sqlalchemy import select # 使用 SQLAlchemy Core 查询 stmt = select(City).where(City.population > 5000000) # 通过 read_postgis 执行查询 gdf = gpd.read_postgis( stmt, con=engine, geom_col="geom" ) print(gdf) 9.7 高级 SQL 空间查询 9.7.1 空间关系函数 PostGIS 提供了丰富的空间关系函数，可以在 SQL 中直接使用： import geopandas as gpd # ST_Intersects — 相交 gdf = gpd.read_postgis( """ SELECT a.name, a.geom FROM buildings a, flood_zones b WHERE ST_Intersects(a.geom, b.geom) AND b.risk_level = 'high' """, con=engine, geom_col="geom" ) # ST_Within — 在…之内 gdf = gpd.read_postgis( """ SELECT p.name, p.geom FROM pois p, districts d WHERE ST_Within(p.geom, d.geom) AND d.name = '海淀区' """, con=engine, geom_col="geom" ) # ST_Contains — 包含 gdf = gpd.read_postgis( """ SELECT d.name, d.geom, COUNT(p.id) as poi_count FROM districts d LEFT JOIN pois p ON ST_Contains(d.geom, p.geom) GROUP BY d.name, d.geom """, con=engine, geom_col="geom" ) 9.7.2 空间分析函数 # ST_Buffer — 缓冲区分析 gdf = gpd.read_postgis( """ SELECT name, ST_Buffer(geom::geography, 1000)::geometry as geom FROM schools """, con=engine, geom_col="geom" ) # ST_Distance — 距离计算 gdf = gpd.read_postgis( """ SELECT a.name AS school_name, b.name AS hospital_name, ST_Distance(a.geom::geography, b.geom::geography) as distance_m FROM schools a CROSS JOIN LATERAL ( SELECT name, geom FROM hospitals ORDER BY a.geom <-> geom LIMIT 1 ) b """, con=engine, geom_col="geom" # 注意此查询可能需要调整 ) # ST_Union — 合并几何 gdf = gpd.read_postgis( """ SELECT province, ST_Union(geom) as geom, SUM(population) as total_pop FROM cities GROUP BY province """, con=engine, geom_col="geom" ) 9.7.3 空间索引利用 # 使用空间索引优化的查询模式 # PostGIS 会自动利用 GIST 索引 # 最近邻查询（KNN）— 使用 <-> 操作符 gdf = gpd.read_postgis( """ SELECT name, population, geom FROM cities ORDER BY geom <-> ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326) LIMIT 10 """, con=engine, geom_col="geom" ) # 边界框过滤 — 使用 && 操作符 gdf = gpd.read_postgis( """ SELECT * FROM buildings WHERE geom && ST_MakeEnvelope(115, 39, 117, 41, 4326) """, con=engine, geom_col="geom" ) 9.8 大数据量流式读取 9.8.1 chunksize 参数 对于大表，一次性读取可能导致内存不足。使用 chunksize 参数可以分块读取： import geopandas as gpd from sqlalchemy import create_engine engine = create_engine("postgresql://user:pass@localhost:5432/gis_db") # 分块读取，每次 10000 行 chunks = gpd.read_postgis( "SELECT * FROM large_table", con=engine, geom_col="geom", chunksize=10000 ) # chunks 是一个迭代器 results = [] for i, chunk in enumerate(chunks): print(f"处理第 {i+1} 块，{len(chunk)} 行") # 对每块数据进行处理 processed = process_chunk(chunk) results.append(processed) # 合并结果 import pandas as pd final_gdf = gpd.GeoDataFrame(pd.concat(results, ignore_index=True)) 9.8.2 服务端游标 # 使用服务端游标减少客户端内存使用 from sqlalchemy import create_engine, text engine = create_engine( "postgresql://user:pass@localhost:5432/gis_db", execution_options={ "stream_results": True # 启用服务端游标 } ) # 配合 chunksize 使用 chunks = gpd.read_postgis( "SELECT * FROM very_large_table", con=engine, geom_col="geom", chunksize=50000 ) for chunk in chunks: # 处理每个块 pass 9.8.3 分页读取 def read_paginated(engine, table, geom_col, page_size=50000): """使用 OFFSET/LIMIT 分页读取""" offset = 0 all_chunks = [] while True: sql = f""" SELECT * FROM {table} ORDER BY id LIMIT {page_size} OFFSET {offset} """ chunk = gpd.read_postgis(sql, con=engine, geom_col=geom_col) if len(chunk) == 0: break all_chunks.append(chunk) offset += page_size print(f"已读取 {offset} 行...") import pandas as pd return gpd.GeoDataFrame(pd.concat(all_chunks, ignore_index=True)) # 使用 gdf = read_paginated(engine, "large_table", "geom") 9.9 连接池与性能优化 9.9.1 连接池配置 from sqlalchemy import create_engine from sqlalchemy.pool import QueuePool # 配置连接池 engine = create_engine( "postgresql://user:pass@localhost:5432/gis_db", poolclass=QueuePool, pool_size=5, # 常驻连接数 max_overflow=10, # 最大溢出连接数 pool_timeout=30, # 获取连接的超时时间（秒） pool_recycle=1800, # 连接回收时间（秒） pool_pre_ping=True # 使用前检查连接是否有效 ) # 查看连接池状态 print(engine.pool.status()) 9.9.2 批量写入优化 import geopandas as gpd # 1. 使用适当的 chunksize gdf.to_postgis("table", con=engine, chunksize=5000) # 2. 先删除索引，写入后重建（大批量写入时） from sqlalchemy import text with engine.connect() as conn: # 删除空间索引 conn.execute(text("DROP INDEX IF EXISTS idx_table_geom")) conn.commit() # 写入数据 gdf.to_postgis("table", con=engine, if_exists="replace") # 重建索引 with engine.connect() as conn: conn.execute(text( "CREATE INDEX idx_table_geom ON table USING GIST (geometry)" )) conn.execute(text("ANALYZE table")) conn.commit() # 3. 使用 COPY 命令（最快的批量写入方式） # 需要使用 psycopg2 的 copy_expert 功能 # 这超出了 GeoPandas 的直接支持范围 9.9.3 查询优化技巧 # 1. 只选择需要的列 gdf = gpd.read_postgis( "SELECT name, population, geom FROM cities", # 而非 SELECT * con=engine, geom_col="geom" ) # 2. 使用空间索引友好的查询 # ✅ 好：使用边界框先过滤 gdf = gpd.read_postgis( """ SELECT * FROM buildings WHERE geom && ST_MakeEnvelope(115, 39, 117, 41, 4326) AND ST_Intersects(geom, some_complex_geometry) """, con=engine, geom_col="geom" ) # 3. 使用 LIMIT 限制结果集 gdf = gpd.read_postgis( "SELECT * FROM pois ORDER BY geom <-> reference_point LIMIT 100", con=engine, geom_col="geom" ) 9.10 数据库空间索引 9.10.1 空间索引的重要性 空间索引是空间数据库性能的关键。没有空间索引，每次空间查询都需要扫描全表。 -- 创建 GIST 空间索引（PostGIS） CREATE INDEX idx_cities_geom ON cities USING GIST (geom); -- 查看索引使用情况 EXPLAIN ANALYZE SELECT * FROM cities WHERE ST_Intersects(geom, ST_MakeEnvelope(115, 39, 117, 41, 4326)); 9.10.2 在 Python 中管理空间索引 from sqlalchemy import text, inspect # 检查表是否有空间索引 def check_spatial_index(engine, table_name, geom_col="geometry"): """检查表是否有空间索引""" inspector = inspect(engine) indexes = inspector.get_indexes(table_name) for idx in indexes: if geom_col in idx.get("column_names", []): print(f"✓ 找到空间索引: {idx['name']}") return True print(f"⚠️ 表 {table_name} 没有空间索引") return False # 创建空间索引 def create_spatial_index(engine, table_name, geom_col="geometry"): """创建空间索引""" index_name = f"idx_{table_name}_{geom_col}" sql = f"CREATE INDEX IF NOT EXISTS {index_name} ON {table_name} USING GIST ({geom_col})" with engine.connect() as conn: conn.execute(text(sql)) conn.execute(text(f"ANALYZE {table_name}")) conn.commit() print(f"✓ 已创建空间索引: {index_name}") 9.10.3 索引类型对比 索引类型 说明 适用场景 GIST 通用搜索树，PostGIS 默认 空间查询（推荐） SP-GiST 空间分区 GiST 非均匀分布的数据 BRIN 块范围索引，体积极小 物理有序的大数据集 R-tree Spatialite 使用 Spatialite 数据库 9.11 常见问题排查 9.11.1 连接问题 # 问题1：连接被拒绝 # 解决：检查数据库是否运行、端口是否正确、防火墙设置 try: engine = create_engine("postgresql://user:pass@localhost:5432/gis_db") with engine.connect() as conn: conn.execute(text("SELECT 1")) print("✓ 连接成功") except Exception as e: print(f"✗ 连接失败: {e}") # 问题2：认证失败 # 解决：检查用户名密码、pg_hba.conf 配置 # 问题3：数据库不存在 # 解决：先创建数据库 # createdb -U postgres gis_db 9.11.2 几何列识别问题 # 问题：read_postgis() 无法识别几何列 # 原因：查询结果中的几何列名与 geom_col 参数不匹配 # 解决1：明确指定几何列名 gdf = gpd.read_postgis( "SELECT id, name, the_geom FROM my_table", con=engine, geom_col="the_geom" # 必须与 SELECT 中的列名一致 ) # 解决2：使用别名 gdf = gpd.read_postgis( "SELECT id, name, shape AS geom FROM my_table", con=engine, geom_col="geom" ) 9.11.3 CRS 问题 # 问题：读取后 CRS 为 None # 原因：几何列没有注册 SRID # 检查 SRID with engine.connect() as conn: result = conn.execute(text( "SELECT ST_SRID(geom) FROM my_table LIMIT 1" )) srid = result.scalar() print(f"SRID: {srid}") # 如果 SRID 为 0 或 -1，需要手动设置 gdf = gpd.read_postgis( "SELECT * FROM my_table", con=engine, geom_col="geom", crs="EPSG:4326" # 显式指定 CRS ) # 修复数据库中的 SRID with engine.connect() as conn: conn.execute(text( "SELECT UpdateGeometrySRID('my_table', 'geom', 4326)" )) conn.commit() 9.11.4 编码问题 # 问题：中文乱码 # 解决：确保连接使用 UTF-8 编码 engine = create_engine( "postgresql://user:pass@localhost:5432/gis_db" "?client_encoding=utf8" ) # 或者在 PostgreSQL 配置中设置 # ALTER DATABASE gis_db SET client_encoding TO 'UTF8'; 9.11.5 性能问题 # 问题：查询非常慢 # 排查步骤： # 1. 检查是否有空间索引 with engine.connect() as conn: result = conn.execute(text(""" SELECT indexname FROM pg_indexes WHERE tablename = 'my_table' """)) indexes = result.fetchall() print("索引列表:", indexes) # 2. 使用 EXPLAIN ANALYZE 分析查询 with engine.connect() as conn: result = conn.execute(text(""" EXPLAIN ANALYZE SELECT * FROM my_table WHERE ST_Intersects(geom, ST_MakeEnvelope(115, 39, 117, 41, 4326)) """)) for row in result: print(row[0]) # 3. 检查表大小和行数 with engine.connect() as conn: result = conn.execute(text(""" SELECT pg_size_pretty(pg_total_relation_size('my_table')) as total_size, (SELECT count(*) FROM my_table) as row_count """)) row = result.fetchone() print(f"表大小: {row[0]}, 行数: {row[1]}") 9.12 本章小结 本章详细介绍了 GeoPandas 与空间数据库的交互，涵盖了以下核心内容： 主题 关键要点 空间数据库 PostGIS 是最强大的开源选择，Spatialite 适合轻量级场景 连接配置 使用 SQLAlchemy Engine，支持连接池 read_postgis() 支持 SQL 查询、几何列指定、CRS 设置、参数化查询 to_postgis() 支持表创建、追加、替换，自动处理几何类型 Spatialite 零配置嵌入式空间数据库，通过 read_file() 更简单 GeoAlchemy2 SQLAlchemy 的空间扩展，支持 ORM 模式 空间 SQL ST_Intersects、ST_Within、ST_Buffer 等函数 流式读取 chunksize 参数控制内存使用 性能优化 连接池、空间索引、查询优化 核心建议： 大型项目使用 PostGIS，小型项目可用 Spatialite 或 GeoPackage 始终创建空间索引 使用参数化查询防止 SQL 注入 大数据集使用分块读取（chunksize） 批量写入前删除索引，写入后重建 使用环境变量管理数据库凭据，避免硬编码 下一章我们将学习 GeoPandas 中的几何属性与度量功能，了解如何获取几何对象的各种属性信息。