domestic energy assessors

Domestic Energy Assessors need to specify what post codes they can service - any that are out of range are not cost-effective to travel to.

How did you decide what post codes you can service? Intimate knowledge of the area? Live in London where post codes are shown on free mapping services? Spent £25 on a map with them on? If none of these, here’s something I stumbled across while looking for a cheap post code map.

All you need are a small CSV file from easypeasy.com, some Excel filters and a bit of Pythagoras!

In order to work out what post codes a DEA can service, the energy assessor needs to have a good post code map (cheapest is GBP25, but if you're on a border you may need 2: GBP50), or know the local post codes well (I know South Warwickshire fairly well but not further afield) or have some means of looking up how far they are.

I tried Multimap, but it was time-consuming and I missed some I didn't know. This is where the coordinate table came in.

While looking for a cheap post code map I discovered a table of post code coordinates which allowed me, by using Excel filters and Pythagoras' theorem to draw a circle enclosing all the post codes within, say, a 30km radius.

Someone has calculated x and y coordinates in metres, from a datum point off Cornwall, of the centre of every Outcode in the UK (excluding Channel Islands and the Isle of Man), and saved these in a CSV file.

All a DEA needs to do with this is open the table in Excel, filter the x and y coordinates to a certain distance either side of their own code, creating a square, use Pythagoras' formula on the x and y differences to create a straight line distance from code to code and then do a final filter on these distances to leave a collection of codes within the desired range.

Detailed instructions for those not quite up to speed with Excel or Pythagoras would greatly increase the size of this article: if you need them, see version 2 (coming soon).

Here they are in brief:

1. Go to http://www.easypeasy.com/guides/article.php?article=64 and download postcodes.zip.
2. Open it up and click on uk-postcodes.csv. It should open in Excel.
3. Type a heading in F1 such as distance or r for radius. Select Data - Filter - Autofilter.
4. Find your own post code. Note down the ‘x’ figure in column B and the ‘y’ in C. These are in metres.
5. Decide how far you want to travel, e.g. 30km. Add and subtract this from the x and y values you noted down. You should have 2 pairs of upper and lower bounds (e.g. 611,400 ± 30,000 = 641,400, 581,400; 166,000 ± 30,000 = 196,000, 136,000).
   
6. Custom filter x less than upper bound and greater than lower bound.
7. Repeat for y bounds to limit post codes to a square.
8. Go back to your own code, and in the F cell, paste  =SQRT((B550-B$550)^2+(C550-C$550)^2) replacing 550 with the row number you’re on.
9. Extend the formula to the top and bottom of the column and filter less than your chosen distance. This will limit post codes to a circle.
10. Save it with a new name.
11. The method is quite a crude first approximation as driving would not be in a straight line, but it rules out faraway codes.

Happy post coding!

Peter.