Geopandas PostGIS connection

Question

I recently started using Geopandas in python for some of my spatial work and am very pleased with it - I'm currently trying to read in PostGIS features and don't quite understand how to parameterize the database connection, and it didn't seem clear in the documentation:

GeoDataFrame.from_postgis(sql, con, geom_col='geom', crs=None, index_col=None, 
    coerce_float=True, params=None)

This is likely a very simple question, all I wanted to know is what needs to go in 'con' - I assume a string with database connection information? But in what format? Setting 'sql' seems straightforward. Any help greatly appreciated - thanks!

You can look in the pandas `read_sql` docs, which is used by geopandas: http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries — joris, Apr 23 '16 at 18:36

score 42 · Accepted Answer · edited Mar 04 '18 at 17:07

42

Example:

import geopandas as gpd

import psycopg2  # (if it is postgres/postgis)

con = psycopg2.connect(database="your database", user="user", password="password",
    host="your host")

sql = "select geom, x,y,z from your_table"

df = gpd.GeoDataFrame.from_postgis(sql, con, geom_col='geom' )

edited Mar 04 '18 at 17:07

Brian Burns

20,575
8
83
77

answered Nov 08 '16 at 18:38

Catalin

591
6
5

4

`psycopg2` is specific to postgres, `sqlalchemy.create_engine()` is generic and can handle connections to most DBMS – Hugh_Kelley Jul 29 '19 at 09:41

score 0 · Answer 2 · edited Jun 01 '23 at 22:35

It seems like the example above doesn't work any more because GeoPandas doesn't accept con = psycopg2.connect(...), and instead returns the following error:

UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(

It seems like sqlalchemy.create_engine() is the only way now.

Geopandas PostGIS connection

2 Answers2

Linked