By Mike Lewis
In my article on calculating distances between UK postcodes, I posted a link to a site named EasyPeasy from where you can download a file containing the grid references of outward postcodes. Several HexCentral readers have recently contacted me to tell me that the link is now dead and the site no longer available.
I have now updated the article to include a new link. This one is to a site hosted by Dan Gibbs, which includes an up-to-date version of the outward postcode file. This version of the file is superior to the one formerly provided by EasyPeasy, as it also includes latitudes and longitudes, and the names of towns and counties (or their equivalents). The site warns that the town names are not completely accurate, but that won't matter for the distance calculations, as these only need the outward postcodes and the grid references.
My thanks to Dan Gibbs for providing this useful resource (free of charge), and to Adrian Bordicott for pointing it out to me.
Showing posts with label Databases and SQL. Show all posts
Showing posts with label Databases and SQL. Show all posts
June 16, 2016
July 25, 2014
Speeding up postcode queries
By John Hyde
In an article on Hex Central, Mike Lewis showed how to calculate the distance between any two British postcodes. Here’s a tip for speeding up the process.
The calculation that Mike demonstrated is a simple application of Pythagoras’ theorem. You start by getting the grid references (that is, the x, y co-ordinates) of the two postcodes. Next, add the sums of the squares of the x and y distances between them. Finally, take the square root of the value thus obtained. That final figure is the straight line distance between the two points.
My tip is simply to omit the calculation of the square root. So, instead of working with the actual distance, you work with the square of that distance.
As an example, let’s suppose you want to sort a series of postcode pairs into descending order of distance apart. You omit the calculation of the square root, which means that you will in fact be sorting by the square of the distance apart. The result will still be correct.
Similarly, if you want to find all postcodes within a given radius of a fixed point, you omit the square root calculation, and compare the distances with the square of the target radius. Again, this will give the correct result.
Since the calculation of the square root is likely to be the most time-consuming part of the process, leaving it out should speed things up considerably.
In an article on Hex Central, Mike Lewis showed how to calculate the distance between any two British postcodes. Here’s a tip for speeding up the process.
The calculation that Mike demonstrated is a simple application of Pythagoras’ theorem. You start by getting the grid references (that is, the x, y co-ordinates) of the two postcodes. Next, add the sums of the squares of the x and y distances between them. Finally, take the square root of the value thus obtained. That final figure is the straight line distance between the two points.
My tip is simply to omit the calculation of the square root. So, instead of working with the actual distance, you work with the square of that distance.
As an example, let’s suppose you want to sort a series of postcode pairs into descending order of distance apart. You omit the calculation of the square root, which means that you will in fact be sorting by the square of the distance apart. The result will still be correct.
Similarly, if you want to find all postcodes within a given radius of a fixed point, you omit the square root calculation, and compare the distances with the square of the target radius. Again, this will give the correct result.
Since the calculation of the square root is likely to be the most time-consuming part of the process, leaving it out should speed things up considerably.
Subscribe to:
Posts (Atom)