GIS Blog

1

Turning the Tables on Text

This post demonstrates how to use the field calculator to extract text values from a field and add them to another field.

We join our programme already in progress…

So here’s the thing – I’ve been doing some more analysis on the proximity of tobacco retailers to secondary schools, focusing on areas within 0 – 500 m of each school in Christchurch – all things we’ve covered in earlier posts.  I used Network Analysis to create zones around the schools and then used a tool that joins together attributes from two layers based on where they are with respect to each other.  In this case, I tabulated the number of retailers within each polygon.  Here’s what the layers looks like:

My aim is to summarise the number of retailers in each zone by the schools so I’d like to join the attribute table to my schools layer so I have everything in one place, only I’ve got a problem – the attribute that holds the school name is different in the two layers:

In the secondary schools layer, it’s just the school name (e.g. “Aranui High School”) but in the walking zones layer, it’s “Aranui High School : 0 – 500”.  The “0 – 500” bit came from the network analysis layer and I can’t do much about that.  In order to do a table join, I need the school names to be exactly the same.  So what’s a GIS analyst to do?  This calls for a bit of field calculating.  I’d like to extract everything to the left of the colon and add that text to a new field.  As a first step, I’ll add a new text attribute to my walking zones layer; I’ll call it SchoolName:

Next, right-click on the new field name and choose the Field Calculator:

Normally, we use the Field Calculator to calculate or add values to fields but we can also use it to extract text or concatenate (link together) multiple attributes.  Here’s one way to remove the “0 – 500” bit.

First tick “Show Codeblock”

This allows us to write a small snippet of Visual Basic (VB) code to do the job.  Python users, note that you can tick the Python parser to use commands in that scripting language.  We’ve got two new boxes now: “Pre-Logic Script Code” and “SchoolName =”.  We can type our code in the Pre-Logic box and then actually run it on the SchoolName attribute in that box.  I’ve set it up this way:

Allow me to translate.  Take the value in the “Name” attribute field (from the WalkingZones layer) and split the text at the colon character.  Take what’s to the left of the colon and refer to it as “newName”.  The “newName(0)” in the “SchoolName =” box then runs the code for each record in the table (or only on selected records if there are any) and puts the extracted text in that field when I click OK.  After running it, here’s what I’ve got in my Walking Zones table:

My SchoolName attribute values now match my NAME values in ChchSecondarySchools and I can proceed to a table join:

Et voila!  Now I’ve got all the data I need in my secondary schools layer:

This is just a small taste of the types of things we can do with the Field Calculator.  More can be found here if one is inclined towards such geeky level GIS stuff (counseling is available).

C

• August 16, 2017


Previous Post

Next Post

Comments

  1. Sharon L. September 9, 2017 - 2:39 pm Reply

    Cool solution. You could also do it (in this case) without the pre-logic script code by just using the formula in SchoolName=
    Left([Name], (InStr([Name], “:”) – 2))
    This uses the string function Left(what, how many characters), with the How many chars bit done by using InStr to find the position of the “:” and then subtracting 2 to allow for the colon and its preceding space character. There won’t always be existing functions, of course. I can see Split being handy. And pre-logic for that matter.

Leave a Reply

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