Ok - lets play a game called “imagine” for a bit. Lets imagine you have a database called Postcodes with a table called tblPostcodes which has a list (a very big one) of all the postcodes in the UK with their corresponding eastings and northings. Now you can do some fun stuff like work out how far the addresses in your database are from a specified postcode…
Lets setup some variables.
declare @e int, @n int, @pc varchar(10)
set @pc = 'SW1 1AA'
As a quick point of trivia, all postcodes that end in 1AA are in fact the postcodes for Royal Mail sorting offices - bet you didn’t know that!
select @e = avg(easting)
from postcode.dbo.postcodes p
where p.area + p.district + ' ' + p.sector + p.unit = @pc
select @n = avg(northing)
from postcode.dbo.postcodes p
where p.area + p.district + ' ' + p.sector + p.unit = @pc
Now we have the approximate easting and northing for that postcode. Note I’m doing an average on the returned results as you may well get more than one entry for that postcode.
select company_name,
convert(int, sqrt(square(@e-easting) + square(@n-northing))) 'dist'
from tblOfCompanysThatsInYourDatabase
Finally a quick application of the Pythagorean theorem gets the company names and how far they are from the selected postcode. I’m making a big assumption that the eastings and northings are something stored in your companies table - if not - just join on the postcodes table.
No need to worry about negatives in the subtract parts of the equation as square’ing a negative number returns a positive number. Results are returned in meters.