Introduction
PostGIS is an open-source, easily accessible PostgreSQL database extension for spatial data types (points, lines, and polygons) storage, geographic operations, spatial indexes, and more.
QGIS is a popular free and open-source desktop GIS application to create, edit, visualize, analyze, and publish geospatial information.
PostGIS and QGIS are powerful tools for working with and managing spatial data; by connecting PostGIS to QGIS, you can enhance your geospatial analysis capabilities.
In this tutorial, I will walk you through the process of connecting PostGIS to QGIS.
Step 1: Download and Install PostgreSQL
Navigate to the PostgreSQL download page to download the latest version of PostgreSQL for your operating system. It supports Linux, MacOS, Windows,BSD, and Solaris.
Follow the installation guide to install the software for Windows, Linux, and MacOS.
During the installation process, you’ll set a password for the Postgres user. Make sure to use a password that would be easy for you to remember, as you’ll need it to connect to PostGIS.
Step 2: Download and Install PostGIS
When installing PostgreSQL, ensure you check all the components. The Stack Builder will be used to download PostGIS, the spatial database extension.
During the installation process, you will receive a prompt to launch Stack Builder. Ensure it is checked, and follow the installation wizard instructions.
Once you click ‘Finish’, Stack Builder will prompt you to select the PostgreSQL version for which you want to install PostGIS. Be sure to choose the version that matches your current PostgreSQL installation.
Note: Ensure you are connected to the internet to download additional tools/software.
Now, you can go ahead and select the application(s) you would like to download. In this case, we want to download PostGIS, a PostgreSQL spatial database extension.
Choose your preferred Download Directory and click on ‘Next’. This action downloads the selected application (PostGIS, in this case).
After the application is downloaded, you will receive a prompt asking if you would like to install it. Click Next and agree to the terms of agreement.
The PostGIS component is automatically checked. You can also check the “Create spatial database”, but in this tutorial, we will go through creating a database using pgAdmin 4. Click ‘Next’ to continue.
Specify your preferred installation destination folder. Make sure it points to the correct PostgreSQL installation directory where you want PostGIS to be installed, and click ‘Next’ to continue.
Once it is installed, you will receive a prompt saying your setup was completed successfully. Now you are good to go, click ‘Close’ to finish.
You have successfully installed the PostGIS spatial database extension to your PostgreSQL, yay!
Step 3: Create a Database using PgAdmin 4
Launch pgAdmin, a PostgreSQL database management tool, which is typically installed along with PostgreSQL.
You will be prompted to enter the password you set during PostgreSQL installation to log in.
Enter the password and click OK
Expand Servers, and right-click on the PostgreSQL> Create > Database
Choose a name for your new database, fill it in (for this tutorial, I'm using “Connect PostGIS Tutorial”), and click ‘Save’.
Now, your new database has been created!
To finish up the PostGIS extension installation, expand the Databases Tab, and navigate to your new database(in my case, Connect PostGIS Tutorial). We can add the PostGIS extension in two ways:
Right-click on your new database, and click on the Query Tool. Type
CREATE EXTENSION postgis;in the Query Editor, and click the Execute button or press F5 on your keyboard to run. This query creates a PostGIS extension in your database
- Another way you can add PostGIS extension to your database is to:
Expand your database and navigate to Extensions. Right-click Extensions > Create > Extension.
In the Create - Extension Prompt, scroll-down or search for postgis, click on it, and click ‘Save’.
Once it is saved, you have successfully added the PostGIS extension.
To confirm that the PostGIS extension has truly been created, expand your database, navigate to Schemas, expand Public and Tables, you should see a spatial_ref_sys table. This confirms that the spatial database extension, PostGIS has been created.
Step 4: Connect PostGIS Database in QGIS
To connect PostGIS in QGIS, you need to download and install QGIS on your system
QGIS is available on Windows, MacOS, Linux, BSD, Android, and IOS. Download the latest version here.If you prefer, you can download the most stable version (3.28). Install the software by following the instructions on the installation wizard. For this tutorial, I would be using QGIS 3.28
Launch QGIS on your computer system to create a PostGIS connection. Click on the Layer Tab, and click on Data Source Manager
Navigate to PostgreSQL and click on New to create a new PostGIS connection.
Fill in the Name (you can use whatever name you prefer. in this tutorial, i would use PostGIS connect), Service as blank, Host as localhost (if you are running this on your local machine) or an IP address (if you are running it on a remote server), port as the port for your installed PostgreSQL (default port, 5432), and database as the name you saved the PostGIS database in pgAdmin 4 (in this tutorial, Connect PostGIS Tutorial). If you are not sure of the name of your database, you can always go back to pgAdmin 4 to check.
There are two types of Authentication: Configurations and Basic.
For the Basic, you only need your username (postgres) and password (that you used when installing PostgreSQL). However, your username and password are stored as plain text in the QGIS project file
For the Configurations, click on the green plus sign button to create a new authentication configuration. You will be required to set up a new master password if you don’t have any saved already in your password manager. Once you set a master password, you won’t be required to enter it again, untill you quit QGIS.
However, this master password is not retrievable, so ensure you always remember it. Once you forget it, you’ll have to erase the entire authentication database in order to create a new master password.
Now, you can go ahead to use any authentication you prefer. For this tutorial, I will stick with the basic authentication.
Click on Test Connection once you have filled every thing correctly, you should see a message pop that confirms that your connection was successful.
Click OK to create your PostGIS connection in QGIS
Now you should be able to see your database in the Browser when you expand PostgreSQL
Conclusion
Yay! We have successfully connected PostGIS to QGIS. You can seamlessly work with spatial data between these two powerful tools; they both have robust user communities continuously adding new functionality that you will benefit from as time goes on.
We have only connected PostGIS to QGIS in this tutorial, later we will explore many other features, but until then… Here are additional resources you can check out to learn more and dive deeper:
