Writing GeoPandas data frame to PostGIS?

Question

Similar question has to this has been asked before Adding GeoPandas Dataframe to PostGIS table? I have followed the example but I am running into an issue I cannot solve. There is some geometry problem. What am I doing wrong?

I have defined a simple geometry in PostgreSQL using PostGIS:

alter table helloworld
ADD COLUMN geom geometry(Point,4326)

This is the error I get running the script

ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'Point'
[SQL: INSERT INTO helloworld (hello, world, geometry) VALUES (%(hello)s, %(world)s, ST_GeomFromEWKT(%(geometry)s))]
[parameters: ({'hello': 'hello1', 'world': 'world1', 'geometry': <shapely.geometry.point.Point object at 0x000001F7C5B21BE0>}, {'hello': 'hello2', 'world': 'world2', 'geometry': <shapely.geometry.point.Point object at 0x000001F7C5B21518>})]

Here is my script.

# Creating SQLAlchemy's engine to use
engine = create_engine('postgresql://postgres:postgres@127.0.0.1:5432/geospatial')

# ====== Writing table ====== #
# Creating a simple pandas DataFrame with two columns
liste_hello = ['hello1','hello2']
liste_world = ['world1','world2']
geom=['POINT(7.14111328125 51.957807388715516)','POINT(9.426269531249998 52.24125614966341)']

# Create DataFrame
df1= pd.DataFrame(data = {'hello' : liste_hello, 'world': liste_world, 'geom':geom })
df1['geometry'] = df1['geom'].apply(wkt.loads)

# Create GeoDataFrame with geom from dataframe
gdf1 = geopandas.GeoDataFrame(df1, geometry='geometry')

#https://gis.stackexchange.com/questions/239198/geopandas-dataframe-to-postgis-table-help  
gdf1['geom'] = gdf1['geometry'].apply(lambda x: WKTElement(x.wkt, srid=4326))
gdf1

#drop the geometry column as it is now duplicative
gdf1.drop('geom', 1, inplace=True)
gdf1

# Write to db
gdf1.to_sql('helloworld', engine, if_exists='append', index=False, dtype={'geometry': Geometry(geometry_type='POINT', srid= 4326)})

What is wkt in this example? – cglacet May 13 '20 at 11:05 — cglacet, May 13 '20 at 11:05

score 6 · Accepted Answer · answered Jun 11 '19 at 07:08

6

You are dropping the wrong column which leads your script to try to insert in the DB the type Point from Shapely, change this line :

gdf1.drop('geom', 1, inplace=True)

to this :

gdf1.drop('geometry', 1, inplace=True)

and the last line to this:

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

answered Jun 11 '19 at 07:08

Hicham Zouarhi

3,255
2
19
32

Thank you for pointing that out! I really got myself confused with the variables :) Much appreciated – geogrow Jun 11 '19 at 10:16

score 2 · Answer 2 · answered Sep 29 '20 at 16:10

GeoPandas 0.7+ now has a new to_postigs method. Using that method the last line becomes:

gdf1.to_postgis('helloworld', engine, if_exists='append', index=False, dtype={'geom': Geometry(geometry_type='POINT', srid= 4326)})

This method is more performant but requires Geoalchemy2

Writing GeoPandas data frame to PostGIS?

2 Answers2

Linked