GIS Blog

A Humble Table Join Saves the Day

Table joins are an easy and convenient way to link data in spreadsheeets to spatial layers.  Here we cover a good example of one simple table join deciphering some codes in a soils layer.

Those familiar with the J: drive will know that we hold many national scale datasets, from soils to land use to elevation, in a mixture of raster and vector formats.  Many will also know that sometimes the attributes are less than forthcoming in terms of meaning.  There are several examples of this, but let’s look at one in particular: S-Map.

S-Map comes to us through Mannaki Whenua (Landcare Research).  This a vector polygon layer of soil families with 238,231 polygons.  Here’s a look at the raw data layer:

Too much detail to see at this scale so I’ll zoom in to somewhere closer to home:

There’s clearly not full coverage but what’s there looks quite detailed.  And here’s a peek at the attribute table:

For those areas mapped, this is a valuable resource with lots of quantitative information about soils.  But the view of table above shows us an issue.  If you can make sense of these values, you’re doing better than I am – these are coded values rather than human-readable text descriptions.  I recently had some students who wanted to map soil texture.  It’s easy enough to symoblise the polygons by the texture code (TextureDom, or dominant texture), but without knowing what it means, it’s next to useless.  Knowing (hoping) that there was some sort of Rosetta Stone somewhere that would allow some translation, I set out on a search.  What we’re in need of here are metadata – data about data, essentially.

My first port of call was the Manaaki Whenua data portal:

First off – I’m in for a shock!  S-Map is no longer available for downloads to commercial users…  (Good thing I had already downloaded it to J: some time ago…)  Second, I couldn’t find anything that would allow me to translate the texture codes.  Notice the “Metadata” tab in the screenshot above – for many layers, you can often get more detail on the data and attributes from here, either is text for or as downloadable documents, but in this case, nothing.

Back to square one, or rather back to the J: drive.  In perusing the S-Map folder (with Windows Explorer rather than ArcCatalog) I did manage to find this little gem:

Which contained this:

So here’s my translation of what the codes mean.  Recall that I’ve got over 328,000 polygons so I have no intention of going through line by line and adding in the description – I wouldn’t even make a summer scholarship student to that!  I could add a new text attribute and then run a series of Select by Attributes and Field Calculations, but instead I’ll opt to use a table join, one of the most powerful ways of enriching existing data layers.  The basic idea is that I’ll create a simple table with the Texture Codes and Descriptions shown above and then join it to the spatial layer based on the Texture Codes.  As long as I have attribute values in both my spatial layer and my spreadsheet (this could also be a text or csv table) that match then I can link them together.  So here’s my simple table, done in Excel:

Three things to note here:

  • the first line is what will become my field names;
  • No spaces, no odd characters like % or & and no numbers at the beginning of the name;
  • The field names between table and layer don’t need to match but the actual values must in both format (e.g numbers versus text) and value.

Next, I can add that sheet to my map using the Add Data button – it will appear in the Table of Contents though you’ll only see it if the List by Source button,, is ticked (this will happen automatically when you do the join).  The figure below shows the table added and open:

Next step is to join this table to the S_Map_All_Attributes attribute table.  Do so by right-clicking on the layer name and going to Joins and Relates > Join…:

(The link between the files is done on the next window:

Here I’m joining the data from TextureCode field in Sheet1$ based on the TextureDom attribute in the spatial layer.  If I click the Validate Join button it will go through and check the validity of the join and how many connections it can make – not a bad thing to do:

This tells me that 237,082 of the 238231 records can be matched.  The records that didn’t match likely don’t have a value for TextureDom.  After ticking Close > OK, the TextureCodes are now joined to the S-Map layer table at the far right:

Great!  Now I’ve translated those codes to something useful – I can use this for analysis and for mapping:

Before we move on though, there is an important final step in this process.  The join is a temporary thing and really belongs to the map document.  To make it permanent, I should export this layer as a new one (right-click the S-Map layer > Data > Export Data… and save as a new layer).  Then it will always be a part of the new layer.

I rank table joins as amongst the top five most useful tools in ArcMap.  Lots of useful data exist as spreadsheets or text files and as long as you can establish a link to a layer’s attributes, you can make use of those data.  In fact we’ve talked about table joins before with regard to mapping census data.  The example above is a slight different type of join.  With the census data, these is a one-to-one match between the spatial data (e.g. meshblocks or suburbs or regions) and the census data.  With the S-Map data, there were many instances of each Texture Code, so this was a one-to-many match.  You saw how simple my spreadsheet table is – ArcMap essentially used it as a lookup table to match the correct description to the correct code.

There are many examples of attributes in data such as this (the Land Resource Inventory and Land Environments of New Zealand, both on J:, come to mind).  If you’ve got some metadata that translates the codes, it’s often quick and easy to create you’re own file and then join it to your spatial data, thereby making hard to understand data easier to interpret. The tricky part if often finding the right metadata.


• May 25, 2018

Previous Post

Next Post

Leave a Reply

Your email address will not be published / Required fields are marked *