Search
  • James Goodall

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


postcodes
.csv
Download CSV • 950B

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):

postcode

outcode

incode

quality

eastings

northings

country

nhs_ha

admin_county

admin_district

admin_ward

longitude

latitude

parliamentary_constituency

european_electoral_region

primary_care_trust

region

parish

lsoa

msoa

ced

ccg

nuts

codes.admin_district

codes.admin_county

codes.admin_ward

codes.parish

codes.ccg

codes.ccg_code

codes.nuts

outcode

eastings

northings

admin_county

admin_district

admin_ward

longitude

latitude

country

parish

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






26 views

©2020 by Data & Stuff. Proudly created with Wix.com