Adding GeoPandas Dataframe to PostGIS table?

Question

I have a simple GeoPandas Dataframe:

enter image description here

I would like to upload this GeoDataframe to a PostGIS table. I have a Database setup with the PostGIS extension already but can't seem to add this Dataframe as a table.

I have tried the following:

engine = <>
meta = MetaData(engine)
eld_test = Table('eld_test', meta, Column('id', Integer, primary_key=True), Column('key_comb_drvr', Text), 
 Column('geometry', Geometry('Point', srid=4326))) 
eld_test.create(engine) 
conn = engine.connect() 
conn.execute(eld_test.insert(), df.to_dict('records'))

• thecornman: I have tried the following: engine = <> # create table meta = MetaData(engine) eld_test = Table('eld_test', meta, Column('id', Integer, primary_key=True), Column('key_comb_drvr', Text), Column('geometry', Geometry('Point', srid=4326)) ) eld_test.create(engine) # DBAPI's executemany with list of dicts conn = engine.connect() conn.execute(eld_test.insert(), df.to_dict('records'))
• GISHuman: Welcome to GIS SE, please have a read of our tour! Could you edit your post to include your code posted in the comments?

Answer 1

Using Panda's to_sql method and SQLAlchemy you can store a dataframe in Postgres. And since you're storing a Geodataframe, GeoAlchemy will handle the geom column for you. Here's a code sample:

# Imports
from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import *
import pandas as pd
import geopandas as gpd
# Creating SQLAlchemy's engine to use
engine = create_engine('postgresql://username:password@host:socket/database')
geodataframe = gpd.GeoDataFrame(pd.DataFrame.from_csv('<your dataframe source>'))
#... [do something with the geodataframe]
geodataframe['geom'] = geodataframe['geometry'].apply(lambda x: WKTElement(x.wkt, srid=<your_SRID>)
#drop the geometry column as it is now duplicative
geodataframe.drop('geometry', 1, inplace=True)
# Use 'dtype' to specify column's type
# For the geom column, we will use GeoAlchemy's type 'Geometry'
geodataframe.to_sql(table_name, engine, if_exists='append', index=False, 
 dtype={'geom': Geometry('POINT', srid= <your_srid>)})

Worth noting that 'if_exists' parameter allows you to handle the way the dataframe will be added to your postgres table:

 if_exists = replace: If table exists, drop it, recreate it, and insert data.
 if_exists = fail: If table exists, do nothing.
 if_exists = append: If table exists, insert data. Create if does not exist.

• rovyko: Is there an opportunity to reproject here by specifying a different SRID than the one in the geometry column, or does the current SRID have to be used? Also what is the best way to get the integer SRID from the geometry column?
• Vilq: Why using this method I have sqlalchemy.exc.InvalidRequestError: Could not reflect: requested table(s) not available in Engine error?
• nate: keep in mind that this approach assumes that all your 'geoms' are actually populated with objects - so if None types (or some other non-geom) occupies any records, an error will be thrown. great approach provided by @Hamri Said!
• wfgeo: I think it is worth noting here that this will NOT work if you are using a psycopg2.connection instead of an sqlalchemy.engine object, even though in many other cases the two are effectively interchangeable.

Answer 2

As of recently, geopandas has a to_postgis method. Woohoo!

Note: you will need psycopg2-binary, sqlalchemy2, and geoalchemy2 installed.

import geopandas
from sqlalchemy import create_engine
# Set up database connection engine
engine = create_engine('postgresql://user:password@host:5432/')
# Load data into GeoDataFrame, e.g. from shapefile
geodata = geopandas.read_file("shapefile.shp")
# GeoDataFrame to PostGIS
geodata.to_postgis(
 con=engine,
 name="table_name"
)

• Tek Kshetri: Do you know about the CRS? how to add the CRS for the data?
• Brylie Christopher Oxley: Have you already set the CRS on the geodata? geopandas.org/en/stable/docs/user_guide/…
• Tek Kshetri: Perfect! Thank you for the solution.
• Nikhil VJ: I'm getting this error: 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 RAIS [SQL: SELECT Find_SRID('public', 'habitation', 'geometry');]
• Brylie Christopher Oxley: @NikhilVJ, it looks like PostGIS doesn't recognize the spatial reference for the data. What is the SRID of your source data?

Answer 3

I have also had the same question you've asked and have spent many, many days on it (more than I care to admit) looking for a solution. Assuming the following postgreSQL table with the postGIS extension,

postgres=> \d cldmatchup.geo_points;
Table "cldmatchup.geo_points"
Column | Type | Modifiers 
-----------+----------------------+------------------------------------------------------------------------
gridid | bigint | not null default nextval('cldmatchup.geo_points_gridid_seq'::regclass)
lat | real | 
lon | real | 
the_point | geography(Point,4326) | 
Indexes:
"geo_points_pkey" PRIMARY KEY, btree (gridid)

this is what I finally got working:

import geopandas as gpd
from geoalchemy2 import Geography, Geometry
from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import sessionmaker
from shapely.geometry import Point
from psycopg2.extensions import adapt, register_adapter, AsIs
# From http://initd.org/psycopg/docs/advanced.html#adapting-new-types but 
# modified to accomodate postGIS point type rather than a postgreSQL 
# point type format
def adapt_point(point):
 from psycopg2.extensions import adapt, AsIs
 x = adapt(point.x).getquoted()
 y = adapt(point.y).getquoted()
 return AsIs("'POINT (%s %s)'" % (x, y))
register_adapter(Point, adapt_point)
engine = create_engine('postgresql://<yourUserName>:postgres@localhost:5432/postgres', echo=False)
Session = sessionmaker(bind=engine)
session = Session()
meta = MetaData(engine, schema='cldmatchup')
# Create reference to pre-existing "geo_points" table in schema "cldmatchup"
geoPoints = Table('geo_points', meta, autoload=True, schema='cldmatchup', autoload_with=engine)
df = gpd.GeoDataFrame({'lat':[45.15, 35., 57.], 'lon':[-35, -150, -90.]})
# Create a shapely.geometry point 
the_point = [Point(xy) for xy in zip(df.lon, df.lat)]
# Create a GeoDataFrame specifying 'the_point' as the column with the 
# geometry data
crs = {'init': 'epsg:4326'}
geo_df = gpd.GeoDataFrame(df.copy(), crs=crs, geometry=the_point)
# Rename the geometry column to match the database table's column name.
# From https://media.readthedocs.org/pdf/geopandas/latest/geopandas.pdf,
# Section 1.2.2 p 7
geo_df = geo_df.rename(columns{'geometry':'the_point'}).set_geometry('the_point')
# Write to sql table 'geo_points'
geo_df.to_sql(geoPoints.name, engine, if_exists='append', schema='cldmatchup', index=False)
session.close()

I can't say if my database connection logic is the best since I basically copied that from another link and was just happy that I was able to successfully automap (or reflect) my existing table with the geometry definition recognized. I've been writing python to sql spatial code for only a few months, so I know there is much to learn.

Answer 4

I have a solution which is requires only psycopg2 and shapely (in addition geopandas of course). It is generally bad practice to iterate through (Geo)DataFrame objects because it is slow, but for small ones, or for one-off tasks, it will still get the job done.

Basically it works by dumping the geometry to WKB format in another column and then re-casts it to GEOMETRY type when inserting.

Note that you will have to create the table ahead of time with the right columns.

import psycopg2 as pg2
from shapely.wkb import dumps as wkb_dumps
import geopandas as gpd
# Assuming you already have a GeoDataFrame called "gdf"...
# Copy the gdf if you want to keep the original intact
insert_gdf = gdf.copy()
# Make a new field containing the WKB dumped from the geometry column, then turn it into a regular 
insert_gdf["geom_wkb"] = insert_gdf["geometry"].apply(lambda x: wkb_dumps(x))
# Define an insert query which will read the WKB geometry and cast it to GEOMETRY type accordingly
insert_query = """
 INSERT INTO my_table (id, geom)
 VALUES (%(id)s, ST_GeomFromWKB(%(geom_wkb)s));
"""
# Build a list of execution parameters by iterating through the GeoDataFrame
# This is considered bad practice by the pandas community because it is slow.
params_list = [
 {
 "id": i,
 "geom_wkb": row["geom_wkb"]
 } for i, row in insert_gdf.iterrows()
]
# Connect to the database and make a cursor
conn = pg2.connect(host=<your host>, port=<your port>, dbname=<your dbname>, user=<your username>, password=<your password>)
cur = conn.cursor()
# Iterate through the list of execution parameters and apply them to an execution of the insert query
for params in params_list:
 cur.execute(insert_query, params)
conn.commit()