Posted by Nel on 10/10/05 22:51

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:kuadnX9eZvJdKNfeRVn-ug@comcast.com...
> Nel wrote:
>> I am trying to understand the best way to extract a list of users from a
>> table based upon their location.
>>
>> To do this I have a table containing all the UK postcodes with a grid
>> reference x & y.
>> postcode x y
>> AB10 392900 804900
>> AB11 394500 805300
>> AB12 393300 801100
>> AB13 385600 801900
>> AB14 383600 801100
>> AB15 390000 805300
>> AB16 390600 807800
>> AB21 387900 813200
>> AB22 392800 810700
>> AB23 394700 813500
>> AB25 393200 806900
>> AB30 370900 772900
>>
>> In a seperate table I have a list of users including a postcode.
>> id firstname postcode
>> 1 Heather BH4
>> 2 Vicky OL1
>> 3 Paola CF8
>> 4 Joanna W14
>> 5 Steve BD13
>> 6 Sally NN1
>>
>> In short I would like your opinoin on how best (most efficiently) to
>> calculate the nearest 20 users. The method I am using to calculate the
>> distance between the users and any chosen postcode is good old Pythagoras
>> dist = sq root ( (userX * userX) + (userY * userY) - (locationX *
>> locationX) + (locationY * locationY) )
>>
>> It seems that looping through the users to first calculate the distance
>> is essential. After that point I am not sure about the besr way to
>> proceed. Do I write the results to a temp table (with distances) then
>> call them in order of distance or is there a better way?
>>
>> Thanks Nel.
>
> Nel,
>
> First of all, you have a problem with your equation. It assumes 1 degree
> if longitude has the same distance as 1 degree of latitude - which only
> occurs at the equator. Everywhere else, 1 degree of longitude is less
> than 1 degree of latitude. I don't have the correct equation handy, but I
> found it one time quite easily with a google search.

The figures are in metres, not degrees so hopefully this will not be a
problem.


> For instance, say you want everything within 25 miles. In your program,
> define a 50x50 mi. square with the "from" point in the center. That is,
> find the longitude 25 mi. east and west with no change in latitude. Then
> find the maximum and minimum latitude with no change in longitude.

Using a square would make it easier. I had visioned calculating the
hypotinuse for each member.


> This now gives you a range that all the target post codes must be in. Now
> let the database do the work for you. Search the database for all
> postcodes where the longitude is between the min and max above, and the
> latitude is within the min and max.

Genius. So I can narrow down the processor work to a limited few.

> Once you have this (much smaller) set of postcodes, you can run your
> equation again against every one to determine if it is within the 25 mile
> radius circle.

One final question, once I have narrowed down the sqlresult to those 25, and
I calculate the distance, how do I sort the sql results in the order of the
distance calculation?

Thanks,

Nel.

[Back to original message]