Loading Geodataframe to PostgreSQL with psycopg2 connection

Question

I have created a Geodataframe from a PostgreSQL table with the following instruction (where 'con' is a psycopg2 connection):

gdf = gpd.GeoDataFrame.from_postgis("SELECT id, geom FROM {0}".format('pg_table'), con=con, geom_col='geom')

I have then processed the Geodataframe and I would like to load the result into a new table in PostgreSQL. However, I don't seem to find an equivalent instruction to 'from_postgis' (something like 'to_postgis' or similar). I have read a post about some SQLAlchemy, which seems over-complicated and I must admit I have not understood. Just wondering whether there's any simple way to do what I'm trying to do?

Answer 1

You can use the to_sql method in pandas. If you don't have any spatial datatypes to write you can use it as is. But if you do have spatial datatypes, you'll need GeoAlchemy. Here is an example for writing a geodataframe with Point data in a column called geom:

import geopandas as gpd
from sqlalchemy import create_engine
from geoalchemy2 import Geometry

engine = create_engine('postgresql://user:password@server:port/dbname')

gdf.to_sql(
    tablename,
    engine,
    if_exists='append',
    index=False, 
    dtype={
        'geom': Geometry('POINT', srid=4326),
    }
)