Calculating the distance between a set of postcodes and ranking them in order.


There are sometimes instances where you have a set of locations and you need to rank them in order. A perfect example of this is allowing customers to find out the closest branch of a shop. The example here involves a set of ice cream shops and showing which one is closest to the postcode we specify.


You can see the code here in action at: As always any patches to the code are very welcome

Getting started with the example

The first step is to download a copy of the source code and sql script which you can do using git:

or downloading it all in a zip file

Create a new database and import the table with the UK postcodes and their corresponding lat and lon (UK postcode database was provided by

then exit back into your shell, cd to the source code directory and run:

That’s the database all set up so now take a look at the example (search.php).

Essentially what this does is takes a postcode (YO31 8UB), looks up the corresponding lat and lon coordinates and runs a SQL query using the haversine formula and returns the ice cream shops in order of distance. So essentially the customer knows where the nearest place to get an ice cream is. Once you have entered your database credentials to search.php you should be able to run:

which should return (distance in miles):

There you have it! We can now see that out of all the ice cream shops in the database (as the crow flies) ‘Archers Ice Cream’ is the nearest. Hopefully this example will help you gain an idea of how you can implement this functionality yourself into the projects you are working on.

Need help or not exactly what you are looking for?

Vixre is a consulting and software development company based in Northallerton, England. We’re happy to help you with implementing the distance calculation, data preparation, processing, server setup and such. Just let us know what you would like to achieve and we will see if we can be of service.

Possible improvements

Investigating the possibility of integration with the API. This would likely involve porting the existing code (worth chatting with the team before doing this).

Currently the class only provides an ‘as the crow flies‘ distance calculation. Using a path routing tool (e.g. OSRM) would be very useful and should allow for distance estimates that take into account roads and terrain.

Getting started with CharitiesNeed

This is a step by step guide to adding your charity to CharitiesNeed. It’s a nice and simple process and shouldn’t take more than a few minutes.


1. Go to and click on “add charity” at the top right of the screen.


2. Fill in the details you see on  the registration form and then click ‘Register’.


3. You should receive an email shortly with a link to activate your account. Once you receive and click on the link you will be able to log in.

Your charity details and needs

1.  After logging in, you will see your charity listed and the first step is to click on ‘location’ and fill out the details there. There is also a section at the bottom which allows you to give a description about your charity or any thing else (e.g. opening times). Once you are happy, click save.


2. The next step is to add the type of items your charity needs. You can also set a ‘need rating’ next to the item categories. So for instance if you really needed books, you could select ‘5’ indicating to donors that you really need books.


The section at the bottom allows you to write any further details about your needs.  Once you are happy, click save.

4. Adding a charity logo or image relating to your charity can be done by clicking on ‘logo’. Try to pick an image that’s not too big. Again, click save.


5. Done! That’s all that is required, your charity is now on CharitiesNeed. Try putting in a postcode near to your charity on the search page. You should find your charity in the results.


The profile donors will see with all the information you have entered.


Charities with more than one donation point and/or managing multiple charities.

You can add additional charities or donation points in the ‘Donation points’ section.

This is particularly useful if you manage multiple different charities and/or your charity has multiple donation points (e.g. clothes banks, shops, drop off points).

This is a quick run through so If you have any questions or suggestions, please feel free to email [email protected] or comment. Thanks for reading and I hope CharitiesNeed can help your charity receive more donations, raise awareness of your charity and help donors find out where they can donate their unwanted items.


Automated wordpress installation in under 30 seconds


Recently a client expressed his interest in teaching people how to use wordpress. The goal was to give each student in the class their own site so they could work on it throughout the day to make the lesson practical and give the students some hands on experience. It’s also great because the student has something they can take home with them and can continue to work on. I got through setting up 3 before I realized it had to be automated.


Why not just have each student sign up for a account? is great, someone just has to sign up and poof, they have a site. The issue is flexibility and one of the major ones is lack of plugins. Currently has a list of plugins and as far as I’m aware you are not able to install any plugin you wish – only the ones they have on their set list. Considering the abundance of great plugins and that most users can benefit from them, it’s a major drawback.

Technical Overview

It’s a bash script that starts at the beginning by creating a user on the server, installs the database, creates relevant directories, downloads wordpress and assigns permissions and all the way to using the cloudflare DNS api to create the records and creating a welcome message. The idea was to just type one command and a site is ready for use like so:

./ test123

which would result in a brand new wordpress site at:

It does just that and has saved me quite a lot of time. If you’re inclined to have a look at the code, please feel free to do so.

If you have any questions or would like something changing or enhancing, we are available.

Server details: Debian stable, Apache2, MySQL/MariaDB, PHP, cURL, wget

Overall message

I think the key message is try to take time out and be aware of how you are working and whether it can be improved. If you are familiar with the process and find it is taking up more time than you would like, exploring how to automate the process is definitely something to look in to and something we can help with.

CharitiesNeed – A brief overview for Donors

CharitiesNeed is one of our recent projects and the main goal behind it is to make donating items to charities easier and quicker. In essence, it’s an app where you (the donor) specify your post code and the category (e.g. Clothes, Bric a brac, Food, Books) of the item or items you wish to donate and CharitiesNeed shows the charities listed in the database ordered by distance. With this, the plan is that you can pick up any item and by using CharitiesNeed, find the nearest Charity which would be happy to take the item.

charitiesNeed landing

For more information sign up to the mailing list. We’ll also notify you as soon as it’s released. Charities interested in registering can email [email protected] for more info.

MySQL UK post code table with corresponding longitude and latitude

(Edit March 2016) Please note the freemaptools site now maintains a MySQL version so it would probably be best to use theirs instead.

One of the projects I have been working on recently involved the calculation of the distance between two postcodes. Google provides a decent API and retrieving the distance between two post codes with map routing is very simple. For example to calculate the distance between Fountains Abbey and Northallerton high street using their postcodes would only require:

The downside to this is that there are request limits imposed on this API and you can only make a certain amount before you need to hand over some cash to Google. Due to the nature of the project, I realized these limits would quickly be met and as a result found the Google API to be unsuitable.
An alternative would be to find a database with all the UK post codes and their longitude and latitude coordinates. You could then easily lookup the coordinates of a post code and pass them to a routing engine (e.g. Open Source Routing Machine) to calculate distances by road or potentially just a simple ‘as the crow flies’ distance calculation using trigonometry.Open Source Routing Machine Logo

It didn’t take long to find the fantastic which has a csv containing all UK postcodes and their coordinates. Unfortunately they only had an MSSQL version so I whipped up a MySQL version which worked nicely and is just below for your perusal and use. The same goes for the output the script generates:

Output of the script: (35M) Last updated 01/07/2014

The PHP script on github


PHP code for taking the csv found at and importing it into a mysql database. You can use the script in two ways:

  1. Fill in the database details at the start of the file and run the queries as they are built.

    php import.php postcodes.csv

  2. Specify ‘echo’ as the 2nd argument and output the sql to a file.

    php import.php postcodes.csv echo > mysql_postcodes.sql