Adding PostGIS layers using QGIS [Tutorial]

Viewing tables as layers is great for creating maps or for simply working on a copy of the database outside the database. In this tutorial, we will establish a connection to our PostGIS database in order to add a table as a layer in QGIS (formerly known as Quantum GIS).

Please navigate to the following site to install the latest version LTR of QGIS.

On this page, click on Download Now and you will be able to choose a suitable operating system and the relevant settings. QGIS is available for Android, Linux, macOS X, and Windows. You might also be inclined to click on Discover QGIS to get an overview of basic information about the program along with features, screenshots, and case studies.

This QGIS tutorial is an excerpt from a book written by Mayra Zurbaran,Pedro Wightman, Paolo Corti, Stephen Mather, Thomas Kraft and Bborie Park, titled PostGIS Cookbook – Second Edition.

Loading Database…

To begin, create the schema for this tutorial then, download data from the U.S. Census Bureau’s FTP site:

The shapefile is All Lines for Cuyahoga county in Ohio, which consist of roads and streams among other line features.

Use the following command to generate the SQL to load the shapefile:

shp2pgsql -s 4269 -W LATIN1 -g the_geom -I tl_2012_39035_edges.shp chp11.tl_2012_39035_edges > tl_2012_39035_edges.sql

How to do it…

Now it’s time to give the data we downloaded a look using QGIS. We must first create a connection to the database in order to access the table. Get connected and add the table as a layer by following the ensuing steps:

1. Click on the Add PostGIS Layers icon:

2. Click on the New button below the Connections drop-down menu.

3. Create a new PostGIS connection. After the Add PostGIS Table(s) window opens, create a name for the connection and fill in a few parameters for your database, including Host, Port, Database, Username, and Password:

4. Once you have entered all of the pertinent information for your database, click on the Test Connection button to verify that the connection is successful. If the connection is not successful, double-check for typos and errors. Additionally, make sure you are attempting to connect to a PostGIS-enabled database.
5. If the connection is successful, go ahead and check the Save Username and Save Password checkboxes. This will prevent you from having to enter your login information multiple times throughout the exercise.

6. Click on OK at the bottom of the menu to apply the connection settings. Now you can connect!

Make sure the name of your PostGIS connection appears in the drop-down menu and then click on the Connect button. If you choose not to store your username and password, you will be asked to submit this information every time you try to access the database.

Once connected, all schemas within the database will be shown and the tables will be made visible by expanding the target schema.

7. Select the table(s) to be added as a layer by simply clicking on the table name or anywhere along its row. Selection(s) will be highlighted in blue. To deselect a table, click on it a second time and it will no longer be highlighted. Select the tl_2012_39035_edges table that was downloaded at the beginning of the tutorial and click on the Add button, as shown in the following screenshot:

8. A subset of the table can also be added as a layer. This is accomplished by double-clicking on the desired table name.
9. The Query Builder window will open, which aids in creating simple SQL WHERE clause statements. Add the roads by selecting the records where roadflg = Y. This can be done by typing a query or using the buttons within Query Builder:

10. Click on the OK button followed by the Add button. A subset of the table is now loaded into QGIS as a layer. The layer is strictly a static, temporary copy of your database. You can make whatever changes you like to the layer and not affect the database table.

The same holds true the other way around. Changes to the table in the database will have no effect on the layer in QGIS.

If needed, you can save the temporary layer in a variety of formats, such as DXF, GeoJSON, KML, or SHP. Simply right-click on the layer name in the Layers panel and click on Save As. This will then create a file, which you can recall at a later time or share with others.

The following screenshot shows the Cuyahoga county road network:

You may also use the QGIS Browser Panel to navigate through the now connected PostGIS database and list the schemas and tables. This panel allows you to double-click to add spatial layers to the current project, providing a better user experience not only of connected databases, but on any directory of your machine:

How it works…

You have added a PostGIS layer into QGIS using the built-in Add PostGIS Table GUI. This was achieved by creating a new connection and entering your database parameters.

Any number of database connections can be set up simultaneously. If working with multiple databases is more common for your workflows, saving all of the connections into one XML file and would save time and energy when returning to these projects in QGIS.

To explore more on 3D capabilities of PostGIS, including LiDAR point clouds, read PostGIS Cookbook – Second Edition.

Read Next

Using R to implement Kriging – A Spatial Interpolation technique for Geostatistics data

Top 7 libraries for geospatial analysis

Learning R for Geospatial Analysis