By Jessica Kane
I presented some of this content at PostGIS Day 2020. A video is available here.
All organizations have data they need to manage. Most of that data can probably be tied to a location, meaning that a lot of value can be gained from using GIS software to visualize that data on a map and run spatial analyses on it. However, many organizations can’t afford to license proprietary GIS software, or they can only afford licenses for a few employees. This doesn’t have to be the case. By leveraging open source solutions, organizations can implement an enterprise GIS system and database with no licensing fees. The desktop software QGIS works seamlessly with the database PostgreSQL and its spatial extension PostGIS. By leveraging these tools, everyone in an organization can view and/or edit the data that drives the organization’s processes and decisions.
I start this article with some background information on QGIS and PostgreSQL and then dive into a step-by-step tutorial that walks you through how to set up a PostgreSQL/PostGIS database and connect it to QGIS. I also highlight several key features that make using QGIS with PostgreSQL/PostGIS a robust enterprise solution, allowing employees of an organization to easily access shared data, projects, and styling. There is so much more functionality then I can review here, so I link to additional resources throughout for you to explore.
Read on to see why QGIS and PostGIS really are a dynamic duo, and why both organizations and individuals should be using them together.
What is QGIS?
QGIS is a free and open source desktop GIS application. You can use it to view and edit vector data (points, lines, polygons) and raster data (images). It can work with a variety of file formats (shapefile, geopackage, etc) and databases (PostgreSQL, SQL Server, etc). QGIS is a powerful tool that includes geoprocessing and spatial analysis functions as well as cartography/styling capabilities. It’s a great GIS solution for organizations because it allows your organization to provide tools to anyone that needs them without worrying about ballooning license costs. QGIS also has a great ecosystem of plugins that are constantly being added to expand its functionality.
What is PostGIS?
PostGIS is a spatial extension for PostgreSQL, a free and open source relational database system. PostGIS allows spatial features (e.g. points) to be stored in tables with non-spatial attributes ( e.g. name). And because PostgreSQL is a relational database, you can build relationships between tables. PostGIS supports both vector and raster data and provides location queries in SQL such as buffer (e.g. buffer a certain distance from a feature like a point) and intersects (e.g. do features overlap?). PostGIS makes PostgreSQL the most fully-featured spatial database out there with support for 3D objects and queries, network topology, serving vector tiles, and more.
Using QGIS and PostGIS in an Enterprise Environment
PostGIS is powerful when installed locally for a GIS specialist, but it's power for an enterprise environment is that it allows users at an organization to be given their own PostgreSQL credentials, assigned roles, and given permissions. Admin users can give some users view-only permissions on all tables and give others edit permissions on certain tables. When users log in with their PostgreSQL credentials in QGIS, editing capabilities will be disabled for any data they only have view access for. Any number of users can be added and there are no licensing fees.
A Brief Overview of Schemas and Tables
There are many benefits of using a database to manage spatial data. A database like PostgreSQL/PostGIS is the backbone of an enterprise system, allowing for multiple users, permissions, and data back-ups. It also makes it much easier to organize and maintain data over time, especially if you have multiple GIS projects/maps that use the same spatial data.
Schemas make it easy to organize data into logical buckets. For example, in the database I use for the small projects I’m working on, I usually store all of the publicly available data that I’m using (e.g. county boundaries, cities) in the public schema. Then I create a schema for each project to store data specific to that project.
Below you can see that I’ve created a schema for the map of ghost towns in Otsego County, Michigan. The lumber industry was huge in Michigan in the 1800s. Many towns popped up that disappeared along with the timber. The Otsego County Historical Society is currently writing a book about these towns and wanted to put together a map. I’ll be using their data for examples throughout the rest of this article.
Each schema has its own tables. Tables allow you to store data in columns and rows. One of the columns for spatial data will be a geometry or geography column that stores the spatial information for each data point.
NOTE: Use a geometry column for data projected on a Cartesian plane (e.g. EPSG:3078 NAD83 / Michigan Oblique Mercator). Use a geography column for data specified with latitude/longitude (e.g. EPSG:4326 WGS84 which is used in GPS).
Tips for PostGIS Tables in QGIS
There are a few things you should keep in mind for your PostGIS tables which will make it easier to work with them in QGIS.
PostgreSQL will fold unquoted table/column names to lower case. If you forget this detail you might end up getting some annoying errors related to (what you thought should be) uppercase table names, so I recommend that you only use lowercase names and use underscores to separate words in your names. For example, historic_railroad.
It is good practice to have a primary key on each table and you will avoid issues in QGIS by following this standard. A primary key is a column that stores a unique identifier for each record. It's a good idea to set primary key columns to auto increment. You can do this in the QGIS DB Manager, which we’ll review in this article.
Download QGIS and PostgreSQL
Download and install QGIS
Navigate to the QGIS download page.
Select the download for the operating system on which you will be using QGIS. If you use the QGIS standalone installer, I recommend you download the long term release version.
Follow the instructions on the install wizard.
Download and install PostgreSQL
Navigate to the PostgreSQL download page.
Select the operating system on which you will be using PostgreSQL.
If you are using the Windows installer, check out this step-by-step guide from PostgreSQL Tutorial. Their site also has installation tutorials for MacOS and Linux. Make sure to ignore the tutorial and install the Stack Builder. You’ll need this to install PostGIS.
During the installation process, you’ll set a password for the postgres user. Make sure to remember this password as you’ll need it to connect to PostgreSQL/PostGIS.
During the installation process you’ll be prompted to launch Stack Builder. Do this and go through the wizard, making sure to select PostGIS to install it.
Create a database
Open PgAdmin. By default this database management tool (which was installed with PostgreSQL) will open in a browser window.
You’ll be prompted for the password you created during the installation process.
Expand Servers. Right click PostgreSQL > Create > Database
4. Fill in a name for your database and click Save
5. Expand Databases. Right click on your database and select Query Tool
6. Type the following into the Query Editor and click the Execute button (or click the keyboard shortcut F5).
Now when you expand the public schema in your database, you should see a spatial_ref_sys table.
NOTE: If you make a change to your database (i.e. add a table) and you don’t see the result in the PgAdmin browser, right click on your database and click Refresh.
It is easy to import shapefile data into PostGIS through QGIS, so first we need to connect to PostGIS in QGIS.
Connecting QGIS to PostGIS
There are a couple different places in QGIS where you can create a PostGIS connection. One is through the Data Source Manager which you can access through the toolbar:
Another is through the Browser which you can dock on the left or right side of QGIS:
1. In the Browser, right click on PostGIS and click New Connection
2. Fill in the Name, Host, Port, and Database. The Name is the name of the connection so you can find it again later. You can call it whatever you’d like. I usually use the name of the database. If you are working off your local machine, the Host will be localhost. The default Port for PostgreSQL is 5432.
There are two different types of authentication in QGIS: Basic and Configuration. You can type your PostgreSQL username and password (that you created during installation) into the Basic tab, and this will connect you to PostgreSQL. However, this will store your credentials as plain text in the QGIS project file. This means that anyone could open the QGIS project file in a text editor like Notepad and see your password. That is why it is recommended that you use an authentication configuration instead.
3. On the Configurations tab, click the green plus button.
4. If you don’t already have a master password, you’ll be prompted to create one. QGIS will ask you to enter this master password any time you are trying to access encrypted data (e.g. adding an authentication configuration). Once you enter the master password, by default it is cached until you quit QGIS, so you won’t be prompted for it again during your current session.
NOTE: Make sure to remember this master password as it is not retrievable. If you forget it, you’ll have to erase the entire authentication database in order to create a new master password.
5. Once you’ve created a master password, you’ll be prompted to create your authentication configuration for PostgreSQL. Fill in the Name (again this can be anything you’d like) and the Username/Password for PostgreSQL and click Save.
6. Now you can test your connection:
7. If you click the pencil button on the Configuration tab, you’ll see that QGIS created an Id for the configuration:
8. You can leave this as is if you’re working locally on your own database. However, if you are using PostGIS in an enterprise environment and multiple people are accessing the same database with QGIS, this ID field needs to be common throughout the organization.
9. Before you create your PostGIS connection, check two boxes in the bottom part of the dialog: ‘Also list tables with no geometry’ and ‘Allow saving/loading QGIS projects in the database’. We’ll get into these options later:
10. Click OK to create the connection.
11. Now you should be able to see your database in the Browser when you expand PostGIS:
NOTE: If you have issues connecting to PostGIS and receive this error: “Authentication method 10 not supported”, you may need to use a newer version of QGIS.
Managing Your QGIS Master Password
You can manage your master password by going to Settings > Options
On the Authentication tab, you can see a list of your authentication configurations. You can edit, add, and delete configurations here. You can also click Utilities to manage your master password including resetting it (you’ll need your old master password to do this), clearing cached configurations or master password, and erasing the authentication database (including all of your authentication configurations), which you’ll need to do if you forget your master password.
Working with PostGIS Data in QGIS
Now that we’ve connected to the PostGIS database, we can bring layers into QGIS either through the Data Source Manager or by dragging PostGIS tables from the Browser onto the QGIS canvas.
You may want to add a basemap to the canvas as a reference to make sure that your PostGIS layer is being projected correctly. You can do this by dragging the OpenStreetMap basemap onto the canvas from the XYZ Tiles section of the Browser:
You can now edit, add, or delete features in QGIS, and when you save your changes they will be saved back to the PostgreSQL/PostGIS database.
Make sure you have selected the desired layer in the Layers panel.
Then click the Toggle Editing button (the pencil icon).
Use the available tools in the QGIS toolbar to make your desired changes. Below I’ve added a feature with the Add Point Feature button.
When you click the Save Layer Edits button, your changes will be saved to the database.
When you are done editing the layer, click the Toggle Editing button again to exit edit mode for the layer
Using Non-Spatial Tables in QGIS
You can also work with non-spatial PostGIS tables in QGIS. There may be a variety of situations in which you might want to work with non-spatial data. You might have spatial county boundaries and non-spatial demographic data related to each county. If you want to represent the demographic data spatially, you can pull the non-spatial data into QGIS and work with it in conjunction with the boundaries. In an enterprise environment, organizations often have a mix of spatial and non-spatial data. Non-spatial data may be related to the spatial data and displaying that data spatially can provide powerful insights. For example, an organization might store facilities in a spatial table, and inspections related to those facilities in a non-spatial table. The organization might want to display the number of inspections per facility in a year on a map, and could do this in QGIS by pulling in both the facility and inspection tables.
To start working with non-spatial PostGIS data in QGIS, drag a non-spatial table from the Browser onto the Canvas. It will be added to the Layers panel. You can tell which tables do not have spatial data in them by the table icon next to the table name in the Browser and Layers pane.
I’ve added a post office layer to my map. Each ghost town has a post office with a start and end date in the post_office table for the years it was in operation. I can join the spatial ghost_town table with the non-spatial post_office table on a common column.
1. In the Layer Properties dialog (double click on a layer or right -click and select Properties) of the spatial layer (in my case ghost_town), go to the Joins section and click the green plus button at the bottom.
2. In the Add Vector Join dialog, enter the non-spatial table (in my case post_office) and indicate which field on both the spatial table and the non-spatial table the join should be performed. Then click OK. For information about the other options on this dialog, go here.
3. You’ll now be able to see your join in the Joins section of the Layer Properties.
4. When you select or identify a feature on the map, you’ll be able to see the attributes of the joined non-spatial table alongside the attributes of the spatial table.
5. If you select Editable join layer when you are creating the join, you’ll be able to edit the joined table’s attributes (e.g. I can edit the associated post_office attributes from a ghost_town feature). Make sure to select the proper sub-items (Upsert on edit, Delete cascade) so that the joined non-spatial table is acting as you expect if you add or delete a feature in the spatial table. You can hover over these items in the dialog to get an explanation of what each does.
6. Now when you have toggled editing on both layers, click on a feature on the map, and open the Edit feature form, you’ll be able to edit attributes on both tables from one form.
Later in this article we’ll talk about how to make your Edit feature form more user friendly.
QGIS Database Manager
There are a number of actions you can complete with your data just by right clicking on a PostGIS table in the Browser. You can rename the table, truncate the table (erase all records in the table), export the table in a variety of file formats including shapefile, delete the table from the database, or view its properties including its contents, metadata about the table, and a preview of the table on a map.
However, if you want to do things like import layers into PostGIS from a shapefile (or some other file format), add new columns to tables, or run SQL queries you’ll want to use the DB Manager.
Go to Database > DB Manager
2. At first you’ll see something like this:
3. Expand your database under PostGIS, so that you can see the connection details:
Import a Shapefile into PostGIS
Once you can see the connection details for your database, you can import shapefiles into PostGIS.
1. Click the Import Layer/File button at the top of the DB Manager
2. The Import vector layer dialog will appear. In the Input field you can choose a layer that you have added to QGIS already from a shapefile (or other file format). Or you can navigate to the file by clicking the button with three dots. If you choose the second option for importing a shapefile, you can select a zipped shapefile or the individual .shp file if your shapefile is not zipped - either will work.
NOTE: You can use this feature to import Esri file geodatabases (.gdb) into PostGIS. Bring a file geodatabase into QGIS by dragging it into the Layers panel. Its layers will be added to the map canvas and made available in the Input dropdown of the DB Manager.
3. Select the Schema you want the table created in, and choose a name for your Table. I recommend using lowercase and underscores in your table name.
4. Under Options, you can change the names of the primary key column and the geometry column by checking the checkboxes next to them to make them editable. The Source SRID should auto populate if your file has proper coordinate reference system information associated with it. If you want the Target SRID in the PostGIS table to be different from the Source SRID, check the box next to Target SRID and select the desired coordinate reference system. I usually check Convert field names to lower case. And select other options as desired.
6. Click OK and you should see your table in the Browser. If you don’t, click the Refresh button at the top of the Browser
Edit PostGIS Schemas and Tables from QGIS
You can also perform a variety of tasks from the DB Manager such as add/delete schemas, and create/edit/delete tables. To create or delete a schema, go to Schema in the DB Manager:
When you select Edit Table from the dropdown, you can complete a variety of tasks related to tables including:
Adding/deleting columns, including geometry columns
Changing the name, type (varchar, int, etc) length, nullability, or default value expression for a column.
Adding a primary key column to a table
Creating/deleting a spatial index on a table
Running Queries with the SQL Window
You can also run SQL queries on your PostGIS tables from the SQL Window in QGIS’s DB Manager.
From the toolbar of the DB Manager, click the SQL Window tool.
2. Enter your SQL query in the top area and click Execute. You can include PostGIS spatial queries in your SQL
NOTE: You can also use the SQL Query Builder to build your SQL statement:
3. Once you have successfully executed your query, you can add it to the canvas as a layer or create a database view. A view is a virtual table that is created by a query. Views are not stored on disk like tables and will always have up-to-date data from the original tables.
NOTE: When editing data, you’ll want to edit the underlying table (not the view) and the data will automatically be updated in the view.
NOTE: You’ll want to make sure that you select the primary key column for Columns(w) with unique values.
4. If you’ve created a view, you can load it into the canvas just like any other PostGIS layer.
Saving QGIS Styles and Projects in PostGIS
Now that we’ve gone over how to get PostGIS layers into QGIS, the next step would be to style your map including appearance and labels. You may also want to style your attribute form to be more user friendly. You can do this by double clicking on a layer to open the Layer Properties. On the Attributes Form section, select Drag and drop designer. You can then add and remove fields from the Form Layout column. You can also add sections or group fields by using the green plus button. You can give alias names for your form fields and set things like defaults, constraints, and dropdown or lookup values. Go here to read more about all you can do with the attribute form.
Without styling, your form will look something like this, listing all the columns in the table and any joined tables:
After styling it might look something like this.
Being able to style your forms and add things like dropdowns, checkboxes, and default values lets you ensure that the data being inputted into the system is of good quality. This is especially important if you are working in an enterprise environment with multiple users inputting data as it will result in much higher quality data for the organization as time goes on.
Save Default Styles in QGIS
Once you’ve styled your map, you can store styles in QGIS, making it easier to share with other people and to use in future maps. In an enterprise environment with multiple users, being able to save a default style to PostGIS means that when a user adds a layer to a map from PostGIS, it will look the same every time. This means users across an organization don’t have to restyle a layer every time they add it to the map. It also provides consistency in symbology so if you’ve created custom attribute-based symbols (e.g. points based on type), everyone will be using the same symbols across the organization.
Here is a map with some basic styles:
Right now, if I were to open a new QGIS project and add these layers back in from PostGIS, QGIS would assign random styling to each layer, resulting in something like this:
Instead of taking the time to copy and paste the styles back in, we can store default styles in PostGIS so that when we load the layers into a new QGIS project, the default style is automatically used. In order to store a default style, follow these steps:
Double click on a layer to open Layer Properties (or right click and click Properties)
In the Symbology section, click Style at the bottom and click Save as Default
3. Click Datasource Database to save the default style to PostGIS and make it available to anyone using that database.
NOTE: If you select Local Database, the style will only be available to you.
4. If you refresh your Browser, you’ll see that a layer_styles table has been created in the public schema of your database. This is where all your saved styles will be stored.
Now the next time you load that layer into a QGIS project, your default styling, labels, and attribute form will be applied automatically. In addition to default styles for a layer, you can store a style in PostGIS so you can apply it to any layer in the future.
In the Style menu, go to Save Style
2. In the resulting dialog, select In database (postgres) for Save style and name the style.
3. Now when you load a new layer that you want to apply that style to, you select Load Style from the Style menu:
4. Select from database (postgres) for the Load style. Select the style you want and click the Load Style button. You can even select which categories (Symbology, Forms, etc) that you want loaded from the style if you don’t want all of them.
Save QGIS Projects to PostGIS
You can also save QGIS projects to PostGIS so that you or others can access them in the future. This is a great feature for multiple people to collaborate on a project. In an enterprise environment, this means that one person could edit the project, updating styles or going through geoprocessing routines. When they save the project, it will save in the database where another user can access it and get all the updates. When a user in an enterprise environment opens a QGIS project that is stored in PostGIS, they will only be able to edit the layers that they have permission in PostGIS to edit.
To save a QGIS project in PostGIS, go to Project > Save To > PostgreSQL
2. In the dialog, select the correct Connection, Schema, and name the Project. Then click OK.
3. If you refresh the Browser, you’ll see that a table called qgis_projects has been created in the schema you specified. The project is stored in this table, but for convenience it is also listed separately in the Browser.
Now I can open a new QGIS project and double click ghostTownProject in the Browser and it will open my project with all layers loaded and styled as I left them. If I make changes to the project and save it, the changes will be saved to PostGIS. This is a great feature for sharing projects with others in an enterprise environment.
NOTE: If you want to delete a QGIS project from PostGIS, go to Project > Save To > PostgreSQL, select the Project you want to delete and click Manage Projects > Remove Project.
More Features Worth Checking Out
There are so many great features that make working with QGIS and PostGIS together a seamless experience - too many to go over in this article. Here are a few that might be worth exploring on your own.
Offline Editing: These days the most efficient data input workflow is happening in the field, not in the office. QGIS has a feature that allows you to edit PostGIS layers offline and sync your changes back to the master database when you are back in the office.
Storing Raster Data in PostGIS: You can store and work with raster data in PostGIS.
Auto Populate Attributes Upon Feature Creation: You can use trigger functions to automatically populate attributes on a newly added feature. This includes populating spatial attributes for the feature such as area or populating township.
Using PostGIS Lookup Values in QGIS Forms: You can use data from PostGIS tables as lookup values in dropdowns in QGIS attributes forms.
Final Thoughts
Getting started with PostgreSQL/PostGIS might feel daunting, but as you’ve seen, you can take advantage of many of its features without ever leaving QGIS. These two open source tools complement each other, both in an enterprise environment with multiple users and for an individual working on their own projects. Both QGIS and PostgreSQL/PostGIS have robust user communities that are continuously adding new functionality which you’ll benefit from as time goes on.
We’ve only just scratched the surface here, and there are many great resources out there if you'd like to dive into PostGIS further. Crunchy Data offers many interactive classes that will teach you the basics of SQL and working in PostgreSQL/PostGIS. PostgreSQL Tutorial is also a great resource. And you can find links to QGIS tutorials at the bottom of this article.
If you have questions or want to learn more about leveraging the full strength of QGIS and PostGIS, let us know. Email us at contact@line-45.com or call us at (833) 254-6345. Special thanks to the Otsego County Historical Society for letting us use their ghost town map data as an example.
