|
|
Posted by Jerry Stuckle on 10/10/05 22:39
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.
Once you have that straightened out, you can do it this way - but it's
the hard way. Instead, let the database help you out.
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.
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.
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.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
[Back to original message]
|