Finding the shortest distance

# Thread: Finding the shortest distance

1. Junior Member
Join Date
Dec 1969
Posts
15

## 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 co-ordinates and that&#039;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&#039;t know what the best way to find the shortest distance is. Keep comparing the distance with the previous and if it&#039;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>

2. Senior Member
Join Date
Dec 1969
Posts
973

## Select statement order by distance

Use a query to sort the table according to the distance between the two postal codes....

3. Senior Member
Join Date
Dec 1969
Posts
484

## find the distance first

finding the distance between to zipcodes is the most difficult but I have seen some web service doing that (see x-methods.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

4. Junior Member
Join Date
Dec 1969
Posts
15

## RE: Select statement order by distance

The distances aren&#039;t in the table .... so unless I&#039;m being incredibly stupid how would I do this? <BR><BR>I have a table of fixed addresses. (of POP&#039;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&#039;s. I can do this bit fine.<BR><BR>I&#039;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>

5. Junior Member
Join Date
Dec 1969
Posts
15

## RE: find the distance first

I can find the distances just fine.<BR><BR>I don&#039;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&#039;m using a sample of 20 addreses now and it&#039;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.

6. Senior Member
Join Date
Dec 1969
Posts
973

Can&#039;t you do a query using both tables.? Retrieve the information you need and sort on the distance?

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## 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>&#060;%<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>&nbsp; &nbsp; & " ORDER BY " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & "( (latitude-" & lat & ")*(latitude-" & lat & ") " _<BR>&nbsp; &nbsp; &nbsp; &nbsp; & "+ (longitude-" & lon & ")*(longitude-" & lon & ") )"<BR><BR>Set RS = conn.Execute(SQL)<BR>...<BR><BR>%&#062;<BR><BR>The distance between two points is SQR( (x0-x1)^2 + (y0-y1)^2 ), but if you don&#039;t really care about getting the actual distance (and you&#039;d be getting it in degrees or some such, anyway, which is moderately useless), you don&#039;t need to take the square root. You&#039;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 pre-select 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>

8. Junior Member
Join Date
Dec 1969
Posts
15

## RE: query on your geographic table along with your

They&#039;re not on the same database.<BR><BR>Regardless my question was not &#039;how to&#039;, but what is going to be the most efficient method.<BR><BR>I can already get the distances for each record. But I&#039;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&#039;m checking now, I probably won&#039;t notice a small difference, but could prove a big problem if I choose the wrong method at this stage.<BR><BR>That&#039;s all I&#039;m trying to find out :-)

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## So pre-select...

...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>&nbsp; &nbsp; & " WHERE ABS(latitude-" & lat & ") &#060; 1.5 " _<BR>&nbsp; &nbsp; & " AND ABS(longitude-" & lon & ") &#060; 1.7 " _<BR>&nbsp; &nbsp; & " ORDER BY " _ <BR>&nbsp; &nbsp; &nbsp; &nbsp; & "( (latitude-" & lat & ")*(latitude-" & lat & ") " _ <BR>&nbsp; &nbsp; &nbsp; &nbsp; & "+ (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&#039;s not, then you&#039;ll have to do a range check instead.<BR><BR>

10. Junior Member
Join Date
Dec 1969
Posts
15

## I do *not* need any more instructions how to find

Bill, thanks for your rather detailed explanation. Unfortunately my question wasn&#039;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 co-ordinates and that&#039;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&#039;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&#039;ve taken the time to answer if I wasn&#039;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
•