FME & APIs: Enhancing your postcode data with geospatial elements
OK so you’ve got a list of postcodes and you want to visualise that in something like Tableau or ArcGIS, what do you do?
Tableau luckily has Postcode area mapping, but not postcode locations, so while you could split the postcode to get the area, this might not provide the level of granularity that you need (or you might not have the necessary fields you need to join on (to e.g. ESRI shapefiles). Realistically what you want for point locations is either the latitude and longitude coordinates or, for ArcGIS, the eastings and northings. So how do you get this from a simple postcode? The answer is APIs and luckily the folks over at https://postcodes.io have put one together that’s free and easy to use!
If you log on to their website you can enter in a postcode to test out what the response looks like
Now obviously there are plenty of other tools and/or scripting languages that could be used to perform this function but this blog will focus on how to perform the basic of this operation using the application FME, created by Safe Software. The version I’ll be using is 2019.0 so some bits may look a little outdated (I’ll be upgrading soon!)
Let’s start off by connecting to our data source – I have attached a list of postcodes below that you can use for this purpose
Add in a CSV writer and connect to the data.
You may need to specify in the parameters that the delimiter character is a comma
Next , add an HTTPCaller transformer and connect the output from the read into the input
Open up the transformer and set it up as follows, remembering to pass your ‘postcodes’ attribute in at the end of the URL where you will be calling the API
Now as is standard, the response you’ll be getting is in JSON format, so we’ll need to handle this in the workflow to bring it back into a nice tabular format. There are plenty of transformers that will do this, but I’m opting for the JSONFragmenter in this case
Set the JSON Attribute to be ‘_response_body’ and then you can leave most other fields as default, however you will need to specify what attributes you want to expose (e.g. which fields from the API you want to pull back into your flow). Listed below are all of the possible attributes (from the postcodes API anyway):
For the purpose of this demo, we will just be pulling through the latitude, longitude, eastings and northings field so simply change the ‘Flatten Query Result into Attributes’ drop-down menu to ‘Yes’ then enter in these values into the ‘Attribute to Expose’ field
Ok – let’s run the data through and see what happens
Success! But we can see that our 100 rows has now turned into 200. This is because the JSON response we get back has 2 index elements – a ‘status’ and a ‘result’. All we are concerned about here is the ‘result’ so we can add an AttributeFilter to sort this out
Select ‘json_index’ from the drop-down menu and type in ‘result’ as an Attribute Value
If we run our flow through this transformer we can now see that we have our filtered list
We have a few additional columns that probably won’t be needed in our final output, so we can use an AttributeRemover to get rid of these
Ok, so now we have all of our data and we can write it out to a format of our choosing. In this case, I’ll write it out to a .csv file again
Give the file a name and you have your dataset ready to visualise however you want