TVF_PostCodeDistance

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply
User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16

TVF_PostCodeDistance

Post by robinwilson16 » Thu Mar 02, 2017 11:28 pm

Returns the distance (as the crow flies if he is going in a straight line) between two post codes outputted in the required unit of measurement.

Link to regularly updated post code source data is:
https://www.freemaptools.com/download-u ... at-lng.htm

File contains millions of rows so if you open and save in Excel this will truncate the file

Code: Select all

CREATE 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

Post Reply

Who is online

Users browsing this forum: No registered users and 2 guests