Tips and tricks to simply your spatial files using QGIS and MapShaper for mapping in Icon Map in Power BI
Displaying very detailed spatial files using WKT in the Icon Map comes with it’s challenges – the biggest being poor performance (i.e. very slow to render). Some simplification and reduction of precision can go a long way In this video blog I walk through some of my tips to simplify and reduce precision of your spatial files (using free tools such as QGIS and MapShaper) BEFORE importing into Power BI as a WKT file to really improve rendering speed and avoid exceeding the 32,766 text character limit in Power Query. I hope this helps you have fun exploring maps in Power BI.
🎦 Check out the video 🎦
🤔 Why is my Icon Map so slow… or not displaying all my spatial data?
Icon Map is my favourite mapping visual in Power BI, as it allows for such flexibility and customisation - including adding your own custom polygons and lines as WKT text strings. However, a challenge that crops up time and again is that my spatial files are very detailed, which can result in really poor performance (i.e. slow rendering speed of the map) or the data not rendering at all!
This is often because my spatial data is very detailed (i.e. it could have a lot of vertices, or each data point has a lot of decimal points), resulting in extremely large WKT text strings (causing slow rendering speeds), which may also exceeded the Power BI cell character limit (of 32,766 characters for a single text value). If you exceed this limit - Power BI imports the data from Power Query, but simply truncates the text value at the maximum character limit (with no warning!!!). Therefore, it's not a valid WKT string, so it fails to render in Icon Map.
🪄 What options do we have?
For this example, I'm using the very detailed Estuary Drainage Catchment shapefile for NSW (which I downloaded from NSW SEED database here). The first step is to convert the Shapefile to a WKT (or well known text) CSV file, using a converter program (like the QGIS - check out my blog here for more details). Quickly opening the CSV file in Excel - we can see an immediate problem, where the text character limit has been exceeded for a number of polygons (interestingly, Excel allows one extra character, where the limit is 32,767).
So we have several options available to us:
Simplify the layer: If the spatial layer has been derived from some algorithm (such as a catchment or river delineation function) it can result in a LOT of vertices (which adds to your text string). You can look to simplify the layer to reduce the number of vertices, noting that this could reduce the accuracy of your layer if you need it to match exact boundaries. MapShaper is a great tool for this, as it preserves the snapping of adjacent polygons (where the simplify tool in QGIS results in small discrepancies at the boundaries of adjacent polygons).
Reduce the precision: by reducing the number of decimal places in the coordinate pairs for the polygon vertices. You can see in my example the long - lat pairs are to 12 dps (149.882448887925 -37.4566315396983) which takes up a LOT of characters. I'd recommend truncating at 4 dps, which I've found works well. You can do this using the 'Snap to Grid' tool from the QGIS processing toolbox.
Split then concatenate WKT text strings: After trying options 1 and 2 (or if we don’t want to reduce the accuracy of the spatial file), our WKT spatial file may still exceed the Power BI character limit. Therefore, we need to do some Power Query and DAX magic to split up our WKT file into segments (using Power Query) that can be imported, and then stitch them back together using DAX (check out Part 1 of this blog where I go over this method in great detail).
In this video, we’re looking at option 1 and option 2 in more detail.
I hope this is useful for your study - and please reach out if you have questions, improvements or other mapping or environmental challenges in Power BI you'd like the DiscoverEI team to investigate.
💙 Connect
We'd love you to connect with the DiscoverEI team:
Email: info@discoverei.com
Website: https://www.discoverei.com/
Blog: https://www.discoverei.com/blog
YouTube: https://www.youtube.com/channel/UCFF5eaUsht-WzTdjEAgPDtQ
LinkedIn: https://www.linkedin.com/company/19050025/
Twitter: https://twitter.com/discover_ei
PowerBI4 Enviro's Meetup: https://www.meetup.com/en-AU/powerbi4enviros/
If you want to learn more about Power BI mapping visuals, then register for our Power BI 4 Enviro’s monthly meetup (https://www.meetup.com/en-AU/powerbi4enviros/), or if you’re keen on presenting at a future session then contact Christian (cborovac@discoverei.com).
Power BI Training Courses
If you’re interested in learning from our team, then we have a range of 2-day Power BI training courses available to fast-track your Power BI journey.
Click on the links below to learn more, and get in touch with the team today!
Purchase Training
Do you want to learn how to design engaging and intuitive Power BI reports, which communicate your key insights at a glance and tell your data story?
Our two-day Power BI training course is designed specifically for Power BI Professionals and Data Analysts, and provides our best practice tips, tricks and hacks to help you transform your data! We provide this course online, in-person group sessions, or customised in-house training for your team. Places are limited (max 8 participants per class) so secure your spot today!