UK Towns List

Articles

Calculating the distance between two locations in SQL

Introduction

For some websites, it is a necessity to calculate the distance between certain locations. Take for example, a dating website which shows potential matches to a user within a 15 mile radius or a taxi business which calculates taxi fares based on distance between two locations. In both situations, this can easily be achieved with a simple SQL query and we're going to show you how.

When calculating the distance between two points, it is important that we're working with latitude and longitude. If you only have a town name or postcode to work with, the latitude and longitude values can be retrieved with the UK Towns Database.

Getting the latitude and longitude from a town name or postcode

If you only have a town name, you can retrieve the latitude and longitude with the following command:

select latitude, longitude from UK_Towns where Place = 'Enter the town name here'

Similarly, you can use the postcode:

select latitude, longitude from UK_Towns where Postcode = 'Enter the beginning of the postcode'

Calculate distance between two cities

Now that we have the latitude and longitude values, we can go about calculating the distance between two points. In this example, we're going to use the two cities 'London' and 'Manchester'. Using the methods described above, we can work out that the latitude and longitude values for London are 51.51713 and -0.10619 respectively.

SELECT (((acos(sin((51.51713*pi()/180)) * sin((`Latitude`*pi()/180))+cos((51.51713*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((-0.10619 - `Longitude`)* pi()/180))))*180/pi())*60*1.1515 ) as distance FROM `UK_Towns` WHERE Place = 'Manchester'

This calculation retreives the distance between the two locations in miles. The results from this calculation is 163.01 miles. To convert the distance to kilometers, simply multiply the distance by 1.609344

Select all locations within a defined radius

The following query identifies all locations within a 10 mile radius from a specific point. We'll use London as an example again.

SELECT *,(((acos(sin((51.51713*pi()/180)) * sin((`Latitude`*pi()/180))+cos((51.51713*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((-0.10619 - `Longitude`)* pi()/180))))*180/pi())*60*1.1515 ) as distance FROM `UK_Towns` HAVING distance <= 10

The result shows 399 places near London. A similar query can be used in the example of a dating website to identify every user within a specified range.