Survey123 Tricks of the Trade: Working with UK postcodes
@MatthewGuilliatt recently asked how to get an XY location from a UK postcode in a Survey123 smart form. The basic idea is to present users with a chance to enter a UK postcode and automatically calculate a geopoint question. I thought at first that this should be pretty straight forward, but then I learned that there are 1.7 million different postcode units in the UK. This turned out to be not just a straight forward thing, but also an interesting one! 😀
In truth, working with such a big list of postcodes presents its own challenges. This article describes a few considerations and techniques that could be useful for this particular use case, but in many others as well. Lets use UK postcodes as an excuse to describe how you can perform lookup searches against huge tables, calculate geopoints and other things.
About UK postcodes
Postal codes in the United Kingdom are known as postcodes.The structure of a postcode is two alphanumeric codes that show, first, the Post Town and, second, a small group of addresses in that post town. For example, SE1 9BG is a postcode corresponding to London Bridge, in Southwark, London.
The first two characters (SE, in our example) specify the postcode area; there are 124 post code areas. The numeric value before the space (1), defines the postcode district; there are roughly 3,000 of them. After the space, the number at the beginning (9) sets the postcode sector; about 11,000 thousand in total. The alphabetical code that follows (BG) describes the postcode unit. There are roughly 1.7 million postcode units in the UK.
The Address Management Unit of the Royal Mail maintains the official database of UK postal addresses and postcodes. Access to this database is licensed for a fee. The UK Ordnance Survey provides access to the postcodes (without the addresses) through Point-Code.
For the purpose of this article, I downloaded postcodes from FreeMapTools.com . I found the data in freemaptools.com good enough: The .csv file I downloaded was last updated on March 19, 2021 and had more than 1.7 million records, each with its own latitude and longitude. More than 60Mb worth of data. Microsoft Excel could not open the file entirely, but here is a screenshot of its contents:
The postcode database can also be accessed through web services, such as https://postcodes.io/ where you can retrieve information about postcodes through a simple REST API.
Calculating a geopoint: Select a postcode, center the map
At a high level, what we want to provide to the end user is a way to enter a postcode in the smart form, and automatically calculate the location of that postcode. The following screenshot shows a basic starting point: The user defines a postcode and we center the map. Simple!
To do the above, we first need to somehow retrieve the latitude and longitude of the entered postcode, and then construct the geometry so we can set the value of the geopoint question to center the map. How you perform the lookup search against that 1.7 million record table is a key decision. Lets explore a couple of methods, and describe when you should use one, or the other.
Lookup search against a local file
One approach is to do the lookup search against a local .csv file. This file is associated with the survey and always goes along with it. If you need the lookup search to work even if disconnected from the network, this is the best method. It is perfect for the Survey123 field app.
To bundle your Survey123 smart form with a local .csv file, you need to copy it into the survey's media folder. The following image shows what the XLSForm design would look like:
The first question is used to let the user enter a valid postcode. The latitude an longitude questions use the pulldata() function to retrieve the coordinates of the postcode by searching the ukpostcodes csv file. This is the file I downloaded from FreeMapTools.com and then copied into the media folder of my survey. Finally, the geopoint is constructed using the latitude and longitude retrieved.
The Work with external content help topic describes in detail the syntax of the pulldata() function. Here, I want to highlight some extra things you should keep in mind:
- When authoring your survey in Connect, you must copy the .csv file in the media folder of your survey.
- As you reference your .csv file in the first parameter of the pulldata() function, you want to make sure you type the name of the file exactly: Some operating systems like iOS are case sensitive. You also do not want to include the .csv extension at the end of the file name. It is assumed that you are working with a .csv file.
- The pulldata() function is best kept alone in the calculation column. Do not be tempted to combine multiple pulldata() calls within the same calculation. It will work in Survey123 Connect, but the Survey123 web app will not be as permissive.
Despite the size of the table, you will notice great performance in the Survey123 field app. The lookup operation will be instantaneous. This is the case because Survey123 takes the .csv file and creates a SQLite database with indexed tables. This process happens when the smart form is opened for the first time in your device. Right after opening your form, if the indexes do not exist, Survey123 will display a message with the message 'Optimizing survey'. That's when the indexes are built. In my iPhone, it took about 5 seconds for the 1.7 million records to process. Once the indexes are built, there is no penalty when the form opens.
The use of the pulldata() function as shown here is quite handy, not only to calculate geopoints, but for any occasion when you want to bring into your form information from a table. For example, say you have a collection of assets and you want to prepopulate your form when the asset id is introduced. You can use a .csv file with as many columns as you need to bring all that information.
The pulldata() function has been available for this purpose in Survey123 for many years, but it has never been able to perform as well as it does today with version 3.12. Having said this, be conscious of the size of files you associate with your survey. In our particular case with the UK postcodes, the table is over 60Mb in size. When downloading the form into the device, you will note that the survey takes a while to download.
Handling changes in the postcode file: Linked content
As described above, UK postcodes can change every day. So what is the best strategy to keep field users with the latest postcode files? Prior to version 3.12, the only chance you had was to update the .csv file in the media folder, and then republish the survey. Philip Wilson describes a clever technique to update the media folder without having to republish.
Starting with 3.12, you can upload your .csv file into ArcGIS as a new item, then link your .csv item to your survey and let the field app do the rest: When the .csv item is updated, the app will automatically download the latest version.
In my experience, the safest way to link your .csv file is as follows:
- In Survey123 Connect, open the media folder in the survey directory. Copy the path to this folder.
- Login to ArcGIS Online (or Enterprise) and use the Add an item from your computer option: Browse to the media folder of your survey and upload the .csv file.
- Share the .csv item accordingly
- Back in Survey123 Connect, go to the Linked Content tab to link your survey with the .csv item you just uploaded.
Once the survey is linked to the .csv file, the app checks for updates in the file when the form details page is loaded in the Survey123 field app.
Lookup search against a web service
Linked or not, using a .csv file in the Survey123 web app is only a good idea when the file is small. In an online smart form, the pulldata() function can only perform a search in your file when it has been downloaded into the browser. Our UK postcodes file is over 60Mb. Too much wait and too much processing for a web browser!
As an alternative, we can use a custom JavaScript function to perform a live query against a web service to retrieve the latitude and longitude values. Since we are working with an online form, it is assumed that having connectivity is not an issue. Here is what the XLSForm could look like:
In this case, we are using two variations of the pulldata() function. We are no longer using it to search a value in a .csv file. Instead, we use it to run a JavaScript function first, and then to parse the contents of a JSON object returned from the JavaScript function.
If you are not familiar with the use of custom JavaScript functions in Survey123 smart forms, check this Tricks of the Trade blog. In our scenario, my custom JavaScript function is making a request to the Lookup a postcode operation of https://postcodes.io/ This operation returns a JSON object with quite a bit of information about the postcode, which is later parsed to extract the latitude and longitude.
Here is the JavaScript function:
function getpostcode(postcode){
if (postcode===""){
return "";
}
var xmlhttp = new XMLHttpRequest();
var url = "https://api.postcodes.io/postcodes/" + postcode;
xmlhttp.open("GET",url,false);
xmlhttp.send();
if (xmlhttp.status!==200){
return xmlhttp.status;
} else {
var responseJSON=JSON.parse(xmlhttp.responseText)
return JSON.stringify(responseJSON);
}
}
There are some important considerations to keep in mind:
- Always work with https when requesting resources from a web service.
- The JSON output from https://postcodes.io/ -and from other web services as well- can be large. When setting up your XLSForm, it is important to set the bind::esri:fieldLength value of the postcodejson question to a large number like 999999. This will avoid the output of the web service to be truncated.
- Since you unlikely want to store the output of the web service in the feature layer as an attribute of you feature, set the bind::esri:fieldType to null on this question.
- You will also want to set its appearance to hidden so the JSON output is not shown in the form to the user.
The use of a custom JavaScript function here helps us keep the form lightweight. We no longer need to bundle it, or link it, to a .csv file. Another great advantage is that we do not have to refresh any tables. As long as the web service is up to date, our smart form will get the latest information. Here you can see the web and field apps side by side running our UK postcode locator.
An important limitation right now when using custom JavaScript functions is that they do not work in public surveys. We are working towards addressing this issue at some point in the near future.
Do you want to try on your own?
I encourage you to try this on your own. Just make sure you have version 3.12 installed. You will not be able to do this with previous versions of the software. I attached the attached file you will find a couple of XLSForm designs and also the custom JavaScript function to do the online queries. The .csv file with the UK postcodes can be downloaded from here: https://www.freemaptools.com/download-uk-postcode-lat-lng.htm