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:
- 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/
- An up and running QGIS Client, see https://www.qgis.org/de/site/ for further instructions
- 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 1||Step 2||Step 3|
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:
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.
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:
- Study OSM’s data-model 😊
- Define which features are of interest and on which scale to present
- Develop queries to grab relevant features
- Turn queries in QGIS styling rules utilizing expressions
- Evaluate map beauty and performance
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;
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 1||Step 2||Step 3|
After zooming in, your map shows your styled amenities like below.
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 1||Step 2|
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
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.