GeoPandas to PostGIS – Troubleshooting ‘No Valid Geometries’ Error with Tables from PostGIS

geopandaspostgispython

So, I apologize in advance as there's probably better ways to approach what I'm doing and I'm new to Python. I'm trying to update a PostGIS table with newly appended columns from reverse geocoding and some data manipulation in GeoPandas.

The original data is a SHP file created in QGIS from geotagged images that was imported to PostGIS. I then proceeded use Python to pull the data out and run reverse geocoding using Nominatim. After some column cleaning and manipulation, I wanted to put the new data back into PostGIS and update the original table with new data. As a part of my learning process I had to separate this into a few steps and now I have a cleaned CSV of my data but I'm struggling with sending the data back with the to_postgis function. Somewhat confusingly, I am getting a ValueError: No valid geometries in the data. at the line in my code that has the to_postgis function.

import pandas as pd
import geopandas as gpd
import geopy
from sqlalchemy import create_engine
import geoalchemy2
import shapely
from shapely.geometry import Point
import psycopg2

engine = create_engine('postgresql://Nizz0k@localhost:5432/public.\"Peng\"')
df = gpd.read_file('/Users/Nizz0k/Sites/python-spatial/address-peng3.csv', geom_col='geom')
df.to_postgis('Peng', engine, if_exists='append')

here is a link to a google sheet with the CSV I'm loading

Best Answer

Your df is a pandas dataframe, not a geopandas

Try this:

import geopandas as gpd
import pandas as pd
from shapely import wkt

csv= r"C:\folder\Pengapp - address-peng3.csv"
df = pd.read_csv(csv)

df['geom'] = df['geom'].apply(lambda x: wkt.loads(x)) #geom column is just a string, create a geometry using shapely
gdf = gpd.GeoDataFrame(df, geometry=df['geom'])
Related Question