1

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.

8
  • So, I have tried a few different ways to do it including using the crs = {init: 'epsg:4326'}, so I'm not sure which syntax is the right one as the few solutions I've seen don't seem to work.
    – nizz0k
    Jan 12, 2022 at 14:34
  • it fails with and without, with I get an error about geometry being undefined, without I get the srid not found error
    – nizz0k
    Jan 12, 2022 at 14:38
  • Are you sure gdf is a geodataframe, try type(gdf) after the gdf = ... line
    – BERA
    Jan 12, 2022 at 14:41
  • could be the issue, doesn't return anything at all
    – nizz0k
    Jan 12, 2022 at 14:44
  • Have you tried simply gdf.to_postgis('Peng', engine, if_exists='append') . You can then st_setsrid if your crs is unknown to postgis
    – BERA
    Jan 13, 2022 at 8:13

2 Answers 2

2

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)
1
  • So, I don't want to just accept this without testing this, but going back to the first script you helped me with confirms your (deleted?) comments that I was creating the geodataframe either unnecessarily or incorrectly. I'm more than willing to give credit where it's due, but I don't want to lead someone astray that this problem is unsolvable because I'm a noob that doesn't know what he's doing. The only reason I separated the steps was that the geocoding for 1500 points is tediously long to wait for an error and move to the next step. Unfortunately, I haven't broken my questions to pieces...
    – nizz0k
    Jan 13, 2022 at 21:07
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.