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.

Buy now!

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

Loading Data into PostGIS: An Overview

There are a lot of ways to load data into a PostgreSQL/PostGIS database and it’s no different with spatial data. If you’re new to PostGIS, you’ve come to the right place. In this blog post, I’ll outline a few free, open source tools you can use for your spatial data import needs.

Chapter 4. Data Management

4.1. Spatial Data Model

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

Loading Data into PostGIS: An Overview

There are a lot of ways to load data into a PostgreSQL/PostGIS database and it’s no different with spatial data. If you’re new to PostGIS, you’ve come to the right place. In this blog post, I’ll outline a few free, open source tools you can use for your spatial data import needs.

Using shp2pgsql instead of ogr2ogr to import shapefile to PostGIS?

I may very well be doing something wrong here, but: If I import some shapefile to a PostGIS database using shp2pgsql, I have to first figure out the SRID/EPSG of that shapefile. I think this is, at

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

Loading Data into PostGIS: An Overview

There are a lot of ways to load data into a PostgreSQL/PostGIS database and it’s no different with spatial data. If you’re new to PostGIS, you’ve come to the right place. In this blog post, I’ll outline a few free, open source tools you can use for your spatial data import needs.

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

Loading Data into PostGIS: An Overview

There are a lot of ways to load data into a PostgreSQL/PostGIS database and it’s no different with spatial data. If you’re new to PostGIS, you’ve come to the right place. In this blog post, I’ll outline a few free, open source tools you can use for your spatial data import needs.

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

geopandas.GeoDataFrame.to_postgis — GeoPandas 0.10.2+0.g04d377f.dirty documentation

Upload GeoDataFrame into PostGIS database.