This map can be found in my #VizForSocialGood submission that I have created for UNICEF and which I mentioned in my previous post (Design & viz will save the world).
Now I am focusing on the technical details, how you can build curved lines on a map.
It depicts the migrants’ route from the Countries of Origin to the Destination over the world in 2015.
You can download the datasource here: Refugee and migrant children xls. and feel free to apply it in order to replicate my map.
The structure of the raw dataset looks like this:
It represents information about the Origin- and the Dest regions with the number of migrants.
While I was creating the map I realized that I required some additional information beside the received dataset.
- I needed the long/lat data of the countries. I used the following file: https://developers.google.com/public-data/docs/canonical/countries_csv and I linked the geocoordinate information to the countries.
- In order to draw a route whether straight or curved it was necessary to have two records for each origin/destination country combination. The first one for the start and the second one for the end. In this case, number 1 is the starting point and number 2 is the end.
See the result below :
Now the dataset is ready for Tableau! After I had loaded the data, I had to create some new calculations.
Just follow me step by step.
- How to get the curved lines depicting the routes?
I had to calculate the Quadratic Brezier curve which starts at t = 0 and ends at t = 1. It is useful, especially when fitting together a string of Brezier curves, to allow an arbitrary parameter interval: t0 ≤ t ≤ t1
This is the formula:
Let’s go to create the Brezier value which starts at 1 and end at 100.
- Next step is to generate the Brezier Value of 100 points varying from 0 to 100. It was done by creating a bin. Set the size to 1. These points define the all the points of the lines.
IIF([Path Order] = 1,1,100)
- Now I calculated the ’t’ value based on the Index field. Since the ’t’ should be t0 ≤ t ≤ t1 therefore, I had to subtract 1 and then divided by 100.
- The next step is to calculate the new latitude/longitude radians fields. Those are required to get the distance between two points on the map.
- Newlat1 Radians
- Newlat1 Radians
- Newlon1 Radians
- Newlon2 Radians
- Using the long/lang radians field I was able to get the distance.Since the surface of a sphere is curved, finding the distance between the origin and the destination countries along that surface will require some non-Euclidean geometry. The haversine formula calculates the great – circle distance between longitude/latitude points assuming a spherical earth. Vincenty’s formula takes into account that the earth is not perfectly spherical, by calculating the ellipsoidal distance between two points on the surface of a spheroid
2*asin(radians(lat1 – lat2) ) + cos( radians(lat1) ) cos( radians(lat2) )sin( radians(lon2 – lon1) /2))2))
◊◊ This formula in Tableau ◊◊
2*ASIN(sqrt(power((sin(([Newlat1 Radians]-[ Newlat2 Radians])/2)),2)+ cos([Newlat1 Radians])*cos([Newlat2 Radians])*power((sin(([Newlon1 Radians]-[Newlon2 Radians])/2)),2)))
- Convert back to (lat,long). I needed some beautiful calculation to get those.
◊◊ These formulas in Tableau ◊◊
sin((1-[t])*[Distance Radians])/sin([Distance Radians])
sin([t]*[Distance Radians])/sin([Distance Radians])
[A]*cos([Newlat1 Radians])*cos([Newlon1 Radians]) + [B]*cos([Newlat2 Radians])*cos([Newlon2 Radians])
[A]*cos([Newlat1 Radians])*sin([Newlon1 Radians+ [B]*cos([Newlat2 Radians])*sin([Newlon2 Radians])
[A]*sin([Newlat1 Radians]) + [B]*sin([Newlat2 Radians])
Now I was able to convert back the previously calculated fields to lat/long.
lat= atan2(z,sqrt(x*x+y*y)) lon = atan2(y, x)
◊◊ These formulas in Tableau ◊◊
- So by now, I’ve had the new long and lat coordinates, but to be able to draw the curved lines I had to generate the degrees. I promise this is the last step.
- Newlong Degress
when 1 then window_min((avg([Origin Longitude])),FIRST(),FIRST())
when 100 then window_min((avg([Target Longitude])),LAST(),LAST())
- Newlat Degress
when 1 then window_min((avg([Origin Latitude])),FIRST(),FIRST())
when 100 then window_min((avg([Target Latitude])),LAST(),LAST())
We are close to the end. Let’s go and create the curved lines.
- Set the Geographical role of the ‘Newlong Degrees’ to Longitude and the ‘Newlat Degrees’ field to Latitude.
- Set the Mark type to line.
- Add the ‘Path’ to the Detail shelf and the ‘Brezier Value(bin)’ to the Path.
- Add the ‘Newlong Degrees’ to the Columns and ‘Newlat Degrees’ field to the Rows.
- Set the Tableau Calculations in the Degrees field using Specific Dimensions, select ‘Path’ and ‘Bezier Value’, use the Deepest level and restarting from every ‘Path’.
And VOILÁ! 🙂
Be sure you set everything and used the bin field in the Path shelf in order to avoid getting not continuous lines. Otherwise, don’t worry because Tableau has the best community and if you just get stuck somewhere a clever guy will help you. 🙂
If you need any further explanations don’t hesitate to get in touch with us in the comments below or you can also find me on twitter and you can share your map with me if you like.