If you need to visualize (non-spatial) data stored in your PostgreSQL database, there are a few different options. You might look into some of the open source GUI tools out there, build your own custom solution with Python or R, or resort to a commercial solution.
However, if you have spatial data in your PostgreSQL/PostGIS database, your case is slightly different. Quite often, visualization and analysis of geospatial data is done with a desktop software, such asQGIS.
QGIS is an open source desktop GIS software. Originally developed in 2002 to view the geometries stored in PostGIS, QGIS has evolved into much, much (much!) more. It runs on Linux, Unix, Mac OSX, Windows, and Android, and it supports many data formats and functionalities. The standalone installer is around 500 MB, and the installation process takes a few minutes.
Connecting to Azure PostGIS with QGIS
If you have a PostGIS database and you’d like to access your data through QGIS, creating a connection from QGIS to an Azure Database for PostgreSQL instance is very straightforward. After you have created a new PostgreSQL instance using the Azure portal, you must modify database network settings to allow external connections from your IP address to PostgreSQL. As I wrote in my previous blog post, to enable the PostGIS extension, you just have to run the following SQL statement:
CREATE EXTENSION postgis;
After this, you can fire up QGIS and create a new connection by adding your connection parameters to QGIS. Remember to enable the SSL mode:
Now, when you selectTest Connection, you should get a blue bar with a message notifying you about a successful connection. You are done! Now you can view, visualize, and edit geometries inside the QGIS main window. Geometry editing is one operation that would be extremely difficult without a tool such as QGIS.
QGIS allows you to perform different spatial analysis on the data layers and to combine information from different data sources. QGIS can also add non-spatial layers from PostgreSQL to a project. So, a user might have tables without a geometry column and QGIS can also handle those, join them to other layers, and use them for analysis.
QGIS in action
QGIS has a few different ways to explore and connect to PostGIS, but one tool you don’t want to miss is DB Manager. It comes with the normal QGIS installation and can be compared to a small pgAdmin equivalent inside QGIS. For most spatial data users, DB Manager makes pgAdmin completely obsolete. DB Manager allows the user to write queries, explore connections, and edit tables in a simple GUI. Besides offering a GUI for most database operations, DB Manager also offers allows you to view to a lot of useful metadata related to your spatial tables.
The ability to read and write multiple spatial data formats is one of the strengths in QGIS, just like the vast visualization options and effective rendering. Rendering even millions of data points can be achieved in QGIS in seconds.
One example of the power of visualization is the rendering of these transportation pattern flows from Azure Database for PostgreSQL. I have collected transportation GPS points from an open API to my Azure Database for PostgreSQL instance. Loading data to PostGIS with QGIS is trivial, and static files can be loaded with pre-built tools, even without any SQL or scripting. I will go through different ways of loading spatial data to PostGIS in a later part of this blog post series.
QGIS is primarily designed for static data-analysis off loaded datasets, but you can extend its capabilities to suit your purposes. For instance, by using the PostgreSQL NOTIFY feature, you can update your QGIS maps automatically.
Depending on the user, QGIS can be considered either as the starting point or the end point of the data workflow. For some users, QGIS can either be the place for manually creating new data (digitized), editing it, and then pushing it to PostGIS. Other users might pull data from PostGIS to QGIS, where it can be analyzed and visualized.
QGIS is a very active open-source project with user groups in several countries. The software has been also translated into many languages. Although I wrote at the beginning that using QGIS doesn’t require any code, this doesn’t mean that you can’t expand its capabilities. QGIS has a powerful Python API, and QGIS can be extended with plugins. Most users are using ‘point-and-click’ workflows, which are the main strength of the software.
New versions are released every few months, and currently QGIS is at version 3.1 which will become LTR early next year. QGIS offers visual changelogs for all major releases, which make it easy to follow the development.
How to get started with QGIS?
As with many new software applications, the best way to get familiar with QGIS is through YouTube tutorials. Whether you are working with files on a disk or PostGIS, the basic principles of the software are quite similar. There are also some good books that can help you to get started. The QGIS documentation is excellent, and Twitter and GIS Stackexchange are places where you can find help easily.
In the next posts in this series, I will explain some ways to find and load spatial data, provide additional detail about FOSS4G components, and explain more about spatial analysis.