因此,我只是为PostGIS数据库实现了这一点,可以在这里粘贴我的方法。对于MysqL,您必须修改代码。
第一步是在地理编码列转换为WKB十六进制字符串,因为我使用SQLAlchemy的,基于发动机pyscopg,并且这两个包的不理解地理类型本身。下一步是照常将数据写入sql DB(请注意,所有几何列都应转换为包含WKB十六进制字符串的文本列),最后通过执行查询将列的类型更改为几何。请参考以下伪代码:
# Imports
import sqlalchemy as sal
import geopandas as gpd
# Function to generate WKB hex
def wkb_hexer(line):
return line.wkb_hex
# Convert `'geom'` column in GeoDataFrame `gdf` to hex
# Note that following this step, the GeoDataFrame is just a regular DataFrame
# because it does not have a geometry column anymore. Also note that
# it is assumed the `'geom'` column is correctly datatyped.
gdf['geom'] = gdf['geom'].apply(wkb_hexer)
# Create sql connection engine
engine = sal.create_engine('postgresql://username:password@host:socket/database')
# Connect to database using a context manager
with engine.connect() as conn, conn.begin():
# Note use of regular Pandas `to_sql()` method.
gdf.to_sql(table_name, con=conn, schema=schema_name,
if_exists='append', index=False)
# Convert the `'geom'` column back to Geometry datatype, from text
sql = """ALTER TABLE schema_name.table_name
ALTER COLUMN geom TYPE Geometry(LINESTRING, <SRID>)
USING ST_SetSRID(geom::Geometry, <SRID>)"""
conn.execute(sql)