Last time we imported OpenStreetMap datasets from Iceland to PostGIS.
To quickly visualize our results, we will now use QGIS to render our datasets and generate some nice maps on the client.

Let’s start with our prerequisites:

  1. A PostGIS enabled PostgreSQL database preloaded with OpenStreetMap datasets as described in blogpost https://www.cybertec-postgresql.com/en/open-street-map-to-postgis-the-basics/
  2. An up and running QGIS Client, see https://www.qgis.org/de/site/ for further instructions
  3. A Git-Client to checkout some predefined styles for QGIS

Import datasets into QGIS

QGIS supports PostgreSQL/PostGIS out of the box.
We can import our datasets by defining our database connection (1), showing up the db catalog (2) and adding datasets of interest (3) to our workspace.

Step 1Step 2Step 3
Visualizing OSM data in QGISVisualizing OSM data in QGIS

 

QGIS will automatically apply basic styles for the given geometry types and will start loading features of the current viewport and extent. Herby the tool will use information from PostGIS’s geometry_column view to extract geometry column, geometry type, spatial reference identifier and number of dimensions. To check what PostGIS is offering to QGIS, let’s query this view:

select f_table_name as tblname, f_geometry_column as geocol, coord_dimension as geodim, srid, type
from geometry_columns
where f_table_schema = 'osmtile_iceland';

 

This brings up the following results:

tblnamegeocolgeodimsridtype
planet_osm_pointway23857POINT
planet_osm_lineway23857LINESTRING
planet_osm_polygonway23857GEOMETRY
planet_osm_roadsway23857LINESTRING

 

For type geometry, QGIS uses geometrytype to finally assess geometry’s type modifier.

 select distinct geometrytype(way) from planet_osm_polygon; 

Basic styling in QGIS

As mentioned before, QGIS applies default styles to our datasets after being added to the QGIS workspace. Figure 1 gives a first impression on how points and lines from OpenStreetMap are visualized this way.

Basic QGIS style applied to osm_points and osm_lines from Iceland
Figure 1 Basic QGIS style applied to osm_points and osm_lines from Iceland

 

It’s now up to the user the define further styling rules to turn this basic map into a readable and performant map.

As a quick reminder – we imported our osm dump utilizing osm2pgsql with a default-style and no transformations into PostGIS. This resulted in three major tables for points, lines and polygons. It’s now up to the map designer to filter out relevant objects, such as amenities represented as points within the GIS client, to apply appropriate styling rules. Alternatively, it’s a good practice to the parametrize osm2pgsql during import or set up further views to normalize the database model.

Back to our styling task – how can we turn this useless map into something useable?

Please find below some basic rules to start with:

  1. Study OSM’s data-model 😊
  2. Define which features are of interest and on which scale to present
  3. Develop queries to grab relevant features
  4. Turn queries in QGIS styling rules utilizing expressions
  5. Evaluate map beauty and performance
  6. Enjoy

Let me present this approach on amenities and pick some representative amenity types.

 select amenity, count(amenity) as amenityCount from planet_osm_point group by amenity order by amenityCount desc; 

 

amenitycount
waste_basket1346
bench1285
parking479

 

To style osm_points and apply individual styles for waste_basket, bench and parking we open up the layer properties, click on symbology and finally select rule-based styling as demonstrated in the pictures below.
For each amenity type then, we set up an individual rule by

  • defining a query to select the amenity of interest,
  • defining an appropriate scale and
  • defining a symbol (svg marker in our case)

 

Step 1Step 2Step 3
Visualizing OSM data in QGISVisualizing OSM data in QGISVisualizing OSM data in QGIS

 

After zooming in, your map shows your styled amenities like below.

 

Custom QGIS styling applied to amenities from Reykjavík
Figure 2 Custom QGIS styling applied to amenities from Reykjavík

 

Well, better but not really appealing you might think, and you are absolutely right.
Luckily, we don’t have to start from scratch, and we can apply given styles to achieve better results.

But wait – we are not done. Let’s see what QGIS is requesting from the database to understand how actions in the map are converted to SQL statements. To do that, let’s enable LogCollector in postgres.conf and monitor the log file to grab the current sql statements. Subsequently this enables us to execute the query with explain analyse in the console to start with possible optimizations.

 EXPLAIN analyse SELECT st_asbinary("way", 'NDR'), ctid, "amenity"::text
FROM "osmtile_iceland"."planet_osm_point"
WHERE ("way" && st_makeenvelope(-2393586.56126631051301956, 9304920.7589644268155098, -2320619.86041467078030109,
                                9356509.49658409506082535, 3857))
  AND (((("amenity" = 'parking') OR ("amenity" =
                                     'waste_basket')) OR ("amenity" = 'wrench'))) 

The resulting explain plan draws attention to a possibly useful, but missing index on column amenity, which has not been created by default by osm2pgsql. Additionally, the explain plain highlights the utilization of planet_osm_point_way_idx, an index on geometry column way, which is being used to quickly identify features, whose bounding boxes intersect with our map extent.

 

 Bitmap Heap Scan on planet_osm_point  (cost=136.95..2272.97 rows=47 width=47) (actual time=0.537..1.239 rows=10 loops=1)
  Recheck Cond: (way && '0103000020110F000001000000050000001093D747F94242C1C46F49186BBF61411093D747F94242C15404E4AF9BD86141641122EE75B441C15404E4AF9BD86141641122EE75B441C1C46F49186BBF61411093D747F94242C1C46F49186BBF6141'::geometry)
  Filter: ((amenity = 'parking'::text) OR (amenity = 'waste_basket'::text) OR (amenity = 'wrench'::text))
  Rows Removed by Filter: 4096
  Heap Blocks: exact=74
  Buffers: shared hit=115
  ->  Bitmap Index Scan on planet_osm_point_way_idx  (cost=0.00..136.94 rows=3821 width=0) (actual time=0.495..0.495 rows=4106 loops=1)
        Index Cond: (way && '0103000020110F000001000000050000001093D747F94242C1C46F49186BBF61411093D747F94242C15404E4AF9BD86141641122EE75B441C15404E4AF9BD86141641122EE75B441C1C46F49186BBF61411093D747F94242C1C46F49186BBF6141'::geometry)
        Buffers: shared hit=41
Planning Time: 0.301 ms
Execution Time: 1.276 ms 

At this point it’s worth to mention auto_explain, a PostgreSQL extension which is logging execution plans for slow running queries automatically. As shortcut, checkout Kareel’s post about this great extension.

Import and apply QGIS styles

Let’s import layer settings on our datasets. For this demo I downloaded layer settings from https://github.com/yannos/Beautiful_OSM_in_QGIS, which result in a typical OpenStreetMap rendering view.

For each layer (point, line and polygon), open up the layer properties, go to symbology and load the individual layer settings file.

 

Step 1Step 2
Visualizing OSM data in QGISVisualizing OSM data in QGIS

 

Figure 3 shows the resulting map, which serves as good foundation for further adoptions.

It should be stressed that adding styling complexity is not free in terms of computational resources.
Slow loading maps on the client side are mostly caused by missing scale boundaries, expensive expressions leading to complex queries and missing indices on your backend, just to mention some of those traps.
OSM’s common style (https://www.openstreetmap.org) is generally very complex resulting in a resource-intensive rendering.

 

Figure 3 Customized QGIS Styling Yannos, https://github.com/yannos/Beautiful_OSM_in_QGIS

Results

This time I briefly discussed how spatial data can be easily added from PostGIS to QGIS to ultimately create a simple visualization. This particular post emphasizes style complexity and finally draws attention to major pitfalls leading to slow rendering processes.