Distance query based on longitude & latitude

# Thread: Distance query based on longitude & latitude

1. Senior Member
Join Date
Dec 1969
Posts
490

## Distance query based on longitude & latitude

Ok, I have a tough one now. Well for me anyway. I want to write a query where I can plug in a zip code and it will then query my database, returning all the locations within a 20 mile radius of that zip code. The data will look something like this...<BR><BR>tblZipCodes<BR>ZipCode<BR>CityName< BR>CountyName<BR>StateAbbr<BR>Longitude<BR>Latitud e<BR>etc...<BR><BR>tblLocations<BR>LocationName<BR >LocationAddress<BR>LocationCity<BR>StateAbbr<BR>L ocationZipCode<BR>etc...<BR><BR>The calculation seems pretty hardcore. This is what I found...<BR><BR>dlon = lon2 - lon1<BR>dlat = lat2 - lat1<BR>a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2<BR>c = 2 * atan2( sqrt(a), sqrt(1-a))<BR>d = R * c (where R is the radius of the Earth)<BR><BR>I found an asp function online that will give me the distance between two points, but I need the locations from [x] miles away, not the distance between two points. Regardless, I could probably figure it out that way, but I actually need it in a sql statement. Does anyone know where I can find a query like this or where I should start? Any ideas or suggestions are appreciated.<BR><BR>Thanks,<BR>Wil<BR><BR>

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

## Several prior discussions of this...

SEARCH in the forums for "latitude" and you&#039;ll surely find some.<BR><BR>I think there was one in the Advanced forum not that long ago.<BR><BR>But I have to ask a question: What, pray tell, is the difference between "the distance between two points" and "the locations from [x] miles away"???<BR><BR>Finally, a warning: Zip codes are a *terrible* way to try to find things closer than about 50 miles (except maybe in large cities). I give you an example: We live in a zip code that is about 16 miles North to South, and we live at the far southern end of it. So when I search for a store within (example) 10 miles, the only one I find is that one that is 15 miles away at the far northern end of our zip area!!! Yes, it&#039;s 15 miles away even though I used a radius of 10 miles! WORSE, the store that is in the neighboring zip code is only about 3 miles away, but because *that* zip code is more than 10 miles from our home zip code, the other store is never found.<BR><BR>Anyway, go searching for those earlier conversations on latitude and longitude. You&#039;ll learn a lot, I suspect.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
490

## RE: Several prior discussions of this...

Hey Bill,<BR><BR>There&#039;s no real difference, just how the calculation is setup. That&#039;s why I said I could probably figure that part of it within the function. But my main concern is translating the function into something that works in a sql statement.<BR><BR>As for the zip codes, I think a 50 mile radius would suffice. The stores they will be searching for are very limited, so they&#039;ll probably be lucky if there&#039;s one within 50 miles. But how would you search for something more accurately? Short of having them put their full address in, I wouldn&#039;t know a more specific way. I&#039;m guessing that would require a database that gave longitude and latitude for an address, which has gotta be expensive. This is all new to me though, so maybe I&#039;m 100% wrong.<BR><BR>I&#039;d searched before for &#039;distance latitude&#039; and didn&#039;t come up with much. Or maybe it was &#039;distance longitude&#039;, I can&#039;t remember. I&#039;ll try just &#039;latitude&#039; and see what I get. Didn&#039;t think of checking the other forums either. Doh!<BR><BR>Thanks,<BR>Wil<BR>

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

http://www.aspmessageboard.com/forum/showMessage.asp?F=27&M=713902&P=1<BR><BR>And I think also a prior one by "Abraham" on same topic.<BR><BR>If you read those threads, you&#039;ll see that you DO NOT NEED that extraordinarily complex formula. And you&#039;ll see my techniques for speeding it all up.<BR><BR>Yes, to search more accurately you need a database that translates addresses to Lat/Long and indeed those are expensive. Keep it simple.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
490

&nbsp;<BR>Nice! I&#039;ll try setting it up first thing tomorrow.<BR><BR>By the way, thanks for not completing that statement...&#039;Keep It Simple...Stupid&#039;.<BR><BR>I know that&#039;s what you were thinking though, lol<BR><BR>Thanks Bill!<BR>Wil<BR>

6. Senior Member
Join Date
Dec 1969
Posts
490

Hey Bill,<BR><BR>I worked on it a little this morning and everything&#039;s already running perfect. I only had a sample zipcode database to work from, so the results were limited, but we&#039;re purchasing the full version right now. Should be 100% complete very soon. Thanks again for the help!!<BR><BR>Wil<BR>

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

## I'm impressed...

Not to ask silly questions, but...Isn&#039;t the zip code database available free from the US Post Office and/or the Census Bureau?<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•