[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

Radius Search From a Postcode

I'm trying to implement a radius for areas in miles around a certain  post code.  I have a table in my DB called locations that contain the columns easting and northing.  How do I use these to search for all the locations with a 5 mile radius of a certain postcode???

I have looked at all the posts here and there is loads of different codes to work out distance between two points but I can't work out how to adapt it to do what I want it to do.

Is there a post that covers this if so can you point me in the direction of it? If not could you please give me an idea how I should go about this mind boggling task!

Thanks
0
leap29
Asked:
leap29
  • 3
1 Solution
 
adatheladCommented:
I've done this myself before, easiest way to explain is via this example:

http://www.ordnancesurvey.co.uk/oswebsite/aboutus/reports/misc/calculate.html
0
 
adatheladCommented:
all you need then, is to wrap up the calculation in a function that accepts the northings and eastings of 2 locations and returns the result of the calculation which you can then slip into your query
0
 
DhaestCommented:
0
 
leap29Author Commented:
"all you need then, is to wrap up the calculation in a function that accepts the northings and eastings of 2 locations and returns the result of the calculation which you can then slip into your query"
Do you have an example of how to do this??
0
 
adatheladCommented:
Knocked this up quickly, but does tally up with the same result as the example in my previous link.

Per the link I gave, the results this gives is dependent on the number of digits in your northings + eastings values. So you may need a slight tweak on @Hypoteneuse before you return it - i.e. with 4 digits in the northings/eastings, this gives an answer in 100m .

e.g.
SELECT dbo.[fnDistanceBetweenPoints](1148,4387,1805,5336) gives: 1154.23 (100m units) which = 115.4 km. If youre values are 4 digits long like this, to get an answer in km, just return @Hypoteneuse / 10 from the function.
CREATE FUNCTION [dbo].[fnDistanceBetweenPoints]
	(@Northings1 INTEGER, 
	@Eastings1 INTEGER,
	@Northings2 INTEGER,
	@Eastings2 INTEGER)
RETURNS DECIMAL(10,2)
AS 
BEGIN
DECLARE @NorthingsDiff FLOAT
DECLARE @EastingsDiff FLOAT
SELECT @NorthingsDiff = 
	CASE 
		WHEN @Northings1 > @Northings2 THEN @Northings1 - @Northings2
		ELSE @Northings2 - @Northings1
	END
 
SELECT @EastingsDiff = 
	CASE 
		WHEN @Eastings1 > @Eastings2 THEN @Eastings1 - @Eastings2
		ELSE @Eastings2 - @Eastings1
	END
 
DECLARE @Hypoteneuse FLOAT
SET @Hypoteneuse = SQRT(SQUARE(@NorthingsDiff) + SQUARE(@EastingsDiff))
 
RETURN @Hypoteneuse 
END

Open in new window

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now