How to program a distance check facility between to UK postcodes in Microsoft Access
Posted: Saturday, May 30, 2009
by Michael Jillions
Mill House Data Solutions Ltd
Being able to calculate the distance between two postcodes is of an enormous benefit in many software applications. We at Mill House Data Solutions have used it to great effect many, many times. There are online companies who can offer automated access to their distance check software but not only will this cost, but will rely on internet access as well.
So how do we do it? The method uses the Haversine formula which assumes that the Earth is a sphere. OK we know that the Earth is not a perfect sphere, but when you are checking between two map co-ordinates it is more than accurate enough for these purposes.
OK so without going into the technicalities of how the formula works, we shall get straight on with the coding requirements for VBA.
You will need a table in your application containing all the UK postcodes and their X & Y axis co-ordinates. We would be happy to provide this.
The following VBA Code attached to the Calculate Distance click event is used in a simple form where the user enters the start and end postcodes and the distance is calculated.
txtPostCodeStart and txtPostCodeEnd are blank text boxes for entering your criteria. txtStartLat, txtEndLat, txtStartLong and txtEndLong are text boxes that are automatically populated through the after update event on the text boxes txtPostCodeStart and txtPostCodeEnd using simple RecordSet vba programming.
Private Sub caldistance_Click() On Error GoTo Err_caldistance_Click
If Me.TxtPostCodeStart = "" Then MsgBox ("Please enter a Start Post Code") Exit Sub End If
If Me.TxtPostCodeEnd = "" Then MsgBox ("Please enter an End Post Code") Exit Sub End If
Distance = (Sin((Me.TxtEndLat * 3.14159265358979) / 180)) * (Sin((Me.TxtStartLat * _ 3.14159265358979) / 180)) + (Cos((Me.TxtEndLat * 3.14159265358979) / 180)) * _ ((Cos((Me.TxtStartLat * 3.14159265358979) / 180))) * _ (Cos((Me.TxtStartLong - Me.TxtEndLong) * (3.14159265358979 / 180)))
Distance = 6371 * (Atn(-Distance / Sqr(-Distance * Distance + 1)) + 2 * Atn(1))
Me.TxTDistance = Distance
Exit_caldistance_Click: Exit Sub
Err_caldistance_Click: MsgBox Err.Description Resume Exit_caldistance_Click End Sub
Whilst we have demonstrated a simple form, the basic formula is there to be used in a host of different ways. We hope you will find this as useful as we have and if you need any assistance please don't hesitate contact us or visit our website!
--------
Michael Jillions is a Director for Mill House Data Solutions Ltd. MHDS are specialists in the design and construction of prefessional, custom software solutions. http://www.millhousedata.com
This Article has been viewed 513 times. (Not updated in real-time.)
No comments yet.We want your comments! If you can read this, you don't have javascript enabled, so you can't use this comment system. Please enable javascript.