PostGIS – Setting CRS/SRID Using to_postgis Function with GeoPandas

geoalchemy-2geopandaspostgispythonsqlalchemy

So, I'm trying to update a PostGIS table after doing some reverse geocoding and other column manipulation I'm running into the find_srid() - could not find the corresponding SRID error.

Based on my research, this should have to do with a type-mismatch error with how the SRID is being passed and PostGIS expecting it as a varchar, but I can't seem to work out the right syntax to send the data to PostGIS.

I can confirm that the SRID is set in the geometry column in my database.

And here's my code:

import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine
import geoalchemy2
from geoalchemy2 import Geometry 
import shapely
from shapely import wkt
from shapely.geometry import Point
import psycopg2
    
df = pd.read_csv('address-peng3.csv')
engine = create_engine('postgresql://Nizz0k@localhost:5432/Nizz0k')
df['geom'] = df['geom'].apply(wkt.loads)
crs = 'EPSG:4326'
gdf = gpd.GeoDataFrame(df, crs=crs, geometry=df['geom'])
gdf.to_postgis('Peng', engine, if_exists='append', dtype={'geom': Geometry('POINT', srid=4326)})

UPDATE: So, per user @BERA's comments below, I've been able to progress through some of the errors and confirm that the GeoPandas data frame was created correctly. What I have noticed is that the SQL statement presented in the error: SELECT Find_SRID('public', 'Peng', 'geometry'); fails if I run it in PGADMIN, but it fails because that does not correspond to the name of the geometry column in my database. Running the SQL SELECT Find_SRID('public', 'Peng', 'geom'); returns the expected 4326.

UPDATE 2:
I found the following issue in the GeoPandas Issue Tracker which seems to be related. In the cited link, the user has 'geography' columns and apparently there isn't an official way to target where the to_postgis function is looking for the SRID.

Best Answer

If you are interested in a workaround you can import your pandas dataframe and create a point table like this:

import pandas as pd
csvfile = r"C:\sample_data\data_1.csv"
df = pd.read_csv(csvfile)

from sqlalchemy import create_engine
engine = create_engine('postgresql://youruser:yourpassword@localhost:5432/yourdatabase')
df.to_sql('data1', engine)

Then in postgresql:

--my geog column has wkt strings
select geog from public.data1 limit 1
--POINT (6688623.275372778 522577.4544667492)

--Add a geometry column
SELECT AddGeometryColumn('public','data1','geom',3006,'POINT',2); --3006 is my crs

--Fill with geometries using the wkt strings in geog column
update public.data1
set geom = st_geomfromtext(geog, 3006)
Related Question