Category: GIS

HTML map using R, Part 1

I always tried to learn R, but I never succeed, I did not like it, after enjoying the GUI of PowerQuery, using R was like a self-inflicted torture. But obviously I was wrong as usual, R is not only for data cleaning.

in a PowerBI usergroup , I heard someone talking about R, and I made a mistake of telling him what R can do that PowerBI can not do ?, all I can say is I wish if I did not ask him this question, anyway, in defending R, he said something about generating HTML file and boom, that’s exactly what I was looking for, a file that anyone can read and it has interactivity and it works offline.

my problem is, I build very nice interactive map visualization, but for large distribution, I just print it to pdf, which obviously fail the purpose, not everyone has access to PowerBI service, and for external parties ( client, subcontractor), you can’t reasonably expect them to subscribe to PowerBI service just to see a map :), using Excel 2016 was an option but unfortunately some functionalities are still missing (can’t show a label) and honestly the integration of 3D map with Excel looks like it was a second thought by the developers.

The good news is, R has an excellent integration of PowerBI, so instead of recreating the wheel, just use R for the stuff that can not be done natively in PowerBI.

I hope to publish multiple blogs to document my workflow.

In my real case scenario which I can not publish for obvious reason, we need to view the status (Completed, or not) of nearly 50,000 Piles, show all the piles will clutter the view, instead, I found by accident that a library called Leaflet (the Engine used by MapBox), has the ability of showing layer only when you zoom.

I will use a dummy data, (literally a foundation on a mountain in a New Zealand as an example),

Capture

the data source is an excel file with the pile location and topojson file to show the foundations location all generated using the Excellent QGIS.
the R code is

library(readxl)
library(leaflet)
library(htmlwidgets)
#read Excel Data
data <- read_excel("pile.xlsx", col_types = c("text","text", "numeric", "numeric"))
#read json
map_data <- geojsonio::geojson_read("foundation.json",what = "sp")
######################################
map <-leaflet(map_data) %>%
addTiles(urlTemplate = "https://mts1.google.com/vt/lyrs=s&hl=en&src=app&x={x}&y={y}&z={z}&s=G", attribution = 'Google') %>%
addPolygons()%>%
addCircleMarkers(lng = data$x, lat = data$y,radius = 3)
map
saveWidget(map,file="foundation.html",selfcontained=TRUE,title = "foundation")

the result is a self contained html, at the moment, it is show only 2 foundations and some piles, in the next blog post, we will add zoom, and how to filter layers, and show labels and stuff.

the result is here, and the source data is located in this github repository.

 

Using QGIS to prepare Custom Maps in PowerBI

This blog post is to document my first experience with mapping in PowerBI, usually the reports we produce are time series and pivot tables, but in this case, there is a geographic dimension to the data, so I thought it is a good opportunity to try map chart in PowerBI, it turn out, it is a bit difficult than I thought.

So the data is in the thousand of piles, a lot of piles in a huge area, my first attempt was just to load the data in PowerBI and view it in a map, for some reason, PowerBI show an empty map.

PowerBI expect the data to be in latitude and longitude format, my the data is using easting and northing, I had to convert it, there are plenty of online converter, but there are not practical, as they don’t support batch processing, converting point by point is not an option.

After some googling, I find this excellent open source software QGIS, it was very straightforward, the software automatically convert the coordinates reference system (I think the technical term is reprojection), my data is GDA94 / MGA zone 55 and the result should be in WGS 84.

Voila the data is ready for PowerBI,

map-1

That’s a bit disappointing, PowerBI complain it cannot show all the points ( PowerBI has a maximum of 3500 points per chart).As a comparison, this is how Tableau show the data

tableau

Tableau doesn’t have a limitation on the number of points.

 

Alternative approach

 

As PowerBI cannot show all the point, one solution is to create a shape file that group the points into smaller areas, and again, it was trivial to be done in QGIS.

QGIS will group the point based on a filed you provide.

Group

 

sub-array

And volia

qgis

 

QGIS save the layer in ESRI shapefile, PowerBI require TopoJSON, I used the excellent tool mapshaper.org

Just make sure you import all the files not only .shp

export

Now the TopoJSON is ready to be loaded in PowerBI

topojson

 

Time to celebrate 🙂  not really there is a problem, I want to show different colour based on the status, if a sub-array is completed, I want it to show Yellow, if it is > 75 %, I want another colour and so on, the shape Map in PowerBI does not offer this option.

Fortunately Synoptic Panel   has more options, it use SVG as a map format, which I got from mapshaper.org

filled map

 

Preparing the custom map is a one time operation,  the color will change as per the construction progress, you need just to assign which measures you want to show

measures

What’s Next

As long as your data model is properly built,  you can do a lot of interesting stuff, you can filter by type of works (piling, Tracker installation, PV Modules), you can click on one sub-array and see in details what’s have been installed and what’s missing.