QGIS-08 3.28: PostGIS Data in QGIS

Owned by Antony Scott (Deactivated)
Last updated: 07 Mar, 2023
2 min read

8. PostGIS Data in QGIS

QGIS s 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

  1. In the Browser, navigate to the PostgresSQL connection you created earlier, and expand the schemas and tables to see the data it contains

  2. Expand one of the tables in the Browser, and look at the field listing

  3. Right-click > Layer Properties on the table, and look at all three tabs for the layer - Metadata, Preview and Attributes

  4. Right-click on the layer again, and look at the other options on the menu (but don’t use any of them)

  5. Load two or three layers from the database into the map

  6. Load two of the file-based vector layers you have been working on

  7. Go to Data Source Manager > PostgreSQL and Connect to the database using the same connection

  8. Expand the crime schema and scroll right across the columns to see the data listed for each table

  9. 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

  10. Go to Database > DB Manager and browse the database there

  11. 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

  1. 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

  2. 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

  3. When the table has been loaded successfully, refresh the connection in the Browser and check the table is there

  4. Drag and drop the second file-based layer from the Layers panel onto the training schema in the Browser

  5. You will see that this also loads the layer into the database, but the options in the DB Manager aren’t available