SQL Basics for Spatial SQL: Importing Data to PostGIS

If you are looking for help loading a variety of geospatial data into PostGIS, then you have landed in the right place. This is a guide for my video below with further tutorials and guides. Special shout out to the team at Crunchy Data for their fantastic post on the topic.

The Spatial SQL Book – Available now!

Check out my new book on Spatial SQL with 500+ pages to help you go from SQL novice to spatial SQL pro.

shp2pgsql

This is a command-line function that comes with PostGIS that allows you to import Shapefiles into a PostGIS database directly.

Example

shp2pgsql -s 4326 -I \
./Parking_Garages_and_Lots.shp \
> parking_garages_lots.sql

# Then run...

psql -U user -h localhost \
-d states -f states.sql

# Or all in one line

shp2pgsql -s 4326 \
-I ./states.shp \
| psql -U user -h localhost states

Resources

ogr2ogr

Another way to do this is to use OGR2OGR, or the vector to vector file translation toolkit from GDAL that allows you to translate one of many different file types into a SQL file and also import it directly to your database.

Example

ogr2ogr \
-f PostgreSQL PG:"host=localhost user=kat password=password \
dbname=states" ./states.geojson \
-nln states -lco GEOMETRY_NAME=geom

Resources

PostgreSQL COPY

You can also use the native COPY command for loading CSV data directly into your database, although you will need to use GDAL and OGR2OGR to turn your data into a CSV if it is not already.

Example

# Turn your files into a CSV

ogr2ogr -f CSV file.csv file.shp

# Create your new table in the database

CREATE TABLE states (
    geom GEOMETRY,
    name TEXT)

# COPY the file

COPY states FROM './file.csv' (DELIMITER ' ,')

Resources

QGIS

If you are not as familiar with the command line and want a familiar interface I recommend using QGIS with your database. It is super simple and gives you a familiar desktop environment to upload your data.

Example

Resources

GeoPandas

Finally, you can use GeoPandas to not only read data from PostGIS but also import your GeoDataFrames into a PostGIS database.

Example

from sqlalchemy import create_engine

engine = create_engine("postgresql://user:pass@host:5432/db")

gdf.to_postgis("my_table", engine)

Resources