Adatis

Adatis BI Blogs

Displaying vector data on maps in Power BI

Something I have enjoyed doing in the past is making maps, especially ones showing how various things move around London. One thing I did was take some open data on usage of London Santander bikes and passed it through the Google Waypoints API and visualised it using Mapbox:Which got me thinking, is it possible to do something similar using Power BI? Microsoft introduced the geography data type with SQL Server 2008 and more mapping providers are making versions of their tools available in Power BI, including Esri and Mapbox. From a business perspective, IoT devices are enabling the collection of greater volumes of geographical data for companies to use in their analyses.Using the GEOjson files I had previously produced for the project above, I set about importing them to a SQL DB instance in Azure as a geography data type. This involved reading the file into a variable and then iterating over the index position of each individual ride.-- load the geojson file into a SQL variable DECLARE @geojson VARCHAR(max) = (SELECT * FROM OPENROWSET (BULK 'C:\path\to\my\file', SINGLE_CLOB) as j) -- use the index position to iterate over each ride SELECT geography::STGeomFromText('LINESTRING(' +STRING_AGG(CAST(long + ' ' + lat as varchar(max)), ',') + ')',4326) FROM ( SELECT * FROM OPENJSON(@geojson, '$.features[0].geometry.coordinates' ) WITH ( long varchar(100) '$[0]' ,lat varchar(100) '$[1]' ) ) aThis results in a row per ride and visualises pretty well in SSMS. If you are familiar with the geography of London you can make out the river Thames toward the centre of the image and Regents Park towards the top left:This could be overlaid on a shape file of London or a map from another provider such as Google Maps or Mapbox.However, when you try to load the dataset into Power BI, you find that Power BI does not natively support Geography data types. There is an idea you can vote on here to get them supported: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/12257955-support-sql-server-geometry-geography-data-types-iAfter a bit of research it looked like using R within Power BI could be an option. The foundation thinking of this was rather than converting the longitude and latitudes to a geography data type as above I wrote them to a table with an Id to associate each ride and let the ggmap library in R join the dots. Once you have your data loaded into Power BI, you only need a couple of lines of R code to render the map:library(ggmap) qmap("london", zoom=12, maptype="toner-lite") + geom_path(aes(x = long, y = lat, group=LineId), size = .7, data = dataset, colour="#FF69B4", lineend = "round")An important thing to note is that this R library uses the Google Maps API which could cause some privacy concerns. Also before showing this to users you would want to assign a Google Maps API token in Power BI as without it you are using a generally available connection which means your maps will fail to load a lot of the time.The resulting map displays the paths – although the sorting of each row gets mixed up somewhere in Power BI for some paths hence the horizontal lines.And it turns out that the Power BI service does not currently support the use of certain R libraries which use client-server queries over the internet meaning you cannot publish this map.So as a proof of concept it is possible to display vector paths on a map in Power BI desktop, however you will need to wait until Microsoft supports geography data types in Power BI or allows R libraries which call an external service before taking it any further.These links provided the basis for this PoC and would be a good starting point if you wanted to take things further:https://bertwagner.com/2018/01/16/importing-geojson-earthquake-data-into-sql-server/https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/ogc-methods-on-geography-instances?view=sql-server-2017http://angryanalyticsblog.azurewebsites.net/index.php/2017/02/27/power-bi-routing-visual-with-two-lines-of-r/

Power BI Maps Handling Duplicate City Names

The Bing map engine behind the map visualisation in Power BI is very intuitive allowing users to provide textual data such as City or Country or Postcode to map metrics, instead of just latitude and longitude as most other applications do. However one thing which is not immediately obvious is how to get around the issue of duplicate City/Town names. In this blog I will explain how to map your metrics when your data source contains duplicate cities/towns. To start with we have a simple data set with quarterly sales for 6 different cities based in 5 different states which is being loaded from a CSV into Power BI. Straight away you can see that we only have 2 distinct city names.   As soon as we try to map the sales data by city, we get an obvious problem all of the Bristol sales are being assigned to Bristol, England, while the Georgetown sales are appearing in Guyana. Adding state to the Location field does nothing to help the problem as Power BI only reads a single input in the Location field. So the solution is to create a new column containing both City and State data. To do this you need to complete the following steps: 1. Click “Edit Queries” 2. Select the data source in question. 3. Select the two or more columns which contain the data we want to merge eg: City and State      -If additional geographical data is available such as Country then this can be included in the merged column. 4. Navigate to the "Add Columns" menu and select "Merge Columns" 5. Choose the separator value and name the new column For simplicity I have just called this “Merged” and separated the values using only a space. Once the new column has been created it can be dropped into the Location field of the map visualization. As you can see from the screenshot below I now have 6 data points, showing all three variations of Bristol, and all three variations of Georgetown. One final tip, is to ensure you have set the Data Category value for the column in question.  In this case I have set the Data Category to City to help Bing identify the type of data I believe I am providing it. The only problem with this, is if you set the Data Category value incorrectly no data will be displayed as shown in this final screenshot where I have changed the Data Category to “Continent”