This post covers how a map of tourist spending for 2019 was done.  Table joins were a critical part of this.  We also look at the mapping process and, more specifically, how to add pie charts to your map.

Tourism, as the most significant industry in terms of foreign exchange earnings, is one of the major sectors of the New Zealand economy. Lincoln has a good history of contributing positively to the cause.  Part of that means that every year, we develop maps that aim to show the impact of international and domestic tourism in relation to regional GDP and how they change over time  In this post, we’ll cover the process from go to whoa of how those maps come about.  But first here an example.

Here’s the map from 2017 to give you a sense of what the maps do – we normally do the whole country but that year we only mapped the South Island:

Two primary things we’re trying to show in this map:

  • The % of regional GDP that tourism contributes
  • The split between domestic and international spending as a proportion and the relative size of the spend by region

The first is shown by the graded colours of the regions and the second is shown by the pie charts, which are scaled in size by the magnitude of the tourism spend in that region.  While it’s difficult to judge exactly what the total spend is, the size of the pie charts helps to communicate a sense of the relative differences (i.e. compare Canterbury with the West Coast).  So hopefully that all makes sense (if it doesn’t, I haven’t done my job…)

What I’d like to do now is go though how we got to this final stage for data up to March 2019.

As a usual starting point for this mapping, David Simmons sent me some spreadsheets which he had gotten from MBIE and Stats NZ.  Here’s the spreadsheet that gives usthe data on gross domestic product by region:

The second spreadsheet from MBIE breaks the tourism spend down by regions as well:

Great!  I’ve got some data to work with now.  But, how am I going to map this?  What make these data spatial?  How do I take a spreadsheet and map it?  This brings us to, arguably, one of GIS’s more powerful tools – table joins.  There are stacks of data out there in spreadsheets that can enrich spatial analysis, so long as we can link them to spatial data layers.  In both these cases, the spatial information is in there as the region name.  It’s not coordinates but the names relate to areas we all know and love.  That should get us thinking about existing data that relates, like a layer of the regions:

For the lastest version of these data, I went to the LINZ Data Service and downloaded a copy.  The key thing is that I’ve got an attribute that also has the region name so I should be able to link the spreadsheet to the layer.

BUT – notice a slight difference: the spreadsheets list the region name but without “Region”, as the spatial layer does – minor inconvenience but not unsurmountable.

ANTOHER BUT: on close inspection there is another issue – the GDP data combine Nelson and Tasman while the other data don’t.  I’ve got no basis for knowing how to split up the two regions GDP values so I’ve got to combine the tourism spend amounts for these two regions and combine the two polygons on the map (the Merge tool in the ArcGIS Pro editing tab worked for this –  I just had to select both and find the Merge tool in the window of often used tools:

Next, I can think about joining the spreadsheets to my regions layer so I can get the values into the table for mapping.  Table joins work off the idea that if there is a “common key” between two tables, they can be joined based on those values.  This means there has to be a one-to-one match between the keys – case and text are important – they have to match.  I’ve already mentioned the issue with the region names, but there are a few other formatting issues I need to sort out first.

As they are, I could add the two spreadsheets to my map but they won’t play nicely with my layer just yet.  The tables must be arranged in a few specific ways:

  • The first row of any table must be the names of attributes
  • Attribute names must have no spaces, start with a number, or have any special symbols
  • No special formatting (e.g. cell colours, formulae, borders, etc)

So I’ve set up a new sheet that will combine all the data into one, simplified table:

I’ve just been copying and pasting here – the region names are there twice to allow me to make sure I’ve got the right data in the right place.  The first three columns came straight out of my spatial layer’s attribute table so I know that they’ll match nicely.  I’ve got my domestic and international spends, the total (so that I can scale the size of the circle), the total GDP and a value for the % of GDP for the tourism spend.  Now I’ll do the join back in Pro.

First, I’ll add the sheet to the map as a standalone table just like any other data using Add Data:

To do the join, I right-click on the layer name and go to Joins and Relates > Add Join.  This opens a new tool pane:

It picks up the spreadsheet table that’s on the map (truth be told, I don’t need to add it to the map – I can find it using the folder button).  The Input Join Field is the attribute that has the common key with the Join Table.  I could have used any one of three attributes but here I chose REGC2018_V field with has a unique number for each region.  Why?  That one’s less reliant on any text values Imay have worked with – it’s just a bit more reliable and less prone to any errors on my part.  Clicking Run then joins up the spreadsheet table to the spatial layer’s table:

The four fields with blue headers are from my spatial layer – all the others are from the spreadsheet table.

Almost done with this – but I need to recognise that this join is somewhat temporary.  To make it permanent, I can just make of copy of this layer and they will be joined in perpetuity (right-click layer name > Data > Export Features).  This now gives me a full-blooded layer to work with for mapping.

As noted earlier, I want to show both % of GDP spend by region and also the breakdown of intentional and domestic tourists’ spend, scale by the total magnitude of that spend.  I’ll do that first

For the %GDP spend, we used classes and colours that we’ve used in previous years – five classes (0 – 5, 5 – 10, 10 – 15, 15 – 20, > 20) with specific colours – that makes it easier to compare maps from different years.  These get set in the Symbology pane:

Next up is the pie charts – I’ve added a copy of the Regions layer to do this (I need one layer for each of the symbologies).  To get a pie chart for this layer, I set the Primary Symbology to “Charts” and the Chart Type to “Pie” in the symbology pane:

I’m not particularly happy with the haphazard look of the pie charts and they’re all the same size so two more tweaks are needed.  First, in the Appearance section, set the Size Type to “Field” and the Field to “TotalSpend” (the total tourism spend in my attribute table – now pie chart size varies with the magnitude of the spend:

Notice how I’ve also got a new legend item that helps us interpret what the size means.

Another tweak – in the Display Options, set the Orientation to “Clockwise” and then they all line up a bit more consistently to my liking:

We’re almost done – and definitely done with the hardest bits of this map.  After some more finalising (well, more like a few hours – it’s amazing how much time you spend getting maps to look “just right”) – this involved a lot of back and forth with David on how we wanted things to look.  Here’s the finished product:

You’ll notice that it looks a wee bit different from what we looked at earlier – when switching back and forth between a Map and a Layout, the sizes can change so there’s a lot of back-and-forth adjusting.  This map debuted in one of David’s thought pieces that went to ministers and organisations like MBIE, DOC and Tourism NZ amongst others.  I hope it helped.  Hard to know what it will look like next year…

So hopefully in this piece you’ve gotten to see how useful information hidden away in Excel spreadsheets can, under the right conditions, make their way onto maps for more visual presentations. It’s like setting free the spreadsheets…  Table joins are a very powerful way of enriching mapping layers, one of the most useful techniques I know of.  ( Another example here)

You’ve also gotten a peak behind a map making process that aimed to best present these data.  I would say that we spent somewhere between four and five hours getting this together, helped by the fact that this was the fourth time we have taken these annual data and mapped them this way.

Hope that was enlightening.

C (with special thanks to David)