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.
type(gdf)
after the gdf = ... linegdf.to_postgis('Peng', engine, if_exists='append')
. You can then st_setsrid if your crs is unknown to postgis