时间戳

首页 > 解决方案 > Geopandas to_sql 在 mysql 中将几何列显示为文本

python - Geopandas to_sql 在 mysql 中将几何列显示为文本

问题描述

我正在尝试从 geojson 获取多面数据，将其读入数据框，然后在 mysql 中创建一个包含数据的表。

对我来说奇怪的是，检查dtype脚本末尾的 将几何列正确显示为geometry. 但是，检查 mysql db 此列显示为text. 尝试将列转换为几何或多面体类型会引发错误

1416 - 无法从您发送到 GEOMETRY 字段的数据中获取几何对象

我尝试了以下查询，这可能是我的问题所在？

ALTER TABLE [table]
MODIFY COLUMN [column] GEOMETRY

类似的问题给出了将数据转换为 WKT 或 WKB 的答案。但是，使用 to_wkb（或 to_wkt）方法然后运行查询也会导致前面提到的错误。我也尝试过自己制作功能，但没有运气。下面是 Python 代码。

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


df = geopandas.read_file('geodata.geojson')

# geodataframe = df.to_wkb()

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

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

df.to_sql('geodatacounty', engine, if_exists='replace', index=False, dtype={'shape_leng': types.FLOAT , 'shape_area': types.FLOAT, '`geometry`': Geometry(geometry_type='MULTIPOLYGON', srid=4326)})

标签： pythonmysqlsqlgeopandaspandas-to-sql

解决方案

AFAIK，既没有sqlalchemy也没有geoalchemy2与 MySQL 直接兼容的几何类型，因此您拥有的可能适用于 PostGIS 的示例不会为 MySQL 生成语法正确的语句。因此，您需要解决此问题，例如首先将列导入为TEXT，然后将数据转换为GEOMETRY.

如果您在TEXT类型列中有多边形数据，则可以使用ST_GeomFromText(). 为确保您可以正确存储结果，请先创建一个额外的类型GEOMETRY（MULTIPOLYGON或任何您想要的）列：

ALTER TABLE tab ADD COLUMN newcolumn GEOMETRY;

然后更新该列：

UPDATE tab SET newcolumn = ST_GeomFromText(oldcolumn);

笔记：

  • 如果您首先在导入数据时遇到困难，那么更改数据库的编码可能会有所帮助： 
    ALTER DATABASE mydbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
  • 如果您首先遇到几何图形的 WKT 文本表示对于列而言太大的错误TEXT，您可以显式使用dtype参数 of.to_sql()将它们存储在LONGTEXT列中： 
    from sqlalchemy.dialects.mysql import LONGTEXT

gdf.to_sql('tab', con, if_exists='replace', index=False, dtype = {'geometry': LONGTEXT})
    （这也适用于MEDIUMTEXT）。

在 MySQL 5.7 的快速测试中对我有用的完整 Python 示例：

import geopandas as gpd
from sqlalchemy import create_engine, sql

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

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

gdf = gpd.read_file('geodata.geojson')
gdf = gdf.to_wkt()

with engine.connect() as con:
    # may not be necessary, see above. 
    con.execute(sql.text("""ALTER DATABASE mydbname CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;"""))

    gdf.to_sql('tab', con, if_exists='replace', index=False)

    con.execute(sql.text("""ALTER TABLE mydbname.tab ADD COLUMN new_geometry GEOMETRY;"""))
    con.execute(sql.text("""UPDATE mydbname.tab SET new_geometry = ST_GeomFromText(geometry);"""))

然后，您应该在表中具有相应new_geometry的类型列，该列以 MySQL 的内部格式存储几何。例如，您可以在该列上使用MySQL 几何格式转换函数来取回 WKT 表示：GEOMETRYtab

SELECT ST_AsWKT(new_geometry) from tab;

推荐阅读