dashboard / Other / Tableau

Draw curved lines on a map!

2017-02-13

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.

Datasource

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.

1.step

2.step

  • 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.

3.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.

4.step

  • 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)

5.step

  • 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.

    ([Index]-1)/100

6.step

  • 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

  RADIANS(WINDOW_MIN(min([Origin Latitude]),FIRST(),FIRST()))

  • Newlat1 Radians

  RADIANS(window_min(min([Target Latitude]),LAST(),LAST()))

  • Newlon1 Radians

  RADIANS(window_min(min([Origin Longitude]),FIRST(),FIRST()))

  • Newlon2 Radians

  RADIANS(window_min(min([Target Longitude]),LAST(),LAST()))

7.step

  • 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(([v]-[ Newlat2 Radians])/2)),2)+ cos([Newlat1 Radians])*cos([Newlat2 Radians])*power((sin(([Newlon1 Radians]-[Newlon2 Radians])/2)),2)))

8.step

  • Convert back to (lat,long). I needed some beautiful calculation to get those.

 

A=sin((1-t)*d)/sin(d)

B=sin(t*d)/sin(d)

x=A*cos(lat1)*cos(lon1)+B*cos(lat2)*cos(lon2)

y=A*cos(lat1)*sin(lon1)+B*cos(lat2)*sin(lon2)

z=A*sin(lat1)

◊◊  These formulas in Tableau  ◊◊
  • A

  sin((1-[t])*[Distance Radians])/sin([Distance Radians])

  • B

  sin([t]*[Distance Radians])/sin([Distance Radians])

  • x

  [A]*cos([Newlat1 Radians])*cos([Newlon1 Radians]) + [B]*cos([Newlat2 Radians])*cos([Newlon2 Radians])

  • y

  [A]*cos([Newlat1 Radians])*sin([Newlon1 Radians+  [B]*cos([Newlat2 Radians])*sin([Newlon2 Radians])

  • z

  [A]*sin([Newlat1 Radians]) + [B]*sin([Newlat2 Radians])

9.Step

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  ◊◊
  • Newlat

  atan2([z],sqrt(power([x],2)+power([y],2)))

  • Newlon

  atan2([y],[x])

10.step

  • 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

   case [Index]

   when 1 then window_min((avg([Origin Longitude])),FIRST(),FIRST())

   when 100 then window_min((avg([Target Longitude])),LAST(),LAST())

   else degrees([Newlon])

   end

 

  • Newlat Degress

  case [Index]

  when 1 then window_min((avg([Origin Latitude])),FIRST(),FIRST())

  when 100 then window_min((avg([Target Latitude])),LAST(),LAST())

  else degrees([Newlat])

  end

We are close to the end. Let’s go and create the curved lines.

FINAL STEPS

  • 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Á! 🙂

 

Final thoughts

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. 🙂

 

 

 

 

 

 

 

 

 

The father of this tableau technique is Chris DeMartini – @demartsc. Check out his post about the topic: Great arcs in Tableau

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.

@IvettAlexa

1 likes

Author

Ivett Kovács

I love taking datasets into beautiful and informative visualizations. Besides data, I am also very passionate about traveling, surfing, cooking and exploring the outdoors with my dog.

Comments (4)

  • March 13, 2017 by Adolfo

    Adolfo

    Hi Ivett,

    Thanks for this great tutorial! I have a problem though, I want to make my lines with a different thickness depending on a variable value but when I drag this variable to my size card I just got circles of different sizes at the end of my lines! Also how did you make to get transparent background in the Country labels in your map? Thanks.

    Adolfo

    • March 13, 2017 by Ivett Kovács

      Ivett Kovács

      Hi Adolfo,

      I used the Normal Tableau Map and just clicked out the Base option.I want to check what causes the size issue.Could you please send me your workbook?

  • May 20, 2017 by Helene

    Helene

    I’m really impressed with your writing skills as well as with the layout
    on your weblog. Is this a paid them or did you customize it
    yourself? Anyway keep up the nice quality writing, it iss rare to see a great
    blog like this one these days.

    • May 22, 2017 by Istvan Korompai

      Istvan Korompai

      Hi Helene,

      Thank you for the kind words.
      This is one of the freely available themes.
      What do use Tableau for, are you a regular user? 🙂

      Cheers, Istvan

Your email address will not be published.