Displaying PostGIS geometries using QGIS

This guide propose some tricks to use ​QGIS to display and modify content of geometries tables in PostGIS.

Please add precisions and new topics if you have ideas

Summary

• Why use QGIS with PostGIS
• Workflow
• QGIS limitations
• 4 ways to display a PostGIS geometry in QGIS
• Tricks

Why use QGIS with PostGIS

QGIS is an open source software with lot's of functionalities. PostGIS is natively integrated in it, and it works relatively well.

Also QGIS has an extension repository and is fully scriptable with Python, which makes it a cornerstone for working in GIS with Open Source software. When using PostGIS you always end up to have to display some geometry, so better use the right tool for it.

Workflow

The workflow is always identical. The first time, you have to define a connection to PostGIS database ((default values are : host:localhost , port:5432 , Database:postgres)). Then you will load a PostGIS table containing a geometry column as a new QGIS layer. Then this layer can be edited, and you have access to all the other columns (if the type is known by QGIS, else cast to ::text), for example to use it as a color categories, or as proportion field, etc.

QGIS limitations

Every loaded PostGIS geometry layer must have a Primary key! That is, a way of uniquely identifying every row of the table. In PostGIS it is usually done adding a gid column in table definiton, like this : CREATE TABLE mytable AS (idauto SERIAL int, other_columns...). If you have no other option , you can always use a trick to generate on the fly a fake uid (see Tricks).

PostGIS srid are not supported, so you have to manually choose the right projection and coordinate system when adding a PostGIS geometry layer. Mixed geometry are not supported ( neither are GeometryCollections). So when using mixed geometries, you have to load 3 times the table each time selecting a different type of geometry . Use St_CollectionExtract or ST_Dump to break GeometryCollection appart.

Loaded PostGIS layers are intended for stable use. So when loading a layer from a PostGIS table, don't delete table or change table def, as it will crash QGIS. Reloading will discard bad PostGIS layer.

PostGIS is made to display up to a few hundred thousand geometry with beautiful styling. More geometries than this is going to be slow, or will crash. You may want to clip you data to keep only a work set if working with massive data.

Funky type : QGIS works well with points, line , polygon. It will work to a certain extent with Multi, but will definitively not work with more subtle geometry type, like curve, mesh, tin, etc. Usually PostGIS has function to convert to classical type (ST_CurveToLine for example)

5 ways to display a PostGIS geometry in QGIS

From easiest to most complicated and powerful

• Load the "QuickWKT" Extension. It should add a button "WKT". This open a text field where you can past WKT geometries. Warning : doesn't support curve or mixed type, but does support EWKT (you can specidy srid). This adds a layer to QGIS, you can add as many layers as you want. This should be used only for small tests.

• Select the "Add a PostGIS Layer" in the "Layer" menu. This open a windows where you have to define a new connection to your PostGIS server (default values are : host:localhost , port:5432 , Database:postgres). You have several options you can try afterward by clicking on the edit button to edit the connection. When connected to a database, you can browse to your table and select it to display it in QGIS. You can select multiple table. A same time will appear X times if it has X geometry columns. If your table doesn't show, it is probably not registered in the public.geometry_columns PostGIS view. You have other option to display content.

• Select the DBManager in the menu Database. In the left panel, browse to your connection, then choose a table with geometry. You can right click on this table and chose "Add as a QGIS layer". This will load table even if geometry column is not listed in PostGIS view public.geometry_columns. This is useful if you create your table with queries like CREATE TABLE my_table AS SELECT geom ....

• Select the DBManager in the DataBase menu, then browse to PostGIS /your_connection. Now click on the "SQL WIndow" icon. This open a text editor where you can use any query you like. It is very useful when geometry are not directly accessible. For example, you have a table with id, X and Y columns, but no geometry column. You can use the following query SELECT id, X, Y, ST_SetSRID(ST_MakePoint(X,Y), your_srid) AS geom FROM your_table

You can test query with F5. There is auto completion. CTE are allowed, but you need to be strict on query formatting (no leading spaces or tabs, neither at the end). When you are satisfied, you can click on the "load as a new layer" box and choose an id and geom column.

Remember each line must have an unique id (see tricks), uni-type geometry, and proper formatting.

• If you prefer, there are various QGIS extensions allowing sql windows /and/or specific PostGIS management.

Tricks

You can use materialized view (require Postgres >= 9.3), as a "buffer" for qgis. Materialized view are like view with cache ! A view is just a stored query which is executed each time it is used. In a materialized view, the query is also stored, but so is the result of the query. Thus when using several time the materialized view, there is no need to do computing several times. The downside is you have to manually update materialized view when the content has changed. See ​http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html

If you have a *very* recent QGIS version you can just add view like any other PostGIS table. Else, you have to create a simple view querying you materilized view. For example :

CREATE VIEW my_proxy_view AS SELECT * FROM my_materialized_view

You can use QGIS visualization settings to make the element appears dynamically.

This is better than just waiting with blank screen while objects are loading, at least when trying to define an interesting area. For this, open Option/Rendering, then put something superior to 1000 in the appropriated field.

When you don't have a unique id for each row you want to display, you can create on on the fly using the SQL windows to load your geometries. Simply add in the select : SELECT row_number() OVER( ORDER BY field1,field2 ) AS id_qgis , and select id_qgis as the id column in the QGIS list. It is necessary to specify the Order By clause to ensure the same ID is always assigned to the same row, for every query, regardless of the usage of fields and joins that do affect the row order in the result set.