4

I would like to insert a geodataframe containing point geometries in SRID 4326 to an existing table in PostGIS+PostgreSQL. The issue is that I would like to retain the existing table structure, which has GEOGRAPHY data type column, but inserts fail. Using a sqlalchemy connection engine, I try the following:

dtypes(gdf)
ts              datetime64[ns]
geog                  geometry
id                      int64

dtype: object 

The geodataframe has a geometry column named geog

import geopandas as gpd
import psycopg2
import numpy as np
import json
from collections import defaultdict
import sqlalchemy
from geopandas import GeoDataFrame
from shapely.geometry import Point
from geoalchemy2 import Geometry, WKTElement, Geography

with engine.connect() as conn:
    gdf.to_postgis('my_data', con=conn, if_exists='append', index=False, dtype={'geog': Geography('POINT', srid=4326)})
    conn.close()

but this returns an error:

 (psycopg2.errors.RaiseException) find_srid() - could not find the corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS table?  Is there an uppercase/lowercase mismatch?
CONTEXT:  PL/pgSQL function find_srid(character varying,character varying,character varying) line 17 at RAISE

[SQL: SELECT Find_SRID('public', ' my_data', 'geog');]

The column names and types match exactly and in PostgreSQL, the geog column is of type geography:

CREATE TABLE public.my_data (
    
    id   SERIAL PRIMARY KEY,
    geog GEOGRAPHY(Point, 4326) NOT NULL,
    ts TIMESTAMP NOT NULL
    
)

What is the correct way to insert geodataframes with GEOGRAPHY data type into PostgreSQL?

3

The PL/pgSQL function is looking for a SRID in varchar format, and you have your srid as an integer. Should work if you change it to this:

with engine.connect() as conn:
    gdf.to_postgis('my_data', con=conn, if_exists='append', index=False, dtype={'geog': Geography('POINT', srid='4326')})
    conn.close()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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