Online tableau training, tableau tips, & video tutorials

Technical Features

Memberships

How to Use Tableau’s MAKEPOINT and MAKELINE Functions

Preview

Instructor

Ryan Sleeper

How to make hub and spoke maps to connect locations

Hub and spoke maps are a great choice for visualizing flight routes, migration patterns, and other paths. In this video, learn how to use Tableau’s MAKEPOINT and MAKELINE functions to connect Playfair Data’s office location to Ryan’s ten favorite barbecue restaurants.

Hi. This is Ryan with Playfair Data TV. And in this video, I’m going to show you how to the use Tableau’s MAKEPOINT and MAKELINE functions to create hub and spoke maps in Tableau.

Hub and spoke maps are a great way to show flight paths, or any type of other travel path or migration pattern, but we’re going to use it for a much more fun reason. And I’m going to show you how to create a map that looks like the map you see here on the screen that is showing the location of the Playfair Data office to my 10 favorite barbecue restaurants in the Kansas City area.

So first thing we need to do over in Tableau Desktop is create our data source to create this map. There’s a little bit of a trick to creating– to using the MAKEPOINT and MAKELINE functions because it doesn’t use the generated Latitude and Longitude fields that come with Tableau.

So you’re probably familiar with this. If you’ve ever got a field in your dataset that has a globe icon next to it, that means Tableau recognized that as a geography, and it generates the fourth and fifth generated fields. Those are Latitude and Longitude. However, if we want to use these special functions, we need to have our own fields that show the latitude and longitude.

So the first thing I’m going to do before I show you how to use these functions is show you the underlying dataset. Note that there are two columns that include my own latitude and longitude coordinates. Those are coordinates that I looked up for each address tied to my 10 favorite barbecue restaurants and the Playfair Data office. So you will need those two– at least those two– in your dataset.

Most people, when they use these functions, have two sets of latitude and longitude pairs. They’ll have Origin Latitude and Longitude and Destination Latitude and Longitude. I’m going to show you a shortcut that allows you to do all of this by using just one set of latitude and longitude coordinates.

So I’m going to go back over here to Tableau Desktop in the Authoring interface, and the first calculated field we need to create will compute the destination points. So this involves a calculated field, and I’ll call this my Destination Points. And the formula is that function MAKEPOINT, and then Latitude, comma, Longitude.

So that is the entire formula. That function is called MAKEPOINT because it is literally making a point out of these latitude and longitude coordinates. I’m going to click OK.

By the way, if you are creating your own latitude and longitude points, there is a good chance that, by default, Tableau won’t recognize those as geographic coordinates. That happens if, perhaps, you’re using some weird abbreviation for those field names or they’re not in the correct format. In either of those cases, there might be one extra step required, which would be to set the geographic role for those Latitude and Longitude measures.

And you can do that by just right-clicking on each one and hovering over Geographic Role. So note I’ve tied Latitude to Latitude and Longitude to Longitude. You might need to go through that step if you do not see a globe icon next to those two fields in your dataset.

We’ve also gone ahead and created the first calculated field to create my destination points. We now need to create a calculated field that computes my origin points, or origin point– singular.

So here, you’re going to have two options. If you have two sets of coordinates in your underlying data– so if you have a latitude and longitude pair for your destinations, as well as a latitude and longitude pair for your origins, this formula would simply be MAKEPOINT Origin Latitude, comma, Origin Longitude. But as you saw in my dataset, there’s only a single pair of latitude and longitude coordinates.

Instead of pointing this to its own set of coordinates, we’re going to isolate the coordinates for my origin by using level of detail expressions. And I just copied and pasted this formula in here, but I will explain what it’s doing. I will also provide it in the related content below this video, so all you have to do is copy and paste it and replace Playfair Data with your own origin destination.

So this is saying the same function, MAKEPOINT, but the Origin Latitude is this piece of code right here. Note it’s just saying it’s a level of detail expression. I know that because of that open curly bracket.

If you don’t type any expression– so if you don’t type FIXED, EXCLUDE, or INCLUDE– that open curly bracket will default to being a FIXED level of detail expression. So if that’s confusing you, this would be the exact same thing as if I were to have wrote FIXED, colon, then space. But you don’t need that when the act– when the expression is FIXED and we’re not addressing any specific dimension. So this is just a way to make the code a little bit more elegant.

And we’re saying, take the average of when the location equals Playfair Data, then show the Latitude. So that is my origin– my origin latitude– is that piece of code right there.

Then I just do the same thing for (Longitude). That’s the second piece of the code. So from there to there, we’re just doing the exact same formula, except this time, we’re isolating the longitude for when the location equals Playfair Data.

I had to create some type of use case to illustrate this. But I do want to point out that I have hardcoded Playfair Data just to kind of create this fun example trying to map the paths between our office and these barbecue restaurants. But you could change the origin to be whatever location you would like, and you could also parameterize that origin.

So instead of hardcoding the string here, you could create a parameter with a data type of string, type in whatever field you’ve got for your different locations, and allow the user to change the origin on the fly. That’s going to change how the lines are being drawn between all the points. But for now, we will call this good. I’m going to click OK.

So we’ve got our origin point and our destination points so far. There’s one more calculated field to get this to work properly, and that is our connecting line– “line-suh,” plural. So connecting lines– and instead of the MAKEPOINT function, we’re going to use the MAKELINE function. And that starts with the function MAKELINE, open parentheses, Origin Point, as the first points that it’s going to look at, and then it’s going to draw a line to our Destination Points.

So fairly simple, elegant calculated fields– that’s the entire formula that time. We’re just going to draw a line between our origin points– origin point, singular– and our destination points created in the first step. I’m going to click OK.

The next thing we’re going to do is set up our map. It’s important to make sure you use the Latitude and Longitude fields from your dataset. Latitude is the vertical location on a map, so we need to create a y-axis out of it. To do that, it needs to go onto the Rows Shelf, so I’m going to drag Latitude to Rows.

Note that the aggregation is average. That is what you want to get this to work. Longitude is the horizontal location on the globe, so it needs to go on the Columns Shelf in order to generate an x-axis.

So Longitude onto columns– also with an aggregation of average. Because I assigned a geographic role to those fields, we see a map automatically generated for us. And that one dot on the map currently represents the average latitude and longitude pairs across everything in my data file that I showed you with those 10 barbecue restaurants and our office.

Critical step to get this to work– that is just something you have to memorize. To use the MAKEPOINT and MAKELINE functions, we need to change the map style from the default, which is a symbol map, to be a map– or a mark type of Map. You won’t see anything change immediately. The mark did get just a little bit smaller, but now we can use these functions on this map. And all we have to do from this point is drag the Connecting Lines calculated field– so the third and final calculated field– to the Detail Marks Card.

And you see this nice hub and spoke map come together, Playfair Data is the origin point– that’s what’s in the center– and then we’ve got these lines connecting to all of our destination points, the first calculated field. From here, it’s all about formatting. There’s a couple of new map styles available as of Tableau 2019.2.

If you go to Map and Map Layers, I might switch that to be Streets, just so that if I were to zoom in, I could see what streets these barbecue restaurants were located on. I might wash it out just a little bit– 40% or 50%– so I can still see the lines really boldly in the forefront there.

A couple of things I might do– I might convert this hub and spoke map to a dual-axis combination chart that uses a symbol map on one axis and a hub and spoke map on the other. I show you how to do this in more detail on another video, but what I would do is duplicate the Latitude pill on the Rows Shelf by holding down the Control key while I drag it right next to itself. We’ve now got the same map on two rows, but those two maps can be edited independently of each other.

So on the second row, I’m going to change the mark type to Circle. You see just one circle, but I need to change the level of detail to Address, because I had 11 addresses in my data file. I no longer need the connecting line for the second row, so I’ll get rid of that.

I can convert this to a dual-axis map by clicking on the second Latitude pill and clicking Dual Axis, maybe make the circles a little bit bigger so we can see them. I’ll change the color of my lines to black so they’re a little more bold, and maybe I will also color those circles based on whether it is an origin or a destination.

Here’s another example of using Tableau in the flow. I’ll just double-click on the Marks Shelf and type Location equals Playfair Data. That’s a Boolean statement, so either the location is going to equal that text or it is not. If I click Enter, it’s been added to the Detail Marks Shelf. But now if I drag it to Color, everything that equals Playfair Data will be colored one thing. Everything else will be colored a second thing.

And of course, I can modify those, maybe make our office the black point on the view, but our destinations red for different barbecue restaurants. And you could take this a step further, but there’s just a introductory example of how to use Tableau’s MAKEPOINT and MAKELINE functions to create a hub and spoke map in Tableau.

This has been Ryan with Playfair Data TV – thanks for watching!

Related video: An Introduction to Tableau Calculated Fields
Related video: An Introduction to Mapping in Tableau
Related video: Unboxing New Map Styles in Tableau 2019.2 and Mapbox
Related blog post: How to Make Hub and Spoke Maps in Tableau
Related chapter: Innovative Tableau – Chapter 28 – How to Make Hub and Spoke Maps

Calculated field to create “destination” points

MAKEPOINT([Latitude],[Longitude])

Calculated field to create an “origin” point (replace “Playfair Data” with your own origin location or parameterize it)

MAKEPOINT({AVG(IF [Location] = “Playfair Data” THEN [Latitude] END)},{AVG(IF [Location] = “Playfair Data” THEN [Longitude] END)})

Calculated field to connect an origin to one or more destination points

MAKELINE([Origin Point],[Destination Points])