Map Room Home page  
written by Nigel James
 
Contents
 
   
MAPINFO How to...  
No. 20: Mapping postcodes using OS Codepoint Open data  
   
(References to tables, folders etc., are not applicable outside the Map Room, so if you are located elsewhere, you should substitute your own)  
   

This explains how to use MapInfo to map locational (point) data by postcode. The Ordnance Survey Codepoint Open data used here can be downloaded free from the OS OpenData website. Codepoint Open has the OS coordinates for every postcode unit in Britain and can be used to make your own postcoded data mappable.

If you are mapping data by postcode areas, districts etc., see MapInfo Howto no. 3

 

NOTE: Some postcodes in Codepoint Open have zero eastings and northings. This is because some delivery points (which the postcode represents) do not have a surveyed position. Also, some coordinates are outside the geographic area of the postcode, so they will not necessarily map in the correct place. This why you may find when geocoding that a postcode cannot be mapped, (zero coordinates) even though it is correct.

Multiple postcodes in blocks of flats share the same coordinates.

 
   
MapInfo users in the Bodleian can use use Codepoint Open data which has been downloaded and processed to make this procedure easier. This is explained in Part One of this guide. Users outside the Bodleian should follow the steps in Part Two which explains how to download the data and use it in MapInfo to make your own postcoded data mappable  

 
   
Part One - mapping postcode data in the Bodleian  
   

1. To map postcoded data, you need an Excel or text file containing your postcodes and any associated data. The table should have a single row of column headers and postcodes should either have a single space between the two parts of the postcode, e.g: OX1 3BG or MK32 5ZT, or the postcode as a single string, e.g: OX13BG or MK325ZT. There should be no other rows (blank or otherwise) apart from the header row and the rows of data in the table. If necessary clean up the table in Excel before opening it in MapInfo.

 
   

2. Open your Excel or Text file in MapInfo. (you will need to change the Files of type to either Excel or Delimited Ascii [*.txt] as appropriate to see your file (text files cannot have a .dat extension as this conflicts with a type of MapInfo file, so rename it to .txt first).

When you open an Excel file you will see this dialog:

Click the Named Range list dropdown button and choose Other...

Click OK.

Then in the Other Range dialog, change A1 to A2, as this is the first data cell (row 1 contains headers), as below:

Click OK.

Now click the Use Row Above Selected Range for Column Titles checkbox to tell MapInfo there is a header row:

Click OK.

You will now see the Set Field Properties dialog, which shows how many fields (columns) there will be, their names and types:

In this case there are just two columns - postcode and data. You don't normally need to change anything here, but you can rename fields in you wish to. Remember that numeric data must either be integer (no decimal places) or float.

Click OK to display the table in a Browser.

 
   
3. Now open the current postcode table - it is called postcodes2010.tab and is in the OS Opendata folder on the Maproom server drive (Z). You do NOT need to display the whole map - it is over 1.7 million dots, so just minimise it out of the way.  
   

4. Before proceeding to geocode your table, make a copy and use the copy to work with. The reason for this is that although MapInfo can read Excel and text files, it cannot write to them, so if you discover any errors in your table (wrongly typed postcodes are common, such as mixing up the letter O with the digit 0) you will not be able to correct them.

To make a copy, open the File menu and click Save Copy As... Choose your data table and save a copy with a new filename (you cannot simply replace the Excel version).

When you have done this, close the Excel version by opening the File menu and clicking Close table... (simply closing the window does NOT close the file), then select the original Excel version and close it. This step is not strictly necessary, but it avoids confusion between the two versions of your table.

 
   
5. Now open the new copy of the table, which will appear in a Browser window.  
   

6. You can now geocode your data. Open the Table menu and Click Geocode... (not Geocode using server...). You will see the Geocode dialog:

The various tables and options should now be selected as above, noting the following:

Geocode Table is your data table

Using Column should be the column containing the postcodes

Leave Boundary column as none

Search Table should the postcodes2010 table

for Objects in Column should be Postcode (if your postcodes do not have a space in them, choose Postcode2

The Optional boxes should both be left as none

Set Mode to Automatic (for automatic coding)

Choose a symbol by clicking the Symbol button. Make it fairly small (say 6pt) and bright coloured.

Click OK.

 
   

7. Your table will now be geocoded. This process creates a point for each postcode in your table, which can then be used as a map layer. When it has finished you will see a message like this:

This shows the geocoding was successful and point objects have been created for all postcodes

Click OK.

If there were any problems, you will see a message like this:

Note that one postcode was not geocoded. This will be either because the postcode does not exist in the postcode2010 table, or it was mis-typed in your table.

To check the postcodes which did not geocode, you can run a Query to find them. To do this, open the Query menu and click Select...

In the Select dialog, choose your table and type not obj in the that Satisfy box (this will find any record for which a point object has not been created):

Check that the Browse Results checkbox is ticked and click OK.

You will see now a browser window containing the postcodes which were not geocoded. Correct any typos, double-spaces etc., and save the table.

Run the geocoding process again to geocode the corrected postcodes. The result will be something like this:

In this case the corrected postcode has now been geocoded and there were no other errors.

If you still get postcodes which are not geocoded, it is because there is no matching postcode in the postcode2010 table. In this case you can either ignore them and not map them, or run the geocoding process interactively to see if there is a close match (but remember that postcodes similar to any in your table may not be geographically adjoining).

To geocode interactively, open the Table menu and click Geocode... again. In the Geocode dialog, select the correct tables as before, and change the Mode to Interactive. Click OK and the process will stop when an unmatched code is found. You will see close matches (but remember they may not be close geographic matches) and you can choose one if you wish, or ignore it and go on to the next ungeocoded postcode.

 
   

8. Now your table has been geocoded, you can display it as a map by opening the Window menu and clicking New Map Window... Select your table from the list and click OK. To view the whole of your data, right-click with the mouse and select View Entire Layer, then select all layers. Click OK to view the postcode locations.

If you already have a mapper window open and you wish to add your postcodes as a new layer, open the Layer Control and click the Add Layer... button. Choose your table and click OK to add the layer. To view the whole of your data, right-click with the mouse and select View Entire Layer, but this time select your postcode table to view, not all layers. Click OK.

 
   
9. You can label the points with any of the data values in your table by opening the Layer Control and clicking the AutoLabel checkbox next to your table. Click the Label button to select font style and size etc. You may need to click the Allow Overlapping Text checkbox in the Label dialog if the points are close together, otherwise not all labels will appear. You can then alter the label positions as required.  
   

 
   
Part Two - mapping postcode data using Codepoint Open data downloaded from OS Opendata  
   
1. You can download Codepoint Open (a free version of OS Codepoint) from the OS Opendata website. This dataset contains the OS easting and northing for every current postcode which can be added to your own postcode data to make it into a mappable table. The steps are different to Part One as you are not geocoding, but creating points in this process.  
   
1. Download the current Codepoint Open data from the OS Opendata website. You can request it on a DVD if you wish but it's only about 20MB zipped so should be quick to download.  
   

2. The data consists of a separate text file for each postcode area (AB, AL, B, etc) in comma-separated format (*.csv). This is a simple format which can be opened in MapInfo, Excel, Notepad, etc.

This does mean that if you have data for more than one area, you will either have to repeat the MapInfo process for each postcode area, or combine the separate files into a single file first. remember that older versions of Excel are limited to 64,000 rows, so you may not be able to copy and paste all the postcode data into a single table.

 
   

3. Before you use the Codepoint Open data in MapInfo, you need to format the postcodes to match yours. A normal postcode has a single space between the two parts (e.g: B6 5EL, OX1 3BG, or AB24 7RS etc), but this means the total number of characters can be 6, 7, or 8. The codes in Codepoint Open are all 7 characters, so they may have two, one or no spaces.

Using this data with postcodes which are all single-space (or no space) will result in a number of failed matches, so the Codepoint Open data needs to be modified first. It is best to do this in Excel before opening the data in MapInfo, as imported Excel tables cannot be edited and you will have to make copies of the tables first.

 
   

4. To make all the postcodes in Codepoint Open single-spaced, open a Codepoint Open file in Excel (select text files as the type to display *.csv files). The postcodes will normally be in column A with the data starting in row 1 (there are no header rows).

Insert a new column at the start of the table (this will now be column A and the postcodes in column B).

Click in cell A1, then enter the following formula (copy and paste to avoid errors) into the formula box:

=TRIM(LEFT(B1,LEN(B1)-3))&" "&RIGHT(B1,3)

Click the tick or press Enter. The postcode will appear in cell A1 with a single space. Fill Down the column to enter the corrected postcodes and save the table in the same *.csv format or as an Excel table - either will be OK.

 
   

5. When you have formatted all the areas you need and saved the file, close Excel, because you can't have a file open in Excel and MapInfo at the same time.

Open MapInfo if it is not already open, then open the Opendata file(s) by opening the File menu and clicking Open...

Go to the folder where the file(s) were saved and change File of type to either Excel or Comma delimited CSV as appropriate.

When you open the file(s), the procedure depends on whether it is in Excel or CSV format:

When you open an Excel file, choose Entire worksheet for Named Range and DO NOT click the Use Row above selected range for column titles (there aren't any):

Click OK.

The table structure will be displayed. Each column will be headed A, B, C etc:

When you open a CSV file, you will see the Comma Delimited CSV information dialog:

- again do not click Use First Line for Column Titles - just click OK.

(If you have created column headers, then click the Use First Line for Column Titles checkbox). The table will appear but this time the columns will be headed _COL1, _COL2, etc.

If you have saved the table in tab-delimited text format, change the File type to Delimited ASCII [*.txt] and open in the same way.

 
   

6. Open your postcode table. If it is in Excel format, you will see the Excel information dialog. Choose Other... from the Named Range list:

Click OK.

If you have a header row (only one row is allowed - no multiple header rows or blank rows), change the A1 in the Named Range to A2 (this is the first data cell):

Click OK and then click the Use row above selected range for column titles checkbox:

(If you do not have a header row (but it makes working with your table a lot easier if it has), leave the cell range as starting with A1 and don't click the Use row above selected range for column titles checkbox).

You will now see the Set Field Properties dialog, which shows how many fields (columns) there will be, their names and type:

In this case there are just two columns - postcode and data. You don't normally need to change anything here, but you can rename fields in you wish to. Remember that numeric data must either be integer (no decimal places) or float.

Click OK to display the table in a Browser.

(If your table is in CSV format , you will see the Comma Delimited CSV information dialog instead. Click Use first line for column titles if appropriate and click OK. The table will appear but this time the columns will be headed _COL1, _COL2, etc).

 
   

7. You must now make a working copy of your table, as Excel files cannot be edited in MapInfo. Open the File menu and click Save Copy As... Choose your postcode table and save it with a different filename (add 'mappable' or 'with points' to the file name, for example).

Now close the original table by clicking File>Close table, select your original file and close it. Note that simply closing the window does not close the file (unlike in Excel for example).

Open the copy you made (File > Open table...and choose MapInfo tables [*.tab] as the File type), which will appear in a browser window.

 
While it is not strictly necessary to close the original Excel or text file first, it does avoid confusion and you will not end up trying to edit or link to the uneditable original version by mistake.  
   
The next step is to add two new columns to your table to contain the eastings and northings for each postcode, which will enable you to create points and map them.  
8. Open the Table menu and click Maintenance..., then Table Structure... Select your table from the list and click OK. You will see the current columns in your table listed and their type. Click the Add Field button. Type Easting for the field name. Change the type to Integer (do NOT click OK yet!). Click Add Field again and type Northing for the Field name and again change the type to Integer. Do not click the Table is mappable checkbox (the table will be made mappable later) Now you can click OK to confirm the changes.  
   
If you are unable to modify your table it is because you are looking at your original Excel/text file, not the MapInfo copy - this is why it is better to close the original file first!  
The Browser window will disappear - this is not a problem (you haven't lost the file), just open the Window menu and click New Browser, then select your table from the list. The easting and northing columns will show zeros at this stage as they are integer fields. If they do not, you have probably selected character as the type by mistake, so go to Table>Maintenance>Table Structure again and change them.  
   

9. The eastings and northings are added separately. Open the Table menu and click Update Column. In the Update column dialog, select your postcode table as Table to Update, and easting as the Column to update. In the Get Value from Table list choose the Codepoint Open file and choose the column in the Opendata file which contains the eastings (in this example it is _COL_3):

Don't click OK yet.

 

10. Click the Join... button. If you see a message "Cannot automatically compute the join", just click OK.

In the Specify Join dialog, select the postcode column from your table in the where box and in the matches list choose the postcode column in the Codepoint Open table:

Click OK.

 
   
11. Back in Update Column, Calculate should be Value, and Of: should be eastings. Uncheck Browse Results as you already have a browser open and click OK. The eastings will appear in your table.  
   

12. Repeat steps 9 - 11, this time selecting northings as the column to update. Join the tables again and select northings from the Of: list. Click OK to add the northings.

Save your table.

 
   
13. Now you have coordinates for your table, you can create points to map the postcodes. Open the Table menu and click Create Points.  
   

14. In the Create Points dialog, select your table from the Create Points for Table list. Click the Using Symbol button and choose a symbol - a small (6pt) filled circle in a bright colour is best for the initial mapping (you can always change it later). The Get X Coordinates from Column should be easting, and the Y coordinates the northing column (if you see a message saying Use Same column for X and Y fields, just click OK then change the Y coordinates field to northing. Leave the X and Y multipliers as 1.

DO NOT click OK yet!:

 
   

15. Click the projection button and choose British Coordinate Systems from the Category list, and British National grid from the Category Members list. :

 
   

16. In the Create Points dialog, click OK.

Nothing appears to happen, but the table is now mappable and can be displayed in a mapper window.

If you wish to create a new map using your data, open the Window menu and click New Map Window. Choose your table from the list of map tables available and click OK. Right-click in the mapper window and select View Entire Layer from the pop-up menu, then click OK. This will zoom the map out to show your entire data coverage.

If you have some zero coordinates in your table you will see these as single point in the lower left corner of the map (they are just off the Isles of Scilly), so to zoom in to the valid postcode points you will need to draw a box around them with the zoom in tool..

If you are adding your data to an existing map, open the Layer Control (right-click with the mouse and select it from the menu), then click the Add Layer button. Select your postcode table and click OK.

To view the whole of your data, right-click with the mouse and select View Entire Layer from the pop-up menu but this time select your postcode table to view, not all layers. Click OK and re-adjust the zoom if necessary using the zoom tools.

 
   
17. You can label the points with any of the data values in your table by opening the Layer Control and clicking the AutoLabel checkbox next to your table. Click the Label button to open the Label Options dialog where you can select font style, size etc. You may need to click the Allow Overlapping Text checkbox in the Label Options dialog if the points are close together, otherwise not all labels will appear. You can then alter the label positions as required.  
   
This is a specific guide to this topic, for more general information, see: MapInfo - an easy guide for new users  
Nigel James
Bodleian Library 2010
 
Bodleian Library Map Room