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
\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
you're not familiar with
psql, we strongly recommend checking out the Intro to
first before you proceed.
This course also assumes that you've completed the previous courses in the PostGIS series:
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:
password(We've set up the environment so that you can log in via
psqlwithout having to manually enter the password)
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
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
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.