{"id":2126,"date":"2018-05-25T10:49:23","date_gmt":"2018-05-24T22:49:23","guid":{"rendered":"http:\/\/blogs.lincoln.ac.nz\/gis\/?p=2126"},"modified":"2023-05-07T03:31:17","modified_gmt":"2023-05-07T03:31:17","slug":"a-humble-table-join-saves-the-day","status":"publish","type":"post","link":"https:\/\/blogs.lincoln.ac.nz\/gis\/a-humble-table-join-saves-the-day\/","title":{"rendered":"A Humble Table Join Saves the Day"},"content":{"rendered":"

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

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

S-Map<\/a> comes to us through Mannaki Whenua<\/a> (Landcare Research).\u00a0 This a vector polygon layer\u00a0of soil families with 238,231 polygons.\u00a0 Here’s a look at the raw data layer:<\/p>\n

\"\"<\/a><\/p>\n

Too much detail to see at this scale so I’ll zoom in to somewhere closer to home:<\/p>\n

\"\"<\/a><\/p>\n

There’s clearly not full coverage but what’s there looks quite detailed.\u00a0 And here’s a peek at the attribute table:<\/p>\n

\"\"<\/a><\/p>\n

For those areas mapped, this is a valuable resource with lots of quantitative information about soils.\u00a0 But the view of table above shows us an issue.\u00a0 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.\u00a0 I recently had some students who wanted to map soil texture.\u00a0 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.\u00a0 Knowing (hoping) that there was some sort of Rosetta Stone<\/a> somewhere that would allow some translation, I set out on a search.\u00a0 What we’re in need of here are metadata<\/a> – data about data, essentially.<\/p>\n

My first port of call was the Manaaki Whenua data portal<\/a>:<\/p>\n

\"\"<\/a><\/p>\n

First off – I’m in for a shock!\u00a0 S-Map is no longer available for downloads to commercial users…\u00a0 (Good thing I had already downloaded it to J: some time ago…)<\/em>\u00a0 Second, I couldn’t find anything that would allow me to translate the texture codes.\u00a0 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.<\/p>\n

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

\"\"<\/a><\/p>\n

Which contained\u00a0this:<\/p>\n

\"\"<\/a><\/p>\n

So here’s my translation of what the codes mean.\u00a0 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!\u00a0 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<\/a>, one of the most powerful ways of enriching existing data layers.\u00a0 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.\u00a0 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.\u00a0 So here’s my simple table, done in Excel:<\/p>\n

\"\"<\/a><\/p>\n

Three things to note here:<\/p>\n