python - Write GeoDataFrame into SQL Database

Question

I hope that my question is not ridiculous since, surprisingly, this question has apparently not really been asked yet (to the best of my knowledge) on the popular websites.

The situation is that I have several csv files containing more than 1 Mio observations in total. Each observation contains, among others, a postal address. I am planning to read all files into a single GeoDataFrame, geocode the addresses, perform a spatial join given a shapefile and save some information from the polygon for each row. Quite standard, I suppose. This is a part of a one-time data cleaning process.

My goal is to set up a database with this final dataset. This is because it allows me to share and search the data quite easily as well as e.g. plot some observations on a website. Also, it makes it quite easy to select observations based on some criteria and then run some analyses.

My problem is that the feature of inserting a GeoDataFrame into a database seems not to be implemented yet - apparently because GeoPandas is supposed to be a subsitute for databases ("GeoPandas enables you to easily do operations in python that would otherwise require a spatial database such as PostGIS").

Of course, I could iterate through each line and insert each data point "manually", but I am looking for the best solution here. For any workaround I would also be afraid that the datatype may conflict with that of the database. Is there "a best way" to take here?

Thanks for your help.

Answer 1

As mentioned before, @Kartik's answer works only for a single call, for appending data it raises a DataError since the geom column then expects the geometry to have an SRID. You can use GeoAlchemy to handle all the cases:

# 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 = '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>)})