2

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)})
1

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)})
  • Thank you for pointing that out! I really got myself confused with the variables :) Much appreciated – geogrow Jun 11 at 10:16

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.