GEOG 868
Spatial Database Management

Viewing Data in QGIS


Quantum GIS (QGIS, pronounced kyü'-jis) is a free and open-source desktop GIS package analogous to Esri's ArcMap/ArcGIS Pro. Because of its support for viewing PostGIS data and strong cartographic capabilities, QGIS and PostGIS are often found paired together. (OpenJUMP is another desktop application often used in combination with PostGIS, though its strengths are in spatial querying and geoprocessing.)

A. Add PostGIS data to QGIS

Let's see how QGIS can be used to view the tables we created and populated in the previous section.

  1. Open QGIS. It'll be the QGIS Desktop 3.x choice from the QGIS folder in your Start menu.

    Double-click the New Empty Project option under Project Templates.

    The basic elements of the application GUI are similar to ArcMap/ArcGIS Pro's. The Layers panel in the lower left of the window lists the project layers and their symbology, while the much wider pane to the right displays the layer features themselves.  Across the top of the window is a set of toolbars that can be moved to custom positions by the user.

    Above the Layers panel is the Browser panel, which provides an interface for browsing data sources.  Moving from top to bottom:
    • Favorites - for enabling easy access to frequently used folders on your file system
    • Home - for accessing data located within your folder in C:\Users 
    • C:\ - for accessing data anywhere on your hard drive

      Each of the items above can be used to add file-based datasets to your project, such as Esri shapefiles or CSV files.
    • GeoPackage - for data in an interchange format from the Open Geospatial Consortium (OGC)
    • SpatiaLite - for data stored in a SpatiaLite database (another free and open-source spatial database extension similar to PostGIS, built to add spatial functionality to an RDBMS called SQLite)
    • PostgreSQL - for PostGIS data
    • SAP HANA - for data stored in SAP HANA spatial databases
    • MS SQL Server - for data stored in Microsoft SQL Server spatial databases
    • Oracle - for data stored in Oracle Spatial databases
    • WMS/WMTS - for data streamed via a Web Mapping Service or Web Map Tile Service 
    • Vector Tiles - for adding vector-tiled basemaps
    • XYZ Tiles - for adding raster-tiled basemaps
    • WCS - for data streamed via a Web Coverage Service
    • WFS - for data streamed via a Web Feature Service
    • ArcGIS REST Servers - for map service or feature service data published via an ArcGIS Server instance
  2. Right-click on the PostgreSQL entry and select New Connection.
  3. In the Create a New PostGIS connection dialog supply the following information. (See Figure 3.1, below.)
    • Name: Lesson3
    • Service - (leave blank)
    • Host: localhost
    • Port: 5432
    • Database: Lesson3db
    • SSL mode: disable

      Supplying your authentication information can be done in a simple, but insecure way or a more secure way that requires a bit more effort.  The simple way is to enter your authentication parameters under the Basic tab.  They will be stored as part of the QGIS project file (.qgs), assuming you save your work, where they could be read fairly easily.  The more secure way is to create a configuration under the Configurations tab.  To do this, you would click the + icon, and, in the resulting dialog, give your configuration a name (e.g., Local Server), specify the URL of the PostGIS instance (or leave blank if using the one on your own machine), then supply the authentication parameters.  If going this route, your credentials are stored in encrypted form in a QGIS authentication database on your machine.

      You're welcome to choose either method, though for the purposes of this class, it should be fine to select the Basic method.
      The Create a New PostGIS connection dialog, showing Lesson3db connection settings as described in text above.
      Figure 3.1: The Create a New PostGIS connection dialog, showing the correct information entered.
  4. You should click the Test Connection button to make sure you have typed things correctly.
  5. Click OK to create the connection and dismiss the dialog.
    Lesson3 should now appear as a PostgreSQL data source.
  6. Now, click the arrow next to the Lesson3 entry to expand the list of schemas made available through that connection.   
    If an Enter Credentials dialog pops up, just supply the postgres user name and the password you established for it.
  7. Expand the list of layers available in the nyc_poi schema. You should see the pts, lines, and polys tables created early in the last section and also three layers that reference the mixed table created later (one layer for each geometry type). One of the convenient features of QGIS is that it automatically creates separate layers for each geometry type in a multi-geometry table, like our mixed table.  The mixed layers can be differentiated by the icons that communicate the geometry type.
  8. Highlight all 6 nyc_poi datasets and drag them onto the map canvas (or right-click and choose Add Selected Layers to Project).  You should see a Select Transformation dialog since the SRID assigned to the geometries in the selected tables (4269, NAD83) is of a different datum than the project's datum (WGS84, from SRID 4326).  Click OK to accept the pre-selected NAD83 to WGS84 transformation.  You should see your data displayed in the map display area and the layers listed in the Layers Panel on the bottom left of the window.

    Now let's take a quick tour of how some common GIS operations are performed in QGIS.

B. Explore basic functions of QGIS

  1. Zoom to the full extent of all the layers by selecting View > Zoom Full.
  2. Click and drag to rearrange the layers. Put them in the following order, from top to bottom: pts, lines, polys, and then the mixed pts, lines, and polys layers.
    Note the various features.
  3. The pts, lines, and polys layers are redundant with the three layers based on the mixed table, so turn off pts, lines, and polys by clicking the next to each layer.

    While playing with layer visibility, you may note that the polys layer contains a hole in the Central Park polygon, whereas the "mixed" version of that polygon does not. This is to be expected, since we didn't bother to create that inner ring in the mixed version.
  4. Each layer based on the mixed table has the same name. Go ahead and re-name these layers: mixed - lines, mixed - pts, and mixed - polys by right-clicking on the layers one at a time and selecting the Rename command.  (Be sure you're working in the Layers panel, not the Browser panel.)

    Now, let's see how we can restrict the features that are displayed in a layer by setting a property analogous to the "definition query" in ArcGIS.
  5. Double-click on the mixed - lines layer to open its Layer Properties dialog. You should see tabs along the left side.

    The Symbology tab is where you'd go to change the way a layer is symbolized. Note the pick list at the very top of the dialog which provides Single Symbol, Categorized, and Graduated options, etc.

    The Actions tab provides functionality similar to ArcGIS's Hyperlink settings for launching external applications to view data found in the attribute table such as images and URLs.

    The Joins tab is where you'd go if you need to join data from another table to the layer's attribute table.

    The Diagrams tab provides settings for creating pie chart and histogram (bar) chart overlays from numeric data in the attribute table.

    You can investigate the other elements of the Layer Properties at your leisure.
  6. Now, still in the dialog for the mixed - lines Layer Properties, note the locations of the three line features, then go to the Source tab. Recall that the three line features represent two tunnels (the longer line features) and a bridge. We are going to restrict the layer to showing just tunnels by doing the following:
    • Click on the Query Builder button at the lower right of the Layer Properties dialog.
    • In the Provider Feature Filter box, compose the expression shown below, or just copy-paste it.
      "name" LIKE '%Tunnel'

      The % character is the wildcard character in Postgres; we saw it was the * character in MS-Access in Lesson 1.

    • Click the Test button to verify the veracity of your expression, then OK after confirming that it returns 2 rows.
    • Click OK to dismiss the Query Builder dialog.
      You'll see the expression mirrored in the Provider feature filter box of the Layer Properties dialog.
    • Click OK to dismiss the Layer Properties dialog.
      You should see that the Brooklyn Bridge is no longer displayed as part of the layer.
  7. An intuitive set of zoom/pan tools can be found on the Map Navigation Toolbar (the bar containing the "hand" icon). Clicking on the Pan tool activates it; you can then click-and-drag in the map display area to alter the visible extent.
  8. To the right of the Pan tool, you'll see a number of useful tools for zooming: in/out, to the full extent, to the extent of a layer, to the extent of the selected features, and backward and forward in the extent history, along with tools associated with map tips and bookmarks.
  9. To the right of the Map Navigation toolbar is the Selection Toolbar and the Attributes Toolbar. The controls on these toolbars include tools for selecting (the layer needs to be highlighted first)/unselecting features, the Identify Features tool, a tool for opening the attribute table of the layer selected in the Layers Panel, a Field Calculator, etc.
  10. Right-click on the mixed - pts layer, and select Open Attribute Table. Again, you should find the table dialog has functionality that is very similar to what is found in ArcGIS.
  11. Find and click the Select features using an expression button (it has a script capital E on it), and enter the following example expression:
    gid < 4
    Note that you can build the expression graphically by expanding the Fields and Values and Operators lists, then double-clicking on items in those lists.
  12. Click Select features to evaluate the selection expression, then click Close to dismiss the Select by expression dialog, and note the selected features in the attribute table and on the map.
  13. Close the Attribute table.
  14. Finally, select Project > Properties.

    Under the General tab, you can: set the selection and background colors, specify whether references to data sources should be stored with relative or absolute paths, and set the display units of the project.

    Under the CRS (Coordinate Reference System) tab, you can specify on-the-fly coordinate system transformation settings. Let's re-project our data into the New York East State Plane system.
  15. Click on the CRS tab.
  16. The Predefined Coordinate Reference Systems section of the dialog (where you pick a coordinate system) should show that NAD83 (with Authority ID of EPSG:4269) is selected.  This setting was inherited from the first data layer added to the project. Scroll to the top of the list and click the minus sign [-] sign box to collapse the list of Geographic Coordinate Systems. This should enable you to see the other major category, Projected Coordinate Systems, which is where we'd expect to find the New York East system.  QGIS doesn't include a State Plane sub-category in its Projected Coordinate System list; all of the state plane projections can be found in either the Lambert Conformal Conic or Transverse Mercator sub-categories.
  17. Scrolling through those long lists can be tedious, so go to the Filter box near the top of the dialog and enter new york. The coordinate system listing should now only include those with 'new york' in the name.
  18. Find NAD83 / New York Long Island EPSG: 32118 under the Lambert Conformal Conic category and select it.  Note that the area the coordinate system is designed for is highlighted in red on the mini map at the bottom right of the dialog.
  19. Click OK to accept the selected CRS.
  20. If your data are no longer visible, select View > Zoom Full. You should note that the Coordinate readout at the bottom of the application window now reports values in the ballpark of 300000,65000.
  21. Finally, save your project by clicking the Save button. Give the project a name of Lesson3 and save it to an appropriate folder. Note that the file extension for QGIS project files is .qgs.

That completes our quick tour of QGIS. In the next section, we'll return to pgAdmin to see how queries can be saved for later re-use.