Setting CRS/SRID using the to_postgis function with GeoPandas?

Question

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.

Answer 1

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)

Answer 2

As far as I can see, your issue comes from the fact that you set a pd.Series in the geometry attribute of your GeoDataFrame instead of a string refering to the geom column name. When a pd.Series is set as geometry, GeoPandas considers it as a new specific column, while if a string is set the geometry column is just an alias for another one, thus when using the to_postgis method it is not considered as a column and not inserted into the DB.

Here is a complete example to reproduce and fix your issue (should be run with pytest). It creates a table with 2 objects and append two new ones in the test (we just update the IDs in this example but you could also update the geometries if you wish).

from pathlib import Path

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

from sqlalchemy import create_engine
from sqlalchemy import MetaData, Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from geoalchemy2 import Geometry


engine = create_engine("postgresql://gis:gis@localhost/gis", echo=True)
metadata = MetaData(engine)
Base = declarative_base(metadata=metadata)
session = sessionmaker(bind=engine)()


class Point(Base):
    __tablename__ = "point"
    __table_args__ = {"schema": "gis"}
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry(geometry_type="POINT", srid=4326))


@pytest.fixture
def meta():
    metadata.drop_all(checkfirst=True)
    metadata.create_all()
    yield metadata
    session.rollback()
    metadata.drop_all()


@pytest.fixture
def raw_data():
    return pd.DataFrame(
        [
            {"id": 1, "geom": "POINT(5 45)"},
            {"id": 2, "geom": "POINT(5.5 45.5)"},
        ]
    )


@pytest.fixture
def csv_points(raw_data, tmpdir):
    filepath = Path(tmpdir / "data.csv")
    if not filepath.exists():
        raw_data.to_csv(filepath, index=False)
    return filepath


@pytest.fixture
def db_points(meta, raw_data):
    pts = []
    for elem in raw_data.to_dict("records"):
        # Create point
        p = Point()
        p.id = elem["id"]
        p.geom = f"SRID=4326;{elem['geom']}"
        pts.append(p.id)

        # Insert point
        session.add(p)
        session.flush()
        session.commit()
        session.expire(p)

    yield pts


def test_to_postgis(csv_points, db_points):
    df = pd.read_csv(csv_points)
    df["geom"] = df["geom"].apply(wkt.loads)
    df["id"] += 10
    crs = "EPSG:4326"
    gdf = gpd.GeoDataFrame(df, crs=crs, geometry="geom")  # This works because the geometry column is just an alias to the geom column
    # gdf = gpd.GeoDataFrame(df, crs=crs, geometry=df["geom"])  # <= This fails because the geometry column is a Series
    gdf.to_postgis(
        "point",
        engine,
        schema="gis",
        if_exists="append",
        dtype={"geom": Geometry("POINT", srid=4326)},
    )