3

I'm trying to write a GeoDataFrame containing some POLYGON Z features directly to a PostGIS table which already has the right structure, especially a PolygonZ geometry column. This works fine:

G.to_postgis('test', engine, if_exists='append')

In a first attempt, psycopgg2 had trouble finding the SRID:


InternalError: (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', 'test', 'geometry');]
(Background on this error at: http://sqlalche.me/e/2j85)

This was because the name of my the geometry field in my GeoDataFrame didn't match the one in PostGIS!
They have to match.

The test table SQL code looks like this in pgAdmin:

-- Table: public.test

-- DROP TABLE public.test;

CREATE TABLE IF NOT EXISTS public.test
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( 
        INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 
    ),
    geometry geometry(PolygonZ,2056)
)

TABLESPACE pg_default;

ALTER TABLE public.test
    OWNER to postgres;

Now, if I write to a table which doesn't already exist, GeoPandas will create it:

G.to_postgis('test2', engine, if_exists='replace') #table 'test2' doesn't already exist

That's cool, but then, the SQL code associated to this table looks like:

-- Table: public.test2

-- DROP TABLE public.test2;

CREATE TABLE IF NOT EXISTS public.test2
(
    geometry geometry(PolygonZ,2056)
)

TABLESPACE pg_default;

ALTER TABLE public.test2
    OWNER to postgres;
-- Index: idx_test2_geometry

-- DROP INDEX public.idx_test2_geometry;

CREATE INDEX idx_test2_geometry
    ON public.test2 USING gist
    (geometry)
    TABLESPACE pg_default;

There is no id column (which I expected as the primary key constraint). It could have simply be taken from the GeoDataFrame index if such index also meet the two conditions of a RDB primary key: it is unique and it cannot be null.

This seems actually possible using the index option:

G.to_postgis('test2', engine, if_exists='replace', index=True)

which leads to:

-- Table: public.test2

-- DROP TABLE public.test2;

CREATE TABLE IF NOT EXISTS public.test2
(
    index bigint,
    geometry geometry(PolygonZ,2056)
)

TABLESPACE pg_default;

ALTER TABLE public.test2
    OWNER to postgres;
-- Index: idx_test2_geometry

-- DROP INDEX public.idx_test2_geometry;

CREATE INDEX idx_test2_geometry
    ON public.test2 USING gist
    (geometry)
    TABLESPACE pg_default;
-- Index: ix_public_test2_index

-- DROP INDEX public.ix_public_test2_index;

CREATE INDEX ix_public_test2_index
    ON public.test2 USING btree
    (index ASC NULLS LAST)
    TABLESPACE pg_default;

but as the index option is a boolean which default value is True, I would have expected the exact same behavior by specifying index=True or not.
Indeed, the index column is created only when this option is manually specified.
If it's not, there is only the geometry column (no index).

But even when the index field is present, it does not appear to be a primary key as when the table was created manually.

Hence my question: is it possible to create a new table with a primary key corresponding to the index of the GeoDataFrame when using GeoDataFrame.to_postgis() method? I cannot figure it out on the documentation.

Versioning:

OS: Ubuntu 18.04
Python: 3.6.9

geopandas:   0.9.0
psycopg2:    2.9.1 (dt dec pq3 ext lo64)
sqlalchemy:  1.3.13
geoalchemy2: 0.9.2

PostgreSQL: 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu

PostGIS full version:

POSTGIS="3.1.2 cbe925d" [EXTENSION]
 PGSQL="130"
 GEOS="3.7.1-CAPI-1.11.1 27a5e771"
 PROJ="Rel. 5.2.0, September 15th, 2018"
 LIBXML="2.9.4"
 LIBJSON="0.12.1"
 LIBPROTOBUF="1.3.1"
 WAGYU="0.5.0 (Internal)" TOPOLOGY

1 Answer 1

0

You won't figure this problem out by reading the documentation. In fact, I don't think there is a solution to this problem. GeoDataFrame.to_postgis() converts the GeoDataFrame to a regular pandas DataFrame and then calls DataFrame.to_sql(). The to_sql method doesn't seem to support creation of primary keys (see here or here). And there also doesn't seem to be much activity on the related github issue.

I took the manual creation of a primary key with an alter table .. add primary key .. statement as a last resort.

1
  • I don't know if it's related, but I seem to be bumping into something similar in the could not find the corresponding SRID error. The SQL in the error SELECT Find_SRID('public', 'Peng', 'geometry'); is looking for a table that doesn't exist. In my database is a 'geom' column. Running the same query in PGADMIN finds the SRID without any issues.
    – nizz0k
    Jan 12 at 20:57

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.