Geopandas用to_sql导出到MySQL时geometry列变为text且报错1416如何解决？

错误原因 #

• 直接修改列类型不会触发格式转换：你直接执行ALTER TABLE MODIFY COLUMN语句时，MySQL不会自动将text列中存储的WKT/WKB文本转换为内部可识别的几何存储格式，直接强转就会抛出1416 - Cannot get geometry object from data you send to the GEOMETRY field错误
• GeoAlchemy2的Geometry类型默认适配PostGIS：原生写法对MySQL的空间类型支持存在适配问题，未做转换的几何对象会被直接序列化为文本存入数据库
• 连接串未指定驱动会出现类型映射异常：裸写mysql://前缀会触发驱动兼容问题，导致几何类型映射失效

解决方案 #

方案1：代码层面直接导入几何类型 #

首先安装必要依赖：pip install pymysql geopandas geoalchemy2 sqlalchemy
修正后的代码如下：

import geopandas
from geoalchemy2 import Geometry
from sqlalchemy import create_engine, types

df = geopandas.read_file('geodata.geojson')
# 提前将geometry列转为WKB二进制格式，适配MySQL识别
df['geometry'] = df.geometry.apply(lambda x: x.wkb)

hostname="localhost"
dbname="mydbname"
uname="iamroot"
pwd = "madeyoulook"

# 连接串指定pymysql驱动，避免兼容问题
engine = create_engine(f'mysql+pymysql://{uname}:{pwd}@{hostname}/{dbname}')

df.to_sql(
    'geodatacounty', 
    engine, 
    if_exists='replace', 
    index=False, 
    dtype={
        'shape_leng': types.FLOAT, 
        'shape_area': types.FLOAT, 
        # 不要给列名加多余的反引号，指定几何类型和SRID
        'geometry': Geometry(geometry_type='MULTIPOLYGON', srid=4326)
    }
)

方案2：导入后通过SQL转换（兼容性更强） #

如果方案1仍存在适配问题，可先存为文本再做转换，步骤如下：

1. 先将几何列转成WKT文本后导入：

import geopandas
from sqlalchemy import create_engine, types

df = geopandas.read_file('geodata.geojson')
# 转成WKT文本
df['geometry'] = df.geometry.apply(lambda x: x.wkt)

hostname="localhost"
dbname="mydbname"
uname="iamroot"
pwd = "madeyoulook"

engine = create_engine(f'mysql+pymysql://{uname}:{pwd}@{hostname}/{dbname}')

df.to_sql(
    'geodatacounty', 
    engine, 
    if_exists='replace', 
    index=False, 
    dtype={
        'shape_leng': types.FLOAT, 
        'shape_area': types.FLOAT
    }
)

2. 在MySQL中依次执行如下SQL完成转换：

-- 新增几何类型列，指定对应的类型和SRID
ALTER TABLE geodatacounty ADD COLUMN geom MULTIPOLYGON SRID 4326;
-- 用MySQL自带的空间函数将WKT文本转成几何对象
UPDATE geodatacounty SET geom = ST_GeomFromText(geometry, 4326);
-- 替换原列
ALTER TABLE geodatacounty DROP COLUMN geometry;
ALTER TABLE geodatacounty CHANGE COLUMN geom geometry MULTIPOLYGON SRID 4326;

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