/ Azure

Using Power BI, R and Azure to visualize car sharing services

Because I do not have a car I frequently use various car sharing services, like car2go or Felyx. These companies offer car / scooter sharing services, which means their vehicles are located around the city and everyone with a subscription can use the vehicles. To find or open the car you can simply use your mobile phone app. Extremely easy to use and just great services.

So what is the issue?

Unfortunately searching the nearest car or scooter requires me to open multiple different app's, so I decided to make a website ridecheck.nl to show all available vehicles around me (works only in Amsterdam for now).
This was already pretty cool, as it saved me quite some time!
However having all this data is even cooler. So in this post I will outline how I have set this up and show my initial dashboard which I have built in Power BI to visualize the data, I hope to write some more posts on this and do some actual analytics on this dataset.

How to get the locations?

Quite simple, lets start with how to get and store data. The sharing services providers provide HTTP endpoints for their vehicle locations which are publicly accessible. I query the endpoints using Azure App Service. The result will be stored in Azure Blob Store in JSON files, I do not store the results in a DB at this point because once saved the results will not be queried a lot.
The simple design overview can be seen below.

Sample overview

Building the website

Site url: ridecheck.nl

Once I got the data, I want to show this in an easy as possible way to users. I choose to build a very simple website focused on mobile. This simply shows a big map with you current position and the position of the available rides nearby, with detailed information about each ride when you click on this.
For the server side I used NodeJS and Express, which are hosted an Azure App Service.
Front and is also very simple Google Maps API, Bootstrap and some vanila Javascript. I might post the complete source code on Github later.

Mobile site view

Show me the data!

In order to easily visualize the data I am using the great power of Power BI, see below. Press the big play button to see how all the cars are moving around over time.

Final data transformations with R

Lastly I want to calculate the distance from point to point in the dataset. This is not in the data, only the location of each time stamp is in the data, to calculate this I am using R in Power BI. The library to calculate the distance is geosphere, the distance calculated will not be very accurate as the shortest distance between the two point will be calculated not taking roads into account.

The complete R function:

library(geosphere)
cars= unique(dataset$carId)

dataset$distance =0
for(i in 1:length(cars)){
  currentVehicle = dataset[dataset$carId == cars[i],]
  for(y in 1:nrow(currentVehicle)){
    if((y != 1) && (currentVehicle[y-1,'lng'] != currentVehicle[y,'lng'])){
      lat1 = as.numeric(currentVehicle[y-1,'lat'])
      lng1 = as.numeric(currentVehicle[y-1,'lng'])
      lat2 = as.numeric(currentVehicle[y,'lat'])
      lng2 = as.numeric(currentVehicle[y,'lng'])
      distance = distGeo(c(lng1,lat1),
                         c(lng2,lat2))/1000
      dataset$distance[dataset$Index == currentVehicle$Index[y]] = distance
    }
  }
}
output <- dataset

Final state and future

Below you can find the final build solution.
In the future I would like to add more sharing services to the list and add more cities to be able to make this available to a broader public.

Final architecture

Vincent van Wingerden

Vincent van Wingerden

I work for Microsoft as Technology advisor on the area of big data and advanced analytics. When I have made another cool demo I hope to write about it here. All opinions are my own

Read More