How to upload Shapefiles to PostGIS with Python, Geopandas and SQLAlchemy - Tutorial

In our research for new geospatial tools in Python and better ways to deal with geospatial data we found that complex or full featured processes are already included in the common spatial libraries as Geopandas. We have developed an applied example to upload point / line / polygon ESRI Shapefile to a Postgres / Postgis database with Python, Geopandas and SQL Alchemy in a few lines of code. Besides, we have simplified the procedure to run a Postgres database inside the Hakuchik Docker image fully connected to QGIS.

This tutorial requires Docker on your computer. You can install Docker in Windows following this tutorial.

Tutorial

Code

#import required libraries
from sqlalchemy import create_engine
import geopandas as gpd
import matplotlib.pyplot as plt

Connect with Postgresql

engine = create_engine('postgresql://gis:gis@localhost:5432/geodatabase')

Open shapefiles

#create geodataframes from all shapefiles
pointDf = gpd.read_file('../Shps/wellPoints.shp')
lineDf = gpd.read_file('../Shps/contoursLines.shp')
polyDf = gpd.read_file('../Shps/lakesPolygons.shp')

#sample of import geodataframes
pointDf.head()

	Bore	Elev	geometry
0	A. Isaac	976.579	POINT (575546.629 4820354.914)
1	A. Woodbridge	904.403	POINT (564600.367 4807827.447)
2	A.D. Watkins	965.698	POINT (558944.843 4789663.820)
3	A.L. Clark; 1	694.670	POINT (519259.006 4810958.617)
4	A.L. Clark; 2	1173.053	POINT (585351.150 4811459.503)

#you might need descartes, uncomment and run this line just one
#!pip install descartes
#a preview of the spatial data
fig, ax = plt.subplots(figsize=(12,8))
pointDf.plot(ax=ax,c='gold')
lineDf.plot(ax=ax,cmap='Oranges',alpha=0.5)
polyDf.plot(ax=ax,ec='royalblue')
plt.show()

Upload shapefiles

#points
pointDf.to_postgis('wellpoints',engine, index=True, index_label='Index')

#lines
lineDf.to_postgis('contourlines',engine, index=True, index_label='Index')

#polygons
polyDf.to_postgis('lakepolygons',engine, index=True, index_label='Index')

Input data

Input data for this tutorial in on this Github repository:

https://github.com/SaulMontoya/howToUploadShapefilestoPostGISwithPython