0

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?

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),
    }
)
  • Hi Ryan, May I know what is 'eng'? – Pitrako Junior Dec 25 '19 at 20:24
  • @PitrakoJunior, good question. eng is a sqlalchemy engine. I edited my answer to show how to create it. – Ryan Dec 26 '19 at 16:48
  • You do not need geoalchemy to insert a spatial table, I do it all the time with psycopg2 without issue. – wfgeo Dec 26 '19 at 17:54
  • Thanks Ryan for the clarification. @wfgeo, may we know how you do it with psycopg2? – Pitrako Junior Dec 27 '19 at 9:28
  • @PitrakoJunior Sorry, I am mistaken. I guess I was mistaking all the reading I was doing for writing. A psycopg2 connection on its own is indeed not sufficient for writing a geopandas dataframe into a PostGIS database. – wfgeo Dec 27 '19 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.