QGIS and PostGIS are excellent tools for managing geospatial data. Both GIS platforms offer an interactive Graphics User Interface that allows users to write queries to answer geospatial questions, manage maps, and produce informative presentations.
This article discusses the process of connecting a PostGIS database with QGIS on your device. Accessing, styling, and saving projects. We will also see how you can import, export, and write SQL queries directly on the QGIS interface.
First, let’s begin with creating a database using PgAdmin.
Key Points
- To create a database using PgAdmin, Databases >> create >> Database >> set database name >> set the owner > Save.
- To create a spatial extension, Extensions >> create >> extension >> set name – PostGIS > Save.
- To create a new PostGIS connection, layer >> Add layer > Add PostGIS Layer > new
- To join two datasets /tables, From the Layer panel – Spatial table >> joins >> select both tables >> set Target field.
- To write a query, open the 1SQL Window, and write >> Execute to run the query.
- To save query results as a layer, 3check the ‘Load as new layer’ >> set your 4geometry column and unique column >> 5Load.
- To save a style as default, on the layer panel, Right click on the layer >> properties >> symbology >> style >> save as default >> data source database
- To save a project on the PostGIS database, Project >> save to >> PostgreSQL >> set database name, schema, and name of the project > OK.
How to Create a Database Using PgAdmin
Creating a PostgreSQL database can be done using the command line or using the PgAdmin GUI, depending on preferences.
To create a spatial database using PgAdmin, enter your master password on the open dialogue to access PostgreSQL.
On PgAdmin; 1Databases (Right-click) >> 2create >> 3Database >> 4set database name >> 5set owner (here, I stuck with the default super-user, Postgres) > 6Save. [The image below and subsequent images are numbered according to superscripts in the descriptions]
1 Create a PostgreSQL database using PgAdmin
To create a spatial extension; 1Extensions (Right-click) >> 2create >> 3extension >> 4set name – PostGIS > 5Save.
2 Add a PostGIS spatial extension to a PostgreSQL database
Confirm this worked by checking if a spatial reference system has been assigned to the database; schemas >> tables > spatial ref
Now, you have a referenced spatial database, let’s connect your QGIS Database Manager with the PostgreSQL database.
How to create a New PostGIS Connection in QGIS
Creating a new connection can be done in a few ways;
1. 1Layer >> 2Add Layer > 3Add PostGIS Layer > 4New
2. AData Source Manager (on the toolbar)>> BPostgreSQL > CNew
3. iBrowser panel >> iiPostgreSQL (Right Click) > iiiNew Connection
3 Create a new PostGIS connection in QGIS
On the opened dialogue, set your parameters accordingly for a successful connection;
Name -> Preferred identifier for the connection
Host –> localhost
Port –> 5432 (Default) – only adjust the set Port to what was used when setting up your database.
Database –> database name for the connection
4 Create a new PostGIS connection in QGIS
You can set configurations for your authentication or stick with the basic protection. The latter leaves your database exposed as the product file can be viewed in a text editor, displaying the password in plain text.
To set up an authentication configuration, click on the “+” icon >> enter a master password
>> Go on to fill in the authentication information with the set username and password
>> Test connection
5 Set Authentication configuration on QGIS-PostGIS Database
I’d love to view all tables including those without the geometry column and also allow syncing projects between QGIS and the database. So, I’ll check those functions.
6 Create a new PostGIS connection in QGIS
>> OK.
When working in an enterprise environment, the auto-generated Id is used for verification before connecting to a database. It can be accessed using the edit configuration button or through the authentication tab in the settings.
To manage your master password if lost, delete all previous configurations from the QGIS database.
1Settings >> 2options >> 3authentication >> 4utilities > 5erase authentication database
7 Reset database authentication password
Working With Datasets – QGIS & PostGIS Database
Using the QGIS database management tool, the user can view and adjust tables, schemas, etc. in a database.
1. Quick Features
- Create, rename and delete schemas and tables.
- Delete all features from a table by truncating it.
- Export a table to different file formats
- Edit tables – delete columns, change column names and types, add a geometry column to a non-spatial table, add a spatial index, etc.
- Import and export data files into your database in any QGIS-supported file format.
8 Access files using the QGIS Database Management tool
2. Import Datasets
All datasets loaded in your QGIS workspace will be provided as options when importing files to the database. Otherwise, navigate to the file directory.
>> select schemas >> you can change the names of your primary key and geometry column, and adjust the Spatial Reference ID (auto-detected). If your data was received in different SRIDs, adjust the Target SRID to ensure all datasets align.
9 Import datasets into the PostGIS database, On QGIS
The time taken will depend on the size of the dataset.
3. Join Datasets
When features of a dataset are split into two or more tables, performing joins is necessary to view every feature of each attribute at a glance.
To join two datasets /tables (spatial and non-spatial)
From the Layer panel – Spatial table (double click) >> joins >> select both tables >> set Target field to a field/feature that matches both tables
Check the editable join layer if you intend to make edits to the attributes of both tables
10 Join datasets in QGIS
When adding and deleting features in a dataset, this setting helps for better syncing of changes.
4. Run Queries
The DB management tool allows users to run SQL queries directly in QGIS. Be sure to connect your database by clicking its drop-down.
To write a query, open the 1SQL Window, and use the provided workspace. Click 2Execute to run the query. Query results can be saved as a new layer, file, query, or simply create as a view on your QGIS workspace. Both options leave you with a precise dataset for analysis.
To save query results as a layer, 3check the ‘Load as new layer’ >> set your 4geometry column and unique column >> 5Load.
11 Write Spatial SQL queries on the QGIS DB Management tool
5. Setting up Styles
Styling GIS maps is important when presenting the result of an analysis. The DB management tool allows users to copy, apply and store map styles (symbology, labels, and forms) from previously generated maps, to be reused in future projects.
To copy a map style;
On the layer panel, Right click on the layer >> styles > copy.
Select the exact feature styles of interest or simply select ‘All style categories’ to copy all feature styles. Apply the map style to the new layer by pasting it in the style feature.
12 Copy and paste map styles from/to QGIS maps
To save a style as default;
On the layer panel, Right click on the layer >> properties >> symbology >> style >> save as default >> data source database.
13 TO save a map style as default,
To save a style and manually apply it to specific features of another layer, use the ‘save style’ option under >> properties >> symbology >> style >> save style
The style is stored in the ‘public’ schema of the database, in a table called: layer styles.
This feature comes in handy in an enterprise environment where multiple users work on the same or similar mapping project. It maintains consistency across projects.
6. Save QGIS Projects to PostGIS Database
Saving projects across platforms allows different users easy access to updated versions of a project.
To save a project on the PostGIS database,
Project >> save to >> PostgreSQL >> set database name, schema, and name of the project > OK.
14 To save a QGIS project to your PostgreSQL Database.
Apple Podcasts
Google Podcasts
Spotify
