How to Connect GeoPandas to PostGIS

Problem statement

Data in PostGIS, analysis in Python: the connection between them is a SQLAlchemy engine, read_postgis and to_postgis, and the details — driver, URL, geometry column, SRID — decide whether a round trip comes back the way it went in.

This page covers the Python side of that workflow: connecting to PostGIS, loading data into a GeoDataFrame, and saving results back to the database. It does not cover PostgreSQL administration or PostGIS installation. Every example was run against a throwaway PostGIS 3.4 database in Docker, holding OpenStreetMap roads and land use for Rutland from Geofabrik, with GeoPandas 1.1, SQLAlchemy 2.1, psycopg 3 and GeoAlchemy2 0.20.

Quick answer

The usual workflow to connect GeoPandas to PostGIS is:

1. Create a PostgreSQL connection with SQLAlchemy
2. Use geopandas.read_postgis() to read spatial data into a GeoDataFrame
3. Use GeoDataFrame.to_postgis() to write results back
4. Verify the geometry column name and CRS/SRID before reading or writing

import geopandas as gpd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg://user@localhost:55433/gis")      # password from PGPASSWORD

roads = gpd.read_postgis("SELECT osm_id, fclass, name, geom FROM roads WHERE fclass = 'primary'",
                         engine, geom_col="geom")
print(len(roads), "primary road segments,", roads.crs)

roads.to_postgis("primary_roads", engine, if_exists="replace", index=False)

56 primary road segments, EPSG:4326

to_postgis() needs the GeoAlchemy2 package installed as well as SQLAlchemy and a driver.

Read and write round trip

Prerequisites, a connection, reading and writing

What you need before connecting GeoPandas to PostGIS

Required Python libraries

You need:

• geopandas
• sqlalchemy
• geoalchemy2, which to_postgis() uses to create the geometry column
• a PostgreSQL driver: psycopg (version 3) or the older psycopg2

Install them with pip:

pip install geopandas sqlalchemy geoalchemy2 "psycopg[binary]"

or, for the older driver, psycopg2-binary in place of "psycopg[binary]". On the database side, the database must have the PostGIS extension enabled (CREATE EXTENSION postgis;).

Required database details

Before you connect, collect the host, port, database name, user name and password, and for each table its schema, the name of its geometry column and its SRID. The geometry column name depends on what loaded the table — GeoPandas writes whatever the GeoDataFrame calls it, ogr2ogr writes wkb_geometry, shp2pgsql writes geom — so do not assume it.

Create a PostGIS database connection in Python

Build a SQLAlchemy connection string

A PostgreSQL SQLAlchemy URL names the driver, the credentials, the server and the database:

from sqlalchemy import URL, create_engine

engine = create_engine("postgresql+psycopg://user@localhost:55433/gis")
print(engine.url)

postgresql+psycopg://user@localhost:55433/gis

With psycopg2 the scheme is postgresql+psycopg2://. The URL here has no password: psycopg is a libpq client, so it takes the password from the PGPASSWORD environment variable or a ~/.pgpass file, and the password never appears in code or in a printed URL.

If you do put a password in the URL, special characters break it. A password containing @, : or / is read as part of the host and port:

password = "p@ss:w/rd#1"                     # a real role with this password, created by the setup
raw = f"postgresql+psycopg://analyst:{password}@localhost:55433/gis"
try:
    create_engine(raw).connect()
except ValueError as err:
    print("raw URL:", type(err).__name__, err)

safe = URL.create("postgresql+psycopg", username="analyst", password=password,
                  host="localhost", port=55433, database="gis")
with create_engine(safe).connect() as conn:
    print("URL.create: connected as", conn.exec_driver_sql("SELECT current_user").scalar())

raw URL: ValueError invalid literal for int() with base 10: 'w'
URL.create: connected as analyst

The error does not mention the password at all — it is SQLAlchemy failing to read w as a port number. URL.create() escapes each part for you; urllib.parse.quote_plus() on the password works too.

Create an engine and test the connection

create_engine() does not connect; the first query does. Test with something small before reading spatial data:

from sqlalchemy import text

with engine.connect() as conn:
    print(conn.execute(text("SELECT postgis_lib_version()")).scalar())
    print(conn.execute(text("SELECT count(*) FROM roads")).scalar(), "rows in roads")

3.4.3
7292 rows in roads

postgis_lib_version() fails if the PostGIS extension is not installed in this database, which separates "cannot connect" from "connected to the wrong database".

Read PostGIS data into a GeoDataFrame

Read records from a spatial table with SQL

geopandas.read_postgis() reads the result of a SQL query:

roads = gpd.read_postgis("SELECT * FROM public.roads", engine, geom_col="geom")
print(len(roads), roads.crs, roads.geometry.name)
print(roads["fclass"].value_counts().head(4).to_dict())

7292 EPSG:4326 geom
{'service': 2597, 'footway': 1273, 'residential': 1217, 'unclassified': 543}

The CRS comes from the SRID stored in the geometries, so a correctly loaded table needs no crs=.

Load filtered records with a SQL query

It is usually better to filter in SQL than to load the whole table and filter in pandas — only the matching rows cross the connection:

primary = gpd.read_postgis("SELECT osm_id, name, fclass, geom FROM public.roads WHERE fclass = 'primary'",
                           engine, geom_col="geom")
print(len(primary), "of", len(roads), "rows transferred")

56 of 7292 rows transferred

Use a parameterized query

For reusable scripts, pass values as parameters rather than formatting them into the SQL string:

from sqlalchemy import text

query = text("SELECT osm_id, name, fclass, geom FROM public.roads WHERE fclass = :road_class")
secondary = gpd.read_postgis(query, engine, geom_col="geom", params={"road_class": "secondary"})
print(len(secondary), "secondary road segments")

106 secondary road segments

Set the geometry column and CRS correctly

geom_col= must name the geometry column in the query's result. The land-use table was loaded with ogr2ogr, which calls it wkb_geometry:

for geom_col in ["geom", "wkb_geometry"]:
    try:
        landuse = gpd.read_postgis("SELECT ogc_fid, fclass, wkb_geometry FROM public.landuse", engine, geom_col=geom_col)
        print(geom_col, "->", len(landuse), "rows,", landuse.crs)
    except ValueError as err:
        print(geom_col, "->", err)

geom -> Query missing geometry column 'geom'
wkb_geometry -> 2405 rows, EPSG:4326

If the SRID is missing in the database (stored as 0), pass crs= to read_postgis(); that labels the result and does not transform anything. Use to_crs() when you need the coordinates in a different CRS.

Write a GeoDataFrame back to PostGIS

Save a new table to PostGIS

processed = primary.to_crs(27700)                       # British National Grid, metres
processed["length_m"] = processed.length.round(1)
processed.to_postgis("primary_roads_bng", engine, schema="public", if_exists="replace", index=False)

with engine.connect() as conn:
    print(conn.execute(text("SELECT type, srid FROM geometry_columns WHERE f_table_name = 'primary_roads_bng'")).one())
    print(conn.execute(text("SELECT indexname FROM pg_indexes WHERE tablename = 'primary_roads_bng'")).scalars().all())

('LINESTRING', 27700)
['idx_primary_roads_bng_geom']

The table's SRID is taken from the GeoDataFrame's CRS, and to_postgis() creates a GiST spatial index on the geometry column as part of creating the table.

Replace or append to an existing table

The if_exists argument controls write behaviour:

• fail (the default): raise an error if the table already exists
• replace: drop and recreate the table — including its indexes, constraints and grants
• append: add rows to the existing table

Appending checks the CRS against the table's SRID and refuses a mismatch:

try:
    primary.head(3).to_postgis("primary_roads_bng", engine, if_exists="append", index=False)
except ValueError as err:
    print("ValueError:", err)

processed.head(3).to_postgis("primary_roads_bng", engine, if_exists="append", index=False)
with engine.connect() as conn:
    print(conn.execute(text("SELECT count(*) FROM primary_roads_bng")).scalar(), "rows after appending 3")

ValueError: The CRS of the target table (EPSG:27700) differs from the CRS of current GeoDataFrame (EPSG:4326).
59 rows after appending 3

Check that geometries were written correctly

After writing, read a sample back and compare:

check = gpd.read_postgis("SELECT osm_id, length_m, geom FROM primary_roads_bng", engine, geom_col="geom")
print(check.crs, check.geom_type.unique().tolist())
print("lengths match:", (check.length.round(1) == check["length_m"]).all())

EPSG:27700 ['LineString']
lengths match: True

Handle CRS before writing

Before writing, make sure the GeoDataFrame's CRS describes its coordinates. set_crs() labels coordinates that are already in a CRS; to_crs() transforms them. A GeoDataFrame with no CRS is written with SRID 0, which every client then has to guess.

Common issues when connecting GeoPandas to PostGIS

Connection errors

The message tells you which part of the URL is wrong:

from sqlalchemy.exc import OperationalError

for label, url in [("wrong password", "postgresql+psycopg://user:wrong@localhost:55433/gis"),
                   ("wrong port", "postgresql+psycopg://user@localhost:55499/gis"),
                   ("wrong database", "postgresql+psycopg://user@localhost:55433/gisdb")]:
    try:
        with create_engine(url).connect():
            pass
    except OperationalError as err:
        print(f"{label:15}", str(err.orig).split("failed: ")[-1].strip())

wrong password  FATAL:  password authentication failed for user "user"
wrong port      Connection refused
	Is the server running on that host and accepting TCP/IP connections?
wrong database  FATAL:  database "gisdb" does not exist

"Connection refused" means nothing is listening on that host and port — the server is down, the port is wrong, or a firewall is in the way. The two FATAL messages come from a server that answered.

Geometry column not found

read_postgis() raises ValueError: Query missing geometry column '<name>' when the query result has no column of that name — either the SELECT left it out or geom_col= names the wrong column (the wkb_geometry example above).

CRS or SRID mismatches

Symptoms include data drawing in the wrong place or layers not aligning in QGIS. Typical causes are a GeoDataFrame written with no CRS (SRID 0), or a table whose SRID does not describe its coordinates. to_postgis(..., if_exists="append") refuses a CRS that differs from the table's, so a mismatch inside one table usually comes from another tool.

Table write failures

Common write problems are missing permissions, columns holding Python objects PostgreSQL has no type for (lists, dicts), and geometry types the table does not accept. GeoPandas writes a layer that mixes Polygon and MultiPolygon into a column of the generic GEOMETRY type; ogr2ogr refuses the same data unless told to promote it (see Mixed geometry types below).

Notes for production and automation workflows

Keep credentials out of scripts

Use PGPASSWORD or a ~/.pgpass file, which libpq-based drivers read by themselves, or a secret manager. Keep them out of version control.

Use SQL filters to reduce data transfer

For large tables, filter in SQL first — with a spatial predicate such as ST_Intersects(geom, ST_MakeEnvelope(...)) for an area — so the database can use its spatial index and only the rows you need are transferred.

Validate geometries before writing

In batch workflows, check for null geometries, invalid geometries, unexpected geometry types and a missing CRS before the write, when the error is still cheap to fix.

Reading only the features in an area

The data used on this page

The setup starts a throwaway PostGIS 3.4 in Docker on port 55433 (database gis, user user, password guide, set in PGPASSWORD) and loads two layers from Geofabrik's OpenStreetMap shapefile extract for Rutland: the roads with to_postgis(), as a table roads with a geometry column geom, and the land use with ogr2ogr, as a table landuse with GDAL's default column name wkb_geometry. It also creates the role analyst, whose password contains @, :, / and #. The extract is rebuilt daily, so counts drift a little.

import os
import subprocess
import time

import geopandas as gpd
import requests
from sqlalchemy import create_engine, text

os.environ.update(PGPORT="55433", PGPASSWORD="guide")
if os.system("docker inspect guide-postgis >/dev/null 2>&1") != 0:
    os.system("docker run -d --name guide-postgis -e POSTGRES_USER=user -e POSTGRES_PASSWORD=guide "
              "-e POSTGRES_DB=gis -p 127.0.0.1:55433:5432 postgis/postgis:16-3.4 >/dev/null")
    time.sleep(10)
engine = create_engine("postgresql+psycopg://user@localhost:55433/gis")

if not os.path.exists("rutland.zip"):
    reply = requests.get("https://download.geofabrik.de/europe/united-kingdom/england/rutland-260101-free.shp.zip",
                         timeout=300)
    reply.raise_for_status()
    open("rutland.zip", "wb").write(reply.content)

roads = gpd.read_file("rutland.zip", layer="gis_osm_roads_free_1").rename_geometry("geom")
roads[["osm_id", "fclass", "name", "geom"]].to_postgis("roads", engine, if_exists="replace", index=False)
subprocess.run(["ogr2ogr", "-f", "PostgreSQL", "PG:host=localhost port=55433 dbname=gis user=user password=guide",
                "/vsizip/rutland.zip", "gis_osm_landuse_a_free_1", "-nln", "landuse", "-overwrite",
                "-nlt", "PROMOTE_TO_MULTI"], check=True)
with engine.begin() as conn:
    conn.execute(text("DROP TABLE IF EXISTS primary_roads, primary_roads_bng"))
    conn.execute(text("DO $$ BEGIN IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'analyst') "
                      "THEN CREATE ROLE analyst LOGIN; END IF; END $$"))
    conn.execute(text("ALTER ROLE analyst PASSWORD 'p@ss:w/rd#1'"))
    print(conn.execute(text("SELECT f_table_name, f_geometry_column, type, srid FROM geometry_columns "
                            "WHERE f_table_name IN ('roads', 'landuse') ORDER BY 1")).all())

[('landuse', 'wkb_geometry', 'MULTIPOLYGON', 4326), ('roads', 'geom', 'LINESTRING', 4326)]

Example 1: Read only the features in an area

from sqlalchemy import text

query = text("""
    SELECT osm_id, name, fclass, geom
    FROM roads
    WHERE ST_Intersects(geom, ST_MakeEnvelope(:west, :south, :east, :north, 4326))
""")
oakham = gpd.read_postgis(query, engine, geom_col="geom",
                          params={"west": -0.745, "south": 52.66, "east": -0.715, "north": 52.68})
print(len(oakham), "road segments in the box around Oakham;", oakham["fclass"].value_counts().head(3).to_dict())

with engine.connect() as conn:
    plan = conn.execute(text("EXPLAIN " + str(query)), {"west": -0.745, "south": 52.66, "east": -0.715, "north": 52.68})
    print([line for (line,) in plan if "Index" in line][:1])

1001 road segments in the box around Oakham; {'residential': 300, 'footway': 292, 'service': 273}
['Index Scan using idx_roads_geom on roads  (cost=0.15..20.67 rows=1 width=128)']

The query plan shows PostgreSQL using the GiST index that to_postgis() created, so the database reads only the candidate rows.

Example 2: The same mixed layer, written two ways

Rutland's land use has 2,461 polygons and 4 multipolygons. to_postgis() and ogr2ogr treat that differently:

landuse = gpd.read_file("rutland.zip", layer="gis_osm_landuse_a_free_1")
print(landuse.geom_type.value_counts().to_dict())
landuse.to_postgis("landuse_gpd", engine, if_exists="replace", index=False)

strict = subprocess.run(["ogr2ogr", "-f", "PostgreSQL", "PG:host=localhost port=55433 dbname=gis user=user password=guide",
                         "/vsizip/rutland.zip", "gis_osm_landuse_a_free_1", "-nln", "landuse_strict", "-overwrite"],
                        capture_output=True, text=True)
print("ogr2ogr exit code", strict.returncode, "|", strict.stderr.splitlines()[0])

with engine.connect() as conn:
    print(conn.execute(text("SELECT f_table_name, type FROM geometry_columns "
                            "WHERE f_table_name IN ('landuse', 'landuse_gpd') ORDER BY 1")).all())

{'Polygon': 2401, 'MultiPolygon': 4}
ogr2ogr exit code 1 | Warning 1: Geometry to be inserted is of type Multi Polygon, whereas the layer geometry type is Polygon.
[('landuse', 'MULTIPOLYGON'), ('landuse_gpd', 'GEOMETRY')]

GeoPandas declares the column as the generic GEOMETRY and stores both kinds; ogr2ogr declares POLYGON from the shapefile's header and fails on the first multipolygon, until -nlt PROMOTE_TO_MULTI makes every row a MULTIPOLYGON. A generic GEOMETRY column is valid, but some clients handle it less well than a single declared type.

SQLAlchemy handles the connection

GeoPandas does not manage the database connection by itself. The connection is handled by SQLAlchemy and a PostgreSQL driver such as psycopg2 or psycopg. Once the connection is available, GeoPandas can read query results into a GeoDataFrame with read_postgis() and write them back with to_postgis().

PostGIS stores geometry data in PostgreSQL tables with spatial metadata such as geometry type and SRID. When you read those records into GeoPandas, the geometry column becomes the active geometry in a GeoDataFrame, which you can then filter, reproject, validate, and export in Python.

Geometry column names, schemas and large tables

Non-default geometry column names

Not every PostGIS table uses geom. ogr2ogr writes wkb_geometry, GeoPandas writes whatever the GeoDataFrame's geometry is called (geometry unless renamed), and ArcGIS uses shape. Pass the actual name with geom_col=, or look it up in geometry_columns.

Working with schemas other than public

If your table is in another schema, include it in the SQL (SELECT ... FROM staging.parcels) and pass schema="staging" to to_postgis().

Large tables and performance limits

Very large spatial tables can be slow to load into memory. Use SQL filters, select only the columns you need, and read in pieces with chunksize= if the result will not fit at once.

Mixed geometry types in one table

A layer mixing Polygon and MultiPolygon writes from GeoPandas into a generic GEOMETRY column; the same layer loaded with ogr2ogr fails unless you pass -nlt PROMOTE_TO_MULTI (Example 2). If downstream tools need a single type, promote before writing.

Internal links

If you need background first, read What Is PostGIS and How It Works With Python.

For a related file-based workflow, see How to Read a Shapefile in Python with GeoPandas.

If you need to change coordinate systems before saving data, see How to Reproject Spatial Data in Python (GeoPandas).

If your database connection fails, use How to Fix GeoPandas PostGIS Connection Errors.

FAQ

Do I need SQLAlchemy to connect GeoPandas to PostGIS?

For writing, yes: to_postgis() needs a SQLAlchemy engine and the GeoAlchemy2 package. read_postgis() also accepts a SQLAlchemy engine or connection, which is the usual choice.

Can GeoPandas read a custom SQL query instead of a full table?

Yes. geopandas.read_postgis() accepts a full SQL query, which is usually better than loading an entire table.

Can I append new records from a GeoDataFrame to an existing PostGIS table?

Yes. Use to_postgis(..., if_exists="append"), but make sure the schema, geometry type, and CRS match the target table.

Why does my PostGIS data load with the wrong CRS?

Usually because the table SRID is missing, incorrect, or does not match the actual coordinates. Check both the database SRID and gdf.crs before processing, and pass crs= explicitly to read_postgis() if needed.

How do I read only the rows within a bounding box from PostGIS?

Filter in SQL using a PostGIS spatial predicate such as ST_Intersects against an envelope, for example WHERE ST_Intersects(geom, ST_MakeEnvelope(xmin, ymin, xmax, ymax, 4326)). Doing the filter in the database transfers far less data than loading the full table and clipping in Python.

Can I write a GeoDataFrame to a specific SRID in PostGIS?

to_postgis() writes whatever CRS the GeoDataFrame already has, so reproject with to_crs() before writing. The table's SRID is derived from gdf.crs: the primary roads reprojected to EPSG:27700 produced a table with SRID 27700.

Does to_postgis() create a spatial index automatically?

Yes, when it creates the table: GeoAlchemy2 adds a GiST index named idx_<table>_<geometry column>, as the pg_indexes check above showed. if_exists="replace" drops and recreates it with the table. append to a table created some other way does not add one, so check pg_indexes there.

How can I append rows to PostGIS without dropping the existing table?

Use to_postgis(..., if_exists="append"). This keeps the current table and adds your rows. A CRS that differs from the table's SRID is refused with a ValueError; columns and geometry type must also fit the table.