Записать GeoDataFrame в базу данных SQL

Я надеюсь, что мой вопрос не является смешным, поскольку, как ни удивительно, этот вопрос, по-видимому, еще не был задан (насколько мне известно) на популярных веб-сайтах.

Ситуация такова, что у меня есть несколько CSV-файлов, содержащих в общей сложности более 1 млн. Наблюдений. Каждое наблюдение содержит, помимо прочего, почтовый адрес. Я планирую прочитать все файлы в одном GeoDataFrame, геокодировать адреса, выполнить пространственное соединение по заданному шейп-файлу и сохранить некоторую информацию из полигона для каждой строки. Я полагаю, вполне стандартно. Это часть единовременного процесса очистки данных.

Моя цель - создать базу данных с этим окончательным набором данных. Это потому, что это позволяет мне легко обмениваться данными и искать их, а также, например, выкладывать некоторые наблюдения на сайт. Кроме того, это позволяет довольно легко выбирать наблюдения на основе некоторых критериев, а затем проводить некоторые анализы.

Моя проблема в том, что функция вставки GeoDataFrame в базу данных, кажется, еще не реализована - очевидно, потому что GeoPandas должен быть заменой для баз данных ("GeoPandas позволяет вам легко выполнять операции в python, которые в противном случае потребовали бы пространственной базы данных, такой как как PostGIS").

Конечно, я мог бы перебирать каждую строку и вставлять каждую точку данных "вручную", но я ищу здесь лучшее решение. Для любого обходного пути я также боялся бы, что тип данных может конфликтовать с типом данных базы данных. Есть ли "лучший способ" взять здесь?

Спасибо за вашу помощь.

Источник

4 ответа

Решение

Итак, я только что реализовал это для базы данных PostGIS, и я могу вставить свой метод здесь. Для MySQL вам придется адаптировать код.

Первым шагом было преобразование геокодированных столбцов в шестнадцатеричную строку WKB, потому что я использую SQLAlchemy с механизмом, основанным на pyscopg, и оба этих пакета изначально не понимают гео-типы. Следующим шагом является запись этих данных в базу данных SQL, как обычно (обратите внимание, что все столбцы геометрии следует преобразовать в текстовые столбцы, содержащие шестнадцатеричную строку WKB), и, наконец, изменив тип столбцов на Geometry, выполнив запрос. Обратитесь к следующему псевдокоду:

# 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)
Источник

Как упоминалось ранее, ответ @Kartik работает только для одного вызова, для добавления данных он вызывает DataError так как geom Затем столбец ожидает, что геометрия имеет SRID. Ты можешь использовать GeoAlchemy обрабатывать все случаи:

# Imports
from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import *

# Use GeoAlchemy's WKTElement to create a geom with SRID
def create_wkt_element(geom):
    return WKTElement(geom.wkt, srid = <your_SRID>)

geodataframe['geom'] = geodataframe['geom'].apply(create_wkt_element)

db_url = create_engine('postgresql://username:password@host:socket/database')
engine = create_engine(db_url, echo=False)

# Use 'dtype' to specify column's type
# For the geom column, we will use GeoAlchemy's type 'Geometry'
your_geodataframe.to_sql(table_name, engine, if_exists='append', index=False, 
                         dtype={geom: Geometry('POINT', srid= <your_srid>)})
Источник

Версия ответа Хамри Саида, но с использованием лямбда, что, на мой взгляд, немного лучше, потому что это такая короткая функция:

# Imports
from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import *

geodataframe['geom'] = geodataframe['geom'].apply(lambda geom: WKTElement(geom.wkt, srid = <your_SRID>))

db_url = create_engine('postgresql://username:password@host:socket/database')
engine = create_engine(db_url, echo=False)

# Use 'dtype' to specify column's type
# For the geom column, we will use GeoAlchemy's type 'Geometry'
your_geodataframe.to_sql(table_name, engine, if_exists='append', index=False, 
                         dtype={'geom': Geometry('POINT', srid= <your_srid>)})
Источник

Я возвращаюсь к этому, чтобы дать лучший ответ. А geopandas.GeoDataFrame объект имеет .to_postgis() метод, который справляется со многими грязными делами, связанными с типами геометрии.

Источник
Другие вопросы по тегам