3 Easy Ways to import a shapefile into a PostgreSQL database

Photo by Annie Spratt on Unsplash

Before we begin, you must have an instance of PostgreSQL installed with the PostGIS extension. PGAdmin is a good database administration software that can be installed with PostgreSQL.

• Download PostgreSQL — https://www.postgresql.org/
• Install PostGIS — https://postgis.net/install/

We also need a dataset. In this post, we will use the University of Richmond’s Mapping Inequality dataset, which is a collection of polygon coordinates that trace redlined districts in the United States.

• redlining GIS dataset — https://dsl.richmond.edu/panorama/redlining/#loc=5/39.1/-94.58&text=downloads

1. Create the table and import the file using the shp2pgsql command line tool

First, create a database in PostgreSQL and enable PostGIS.

CREATE DATABASE "gis_db"
    WITH 
    OWNER = <dbuser>
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;
CREATE EXTENSION postgis;

Then, from the command line in the directory that contains the holc_ad_data.shp file (as well as the .proj and .dbf files), run this:

shp2pgsql -s 4326 -I holc_ad_data.shp redlining  \
| psql  -d gis_db -U <dbuser>

This creates a new table called ‘redlining’ and imports the data from the holc_ad_data.shp.

2. Python — Push the data using Geopandas and SQLAlchemy

This is a simple code solution that can be easily automated in a pipeline.

3. Push the data from the QGIS application

Last but not least, the GUI option. First, download and install QGIS on your local machine.