
Finding the shortest distance
What I need to do is this. A user enters a postcode and I need to find the closest address from a table of say 20 addresses that I have.<BR><BR>1. Finding the distance between any given 2 postcodes I can do (have a database of their geographic coordinates and that's working perfectly)<BR><BR>2. Listing the contents of the table with a column listing the distance between the fixed address & the input postcode is also no problem.<BR><BR>I just don't know what the best way to find the shortest distance is. Keep comparing the distance with the previous and if it's less then store it in a string & movenext and loop that? There must be a better way. Could I do this with arrays?<BR><BR>Any ideas gratefully received :)<BR>

Select statement order by distance
Use a query to sort the table according to the distance between the two postal codes....

find the distance first
finding the distance between to zipcodes is the most difficult but I have seen some web service doing that (see xmethods.com)<BR>there may be some component as well.<BR>When you have established an array with 20 distances, finding the shorttest is a piece of cake

RE: Select statement order by distance
The distances aren't in the table .... so unless I'm being incredibly stupid how would I do this? <BR><BR>I have a table of fixed addresses. (of POP's. I work for an ISP). What I need to do is have a form where you input a postcode. It will then work out the distance between this postcode the user has put in to *each* of the POP's. I can do this bit fine.<BR><BR>I'm just trying to think of the way that will most efficiently then compare each of those distances and output a POP from the table.<BR><BR>

RE: find the distance first
I can find the distances just fine.<BR><BR>I don't think I expressed myself very well. I want to know what the most efficient (on server resources) and best way is going to be. <BR><BR>Bear in mind that I'm using a sample of 20 addreses now and it's just me testing and looping it & comparing is taking a long time. The eventual database of addreses is going to be over 250 & there will be more than just me using it.

query on your geographic table along with your oth
Can't you do a query using both tables.? Retrieve the information you need and sort on the distance?

You can get a close approximation...
...by doing sum of squares of the diffs in latitude and longitude.<BR><BR>I *assume* you have lat and long of each postal code, yes?<BR><BR>So get lat/long for the primary point first:<BR><BR><%<BR>Set RS1 = conn.Execute("SELECT latitude, longitude FROM locations WHERE id=" & whateverTheIDis)<BR>lat = RS(0)<BR>lon = RS(1)<BR>RS1.close<BR><BR>SQL = "SELECT TOP 20 * FROM locations " _<BR> & " ORDER BY " _<BR> & "( (latitude" & lat & ")*(latitude" & lat & ") " _<BR> & "+ (longitude" & lon & ")*(longitude" & lon & ") )"<BR><BR>Set RS = conn.Execute(SQL)<BR>...<BR><BR>%><BR><BR>The distance between two points is SQR( (x0x1)^2 + (y0y1)^2 ), but if you don't really care about getting the actual distance (and you'd be getting it in degrees or some such, anyway, which is moderately useless), you don't need to take the square root. You'll be ordering by the square of the distance, so that will still give the right ordering.<BR><BR>If the DB you are using has a SQUARE [not square root] function, you could use it in place of the repeated fields and the multiplies.<BR><BR>If you want, you could preselect for close locations by putting a max difference on the latitude and longitude differences.<BR><BR>Since we live on a ball, not a flat surface, that distance formula is most definitely very approximate. It breaks down worse and worse the closer you get to the poles. But for any place south of Dawson Creek [or north of Hobart, Tasmania] it should be good enough.<BR><BR>

RE: query on your geographic table along with your
They're not on the same database.<BR><BR>Regardless my question was not 'how to', but what is going to be the most efficient method.<BR><BR>I can already get the distances for each record. But I'm worried that the performance is going to be bad with about 10 times the addresses I have now and lots of users checking it at the same time. So I need to know that whatever method I choose to do it, is going to be the most efficient. With the small of data I'm checking now, I probably won't notice a small difference, but could prove a big problem if I choose the wrong method at this stage.<BR><BR>That's all I'm trying to find out :)

So preselect...
...pick only the locations that are within X degrees latitude/longitude before you start doing the distance calcs.<BR><BR>SQL = "SELECT TOP 20 * FROM locations " _ <BR> & " WHERE ABS(latitude" & lat & ") < 1.5 " _<BR> & " AND ABS(longitude" & lon & ") < 1.7 " _<BR> & " ORDER BY " _ <BR> & "( (latitude" & lat & ")*(latitude" & lat & ") " _ <BR> & "+ (longitude" & lon & ")*(longitude" & lon & ") )" <BR><BR>The 1.5 and 1.7 in there are arbitrary, of course. Use numbers appropriate to your own system of measurement.<BR><BR>And the ABS function may or may not be available in the DB you are using. If it's not, then you'll have to do a range check instead.<BR><BR>

I do *not* need any more instructions how to find
Bill, thanks for your rather detailed explanation. Unfortunately my question wasn't how to find the distances.<BR><BR>My original post said "1. Finding the distance between any given 2 postcodes I can do (have a database of their geographic coordinates and that's working perfectly) <BR><BR>2. Listing the contents of the table with a column listing the distance between the fixed address & the input postcode is also no problem. <BR><BR>I just don't know what the best way to find the shortest distance is"<BR><BR>I can get the distances no problem. I want to know the *best* and most efficient way of doing it (in terms of resources on the server). Apologies to all who've taken the time to answer if I wasn't clear enough about what I was looking for.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

