Connecting QGIS to Postgres and PostGIS

QGIS, the Quantum Geographic Information System, is an open-source graphical user interface for map making. QGIS works with a wide variety of file types and has robust support for integrating with Postgres and PostGIS. Today I just wanted to step through getting QGIS connected to a Postgres database and the basic operations that let you connect the two systems.

Connecting QGIS to Postgres

Connecting QGIS to Postgres is very similar to any other GUI or application, you’ll need the database host, login, and password details. This is the same process for a local connection or remote one (like Crunchy Bridge). You’ll connect the first time through the Browser option listed PostgreSQL and Add New Connection.

By default, QGIS will store your passwords as plain text in a file. If you’re just working with a local database and don’t have anything special in there, that may not be a problem. But if you’re working with a larger production database shared by lots of users, you’ll want to opt for a higher level of protection for the password. In your PostgreSQL connections box, you’ll see a way to add Configurations for the password. Here you can create a master password, store your database credentials, and they’ll be encrypted and only decrypted with your master password.

Using QGIS to load data

QGIS is a great way to get spatial data into Postgres and PostGIS. You can use any file type supported by QGIS including vector types like shapefiles (shp), GeoJSON, and even csv files on your local machine. To load data into QGIS, you’ll first go to Layer —> Add Layer and choose the type of file you have.

For this sample I have a county map of the state of Kansas. Maps like this are often freely available for download from government agencies.

Once my layer is in, I can toggle on to show labels which will add any label data for your geometry.

Now that I have data in QGIS I can save this to a Postgres database. This will allow me to work with this data later. Go to the DB manager icon, and choose Import Layer.

There are several settings here, like choosing the primary key, the origin and destination SRID. QGIS will even suggest that adding an index for your geometry column is a good idea and will build an index in your database for you.

Loading data from PostGIS into QGIS

QGIS works both ways, so if you already have a dataset to work from, you can just use that data as your source. In that case, you’ll start from the Layer — Add Layer option. You’ll either need to specify the database connection you want, or add a new one here. You’ll be able to open all the tables in your database and choose which ones to add as a layer in your map viewer.

There's a good overview of PostGIS file loading on our blog.

File loading troubleshooting

Depending on the file you get, QGIS may or may not be super happy with it. You might see a warning icon next to your file. The main issues that QGIS will be warning you about are:

• There’s no spatial reference id

  The spatial reference ID is an important quirk when dealing with geospatial data. You can default to 4326 if you don’t have a better option.

  To find a spatial reference id, or see if it is set:

  SELECT ST_SRID(geom) FROM my_table_name LIMIT 1;
  

  And to update it:

  SELECT UpdateGeometrySRID('my_table_name','geom',4326);
  

• There’s no geometry column

  Assuming you have points, lines, or polygon data and it is just in the wrong data type, you can create a new column for the geometry and point your data to that new column.

  ALTER TABLE my_table_name
  ADD COLUMN geom geometry(Point, 4326);
  
  UPDATE my_table_name
  SET geom = ST_SetSRID(ST_MakePoint(my_column, my_column), 4326);
  

• There’s no primary key

  Relational databases rely on a primary key to tie other data together. If there’s already an id column, you can just create a primary key index on it like this. If there’s not a unique column like that, you might have to do a bit more work on the data to get this fixed.

  alter table my_table add primary key (id_column);
  

Writing and Saving SQL

One really cool feature of QGIS is that you can write SQL directly against your Postgres database and view the results as spatial geometries. You can save queries for use later as well. You can also use QGIS to create a layer based on your query results.

Here’s a sample query where I’m joining two data sources, one my geometry of Kansas counties that I loaded earlier. Second population data by county. I’m selecting just the geometry column and with the load option can have QGIS add that query result as a layer.

QGIS will also let you save a query as “view”. This is a database specific term that will save your query results as a table for use later. This can also be loaded as a layer in QGIS projects. There’s an overview of using views in the post Postgres Subquery Powertools. Views are a great idea if you’re using only a small subset of data in your QGIS map but you are storing a larger dataset as well.

Here’s an example of a map I made using 4 different query layers, one for each different population density.

Don’t forget that QGIS works in stacked layers, so your new SQL query layers will have to be on top of your base map or they won’t be visible.

Saving QGIS projects in Postgres

You can also save your QGIS project in your Postgres database. This is under the Project — Save to options. This can be a good idea if you want others on your team to have access to projects or if you don’t want your QGIS projects stored locally.

Final notes

There’s a great video of this and more from PostGIS Day 2020 called QGIS and PostGIS.

• You can use QGIS to load shape files and other file types into Postgres
• You can use QGIS to create maps from existing Postgres/PostGIS data sources
• You can write queries in QGIS against your Postgres data and show the results as geometry layers
• You can write join queries in QGIS and join your geometry fields with other attribute data or tables in your database
• You can save all of your project work for QGIS in the Postgres database