Page 1 of 1

Calculating Distance Between Two Post Codes - As The Crow Flies

Posted: Tue Oct 25, 2016 10:53 am
by robinwilson16
I needed to work out how many miles away students are for support payments and thought I would share the code in case others find it useful.
Please note that this is distance as the crow flies and not taking road layouts into account

Step 1: Download Post Code Latitude and Longitude Source Data
Download the source data from this link and import into a table in SQL Server:
https://www.freemaptools.com/download-u ... at-lng.htm
This is regularly updated being last updated Oct 2016 (at time of posting this)

Step 2: Test SQL and Compare to Google Maps
Amend this SQL to calculate the number of miles between two post codes using the source data from step 1:

Code: Select all

SELECT
		Distance = 
			ROUND( 3958.761 /*Miles*/ * ACOS( COS( PI( ) /2 - RADIANS( 90 - PCT.Latitude) )* COS( PI( ) /2 - 
				RADIANS( 90 - PCF.Latitude ) ) * COS( RADIANS(PCT.Longitude) - 
				RADIANS( PCF.Longitude ) ) + SIN( PI( ) /2 - 
				RADIANS( 90- PCT.Latitude) ) * 
				SIN( PI( ) /2 - 
				RADIANS( 90 - PCF.Latitude ) ) ), 2 )
	FROM PostCodeLatLng PCF
	INNER JOIN PostCodeLatLng PCT
		ON PCT.PostCode = 'GU1 1EZ'
	WHERE
		PCF.PostCode = 'NE61 1TB'
Compare with Google Maps (note that Google Maps will be higher as this does take road layouts into account whereas this code is point to point (as the crow flies)

Step 3: Make into a Table Valued Function (Optional):
Create a Table Valued Function that takes the post code from, post code to and measurement unit required:

Code: Select all

USE [GC]
GO
/****** Object:  UserDefinedFunction [dbo].[TVF_PostCodeDistance]    Script Date: 25/10/2016 10:39:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[TVF_PostCodeDistance] (
	@PostCodeFrom VARCHAR(9),
	@PostCodeTo VARCHAR(9),
	@MeasurementUnit VARCHAR(10)
)
RETURNS @T TABLE(
	Value DECIMAL ( 20, 2 )
)
AS
BEGIN
	DECLARE @EarthMeanRadius DECIMAL ( 20, 4 )

	/* Get updated post code source data from:
	https://www.freemaptools.com/download-uk-postcode-lat-lng.htm
	*/

	SET @EarthMeanRadius =
		CASE @MeasurementUnit
			WHEN 'km' THEN 6371.009
			WHEN 'm' THEN 6371.009 * 1000
			WHEN 'miles' THEN 3958.761
			WHEN 'yards' THEN 3958.761 * 1760
			WHEN 'yds' THEN 3958.761 * 1760
			WHEN 'feet' THEN 3958.761 * 1760 * 3
			WHEN 'ft' THEN 3958.761 * 1760 * 3
			WHEN 'nm' THEN 3440.069
			ELSE 0
		END

	INSERT INTO @T
	SELECT
		Distance = 
			ROUND( @EarthMeanRadius * ACOS( COS( PI( ) /2 - RADIANS( 90 - PCT.Latitude) )* COS( PI( ) /2 - 
				RADIANS( 90 - PCF.Latitude ) ) * COS( RADIANS(PCT.Longitude) - 
				RADIANS( PCF.Longitude ) ) + SIN( PI( ) /2 - 
				RADIANS( 90- PCT.Latitude) ) * 
				SIN( PI( ) /2 - 
				RADIANS( 90 - PCF.Latitude ) ) ), 2 )
	FROM GC.dbo.PostCodeLatLng PCF
	INNER JOIN GC.dbo.PostCodeLatLng PCT
		ON PCT.PostCode = @PostCodeTo
	WHERE
		PCF.PostCode = @PostCodeFrom

	RETURN
END
Usage:

Code: Select all

SELECT *
FROM GC.dbo.TVF_PostCodeDistance ( 'NE61 1TB', 'GU1 1EZ', 'miles' )
Example:
PostCodeDistance.PNG
PostCodeDistance.PNG (8.79 KiB) Viewed 334 times
Hope it's useful for someone 8-)