将GeoDataFrame写入SQL数据库

一尘不染

将GeoDataFrame写入SQL数据库

mysql

我希望我的问题不会很荒谬，因为令人惊讶的是，就流行的网站而言（据我所知）显然还没有真正问过这个问题。

情况是我有几个csv文件，总共包含1个以上的Mio观测值。每个观察结果都包含一个邮政地址。我打算将所有文件读入一个GeoDataFrame中，对地址进行地理编码，在给定shapefile的情况下执行空间连接，并为每行保存来自多边形的一些信息。我想是相当标准的。这是一次性数据清理过程的一部分。

我的目标是使用此最终数据集建立数据库。这是因为它使我可以轻松共享和搜索数据，以及在网站上绘制一些观察结果。而且，它使得根据某些标准选择观察值然后进行一些分析变得非常容易。

我的问题是，似乎尚未实现将GeoDataFrame插入数据库的功能-显然是因为GeoPandas应该是数据库的替代品（“
GeoPandas使您能够轻松地在python中执行操作，而该操作否则将需要空间数据库，例如作为PostGIS”）。

当然，我可以遍历每一行并“手动”插入每个数据点，但是我正在这里寻找最佳解决方案。对于任何解决方法，我还担心数据类型可能与数据库的数据类型冲突。是否有“最好的方法”可以采取？

谢谢你的帮助。

阅读 531

2020-05-17

共1个答案

一尘不染

因此，我只是为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)

2020-05-17