Difficulty: intermediate
Estimated Time: 20 minutes

You may already be familiar with how to load data into "standard" PostgreSQL tables. Importing spatial data in particular is a little different: you can certainly still use Postgres commands like INSERT or \copy, but you may also want to use special utilities to handle the import.

If you have access to desktop GUIs, and deal with spatial data on a regular basis, QGIS is a free and open source desktop client you might use to easily import spatial data into a PostGIS database. But in case you can't use a tool like QGIS, there are command-line options available. Using the command line can also come in very handy for scripting and doing batch processes with your data files.

This course will focus on importing spatial data into PostGIS via the command line. We'll be dealing with GIS vector data but in case you're not aware, there is raster data as well.

In addition to psql, we'll be trying out shp2pgsql and ogr2ogr. If you're not familiar with psql, we strongly recommend checking out the Intro to psql course first before you proceed.

This course also assumes that you've completed the previous courses in the PostGIS series:

  1. Quick Introduction to PostGIS
  2. Geometries
  3. Geography

The data in this course is taken from the City of Tampa Open Data GeoHub. The data was downloaded in a few different GIS formats so you can see how you might choose or use an import tool depending on the format your spatial data is in.

PostGIS has already been installed in this environment. We'll mostly be using the following credentials to log in to Postgres:

  • username: groot
  • password: password (We've set up the environment so that you can log in via psql without having to manually enter the password)
  • database: tampa
    We'll log in as a superuser in one instance (to run CREATE EXTENSION). Otherwise, it's best practice to avoid the superuser role when possible as it can bypass almost every access restriction in the database!

Before we dive into PostGIS, let's also take a quick look at the background behind projections, and why it matters when it comes to importing spatial data. If you're new to spatial work, this will be useful context to have, even outside of PostGIS.

Congrats! We've covered a lot in this lesson. We've seen multiple ways to import spatial data on the command line, and hopefully you now have a good idea how to use one method over another for a particular scenario.

If you're interested in reviewing different methods for loading (non-spatial) data into Postgres in general, feel free to check out this course.

We also started a discussion on projections, but the next course in this series contains even more technical details and examples. Enjoy!

Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month._

Loading Spatial Data into PostGIS

Step 1 of 10

Spatial Reference Systems

Spatial data allows you to model and position things, typically on the Earth's surface. But a value like "POINT(-103.771555 44.967244)" is practically meaningless without a spatial reference system (SRS). (You may also come across the term "coordinate system" used interchangeably in GIS.)

A spatial reference system is a framework for understanding a set of spatial data. It provides information such as units of measurement (e.g. degrees, meters, feet); whether the data represents a location on the Earth represented as a sphere or projected out on a flat surface, like a map; and, if it's on a projection, how the "flattening" is done. So, as you may already know or have guessed, a spatial value could be valid across different SRS's, but end up meaning entirely different things in each case.

Spatial reference systems and PostGIS

PostGIS comes with a list of spatial reference systems -- you can take a look at it by querying the spatial_ref_sys table. First, we'll log in to Postgres as user groot, toggle on extended display in the psql shell, then execute a SELECT query to return a single record (srid = 4326).

psql -U groot -h localhost workshop
\x

SELECT * FROM spatial_ref_sys WHERE srid = 26918;

In addition to having a frame of reference for a given set of spatial data, we also need to assign an SRS to ensure that different datasets can "work" together. You may be using various reference systems across your database but your functions and queries will still work because PostGIS understands how to process your data.