Radius Search From a Postcode

Posted on 2009-02-12
Last Modified: 2013-12-17
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!

Question by:leap29
    LVL 23

    Expert Comment

    I've done this myself before, easiest way to explain is via this example:
    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
    LVL 53

    Expert Comment


    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??
    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 .

    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)
    DECLARE @NorthingsDiff FLOAT
    DECLARE @EastingsDiff FLOAT
    SELECT @NorthingsDiff = 
    		WHEN @Northings1 > @Northings2 THEN @Northings1 - @Northings2
    		ELSE @Northings2 - @Northings1
    SELECT @EastingsDiff = 
    		WHEN @Eastings1 > @Eastings2 THEN @Eastings1 - @Eastings2
    		ELSE @Eastings2 - @Eastings1
    DECLARE @Hypoteneuse FLOAT
    SET @Hypoteneuse = SQRT(SQUARE(@NorthingsDiff) + SQUARE(@EastingsDiff))
    RETURN @Hypoteneuse 

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    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.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now