8. PostGIS Data in QGIS

QGIS is particularly tightly integrated with PostgreSQL/PostGIS, which is the focus of this module, although it can also use data in a number of database formats, both client/server (e.g. MSSQL, Oracle) and file-based (e.g. GeoPackage, SpatialLite, ESRI).

This exercise shows you how to connect to data in a PostGreSQL/PostGIS database, and how to load data into the database from QGIS. The functionality to do this is split (and to some extent duplicated) across the Data Source Manager, Browser, and DB Manager plugin (which is installed by default), so we’ll compare these techniques.

8.1. Browse, preview and load data

In the Browser, navigate to the PostgresSQL connection you created earlier, and expand the schemas and tables to see the data it contains
Expand one of the tables in the Browser, and look at the field listing
Right-click > Layer Properties on the table, and look at all three tabs for the layer - Metadata, Preview and Attributes
Right-click on the layer again, and look at the other options on the menu (but don’t use any of them)
Load two or three layers from the database into the map
Load two of the file-based vector layers you have been working on
Go to Data Source Manager > PostgreSQL and Connect to the database using the same connection
Expand the crime schema and scroll right across the columns to see the data listed for each table
Check the Also list tables with no geometry box at the bottom, and notice that more non-spatial tables are listed in the crime schema
Go to Database > DB Manager and browse the database there
Select a table in the DB Manager and open the tabs on the right to see the same details you saw earlier in the Browser, presented differently

8.2. Upload data to the database

In the DB Manager, use the Import Layer/File button to load one of the file-based layers you loaded earlier into the training schema - include your name in the table name so we know whose is whose
In the Options section, you can leave most of the defaults in place, but note:
1. Replace destination table (if exists): will overwrite a table with the same name
2. Convert field names to lower case: is good practice
3. Create spatial index: will make your spatial queries run faster
When the table has been loaded successfully, refresh the connection in the Browser and check the table is there
Drag and drop the second file-based layer from the Layers panel onto the training schema in the Browser
You will see that this also loads the layer into the database, but the options in the DB Manager aren’t available

Browser not supported