Step 2 - geocode by postcode
Click here to download the complete files for all steps in this tutorial.
Firstly download the zipped geocoding files to be used with ODL’s postcode geocoder component from our downloads page. Unzip the file to a directory on your computer.
Back in ODL Studio, in the tables list ensure the Customers table is selected and run the component wizard either by right-clicking on the table name and using the pop-up menu or clicking on the component wizard icon (the small wand) below the tables list. Select the geocode postcode option. If you already have a geocode postcode script in your current scripts directory, a dialog will appear asking whether to run the wizard or the pre-existing script; ensure you select the option Launch wizard “Geocode postcodes”
The adapter dialog should appear. As the geocode postcode component expects latitude and longitude columns it will have already automatically matched these to the columns you just added. This component also needs a postcode column to run. If the spreadsheet already had a column containing just postcodes, we could select this for the source and then run the script. Unfortunately though, the data instead has an Address column which contains the full address including postcode – e.g. 27 Urswick Road, Goresbrook, London, RM9 6ED. To solve this we are going to use a formula. Set the postcode column to be calculated by ticking the calculated checkbox and then enter the following formula into the formula box:
postcodeukunit(Address)
This formula will search the Address column for any string that matches the format of a UK unit postcode (i.e. full postcode) and return it if found. You should then select the geocoding database file. Under the label Postcode geocode database file press the button on the right to browse for the file and then navigate to the geocoding data files directory which you unzipped earlier. Geocoding files are named by their country code, as listed in the earlier table. As the data is for the UK you should select the file gb.gdf. The geocode postcodes script editor should now look like the following screenshot:
If you press play the geocoder component will run and a summary window will appear showing how many postcodes were successfully geocoded. For this tutorial’s Excel file, 96 out of the 100 customers should geocode. For these 96, the latitude and longitude values have been filled into the Customers table.
Tip - for countries with hierarchical postcodes (e.g. the UK has postcode areas, districts, sectors and units), if the postcode geocoder does not match to the most detailed postcode it can still match to a higher level – e.g. match to sector instead of unit. This will be less accurate. To see what level the postcode matched to or why the geocoder failed to match, set-up a column in the adapter to output the MatchInformation column.
