Solved

# Radius Search From a Postcode

Posted on 2009-02-12
551 Views
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
Question by:leap29

LVL 23

Expert Comment

I've done this myself before, easiest way to explain is via this example:

0

LVL 23

Expert Comment

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

LVL 53

Expert Comment

0

Author Comment

"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

LVL 23

Accepted Solution

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
``````
0

## Featured Post

### Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.