Zip Code, latitude & longtitude

# Thread: Zip Code, latitude & longtitude

1. Senior Member
Join Date
Dec 1969
Posts
666

## Zip Code, latitude & longtitude

I have a databse with all the zip codes, states, cities, latitude & longtitude.<BR><BR>How can I make it that way that the user can type his zip code plus a miles radius and in return he&#039;ll get the list of cities the within that radius of the zip code?<BR><BR>Thanks a lot!<BR>

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

## Several discussions of this...

...in the past.<BR><BR>Here&#039;s one where I stuck my 73 cents in:<BR>http://www.aspmessageboard.com/forum/showMessage.asp?F=27&M=710170&P=1#709712<BR><BR>If you implement every one of the suggestions I made in that thread, you&#039;d have a system that should be efficient enough even if you are using Access. <BR><BR>The big tricks are: <BR>(1) *avoid* doing the radius calculation except for the zip codes that are within reasonable "range." <BR>(2) store the latitude and longitude in a form that is "friendly" to your calculations (miles work pretty well). Okay to ALSO have the raw data in degrees, but store both forms in the table. <BR>(3) if you are going after the more accurate calculation (and I *still* think it&#039;s a waste of time and coding to do so, but...), then store the SIN() and COS() values in the DB, so they don&#039;t have to be calculated each time. <BR><BR>Just be aware that the more rural the zip code, the less accurate this is going to be. I live about a half mile from a zip code boundary. If I ask to find the nearest "RiteAid" store, I am inevitably given one that is about 8 miles away...even though the closes one is in the next zip code, maybe 2 miles away.<BR><BR>Or put it another way: If I ask for all cities within 6 miles of my zip code I get back only ONE. Yet there are four, but because they are more than 6 miles from the town that is the center of our zip code...<BR><BR>Enough. Again, don&#039;t strain too hard for accuracy on this thing. You ain&#039;t a gonna get it.<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
666

## It's complicared but I'll try to get it.

Thanks!

4. Senior Member
Join Date
Dec 1969
Posts
6,476

## Bill, something I found

long time ago and copied it to a file...<BR><BR>I will be needing to impliment this pretty soon myself...<BR><BR>But, if one stores the longitude and latitudes in radians<BR>then the sp becomes quite a bit easier...<BR>this was all i could find was the little code sniplet...<BR>dont have a copy of the article and forget where i got it...<BR><BR><BR>SELECT a.name, a.city, a.state,<BR> ROUND(<BR> (ACOS(<BR> (SIN(c.latitude) * SIN(a.latitude)) +<BR> (COS(c.latitude) * COS(a.latitude) *<BR> COS(a.longitude - c.longitude))<BR> )<BR> )<BR> * 3963,1<BR> ) AS distance<BR>FROM Airports a, Cities c<BR>WHERE (<BR> ACOS(<BR> (SIN(c.latitude) * SIN(a.latitude)) +<BR> (COS(c.latitude) * COS(a.latitude) * COS(a.longitude - c.longitude))<BR> )<BR> ) * 3963 &#060;= [RADIUS IN MILES]<BR>AND c.zip=&#039;[ZIP CODE]&#039;<BR>ORDER BY distance<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
666

## So what re you saying?

This is the code for what I&#039;m looking for?

6. Senior Member
Join Date
Dec 1969
Posts
6,476

## Not sure maybe Bill can decifer it?

I remember the article saying to have to store the longitud and latitudes as radions<BR><BR>basically my understanding was this would save the need of many more calculations in the where clause and sql...<BR><BR>it was not something I studied in depth and was a while ago...<BR><BR>all i remember is it was a good article so i saved the sp to file, but lost the bookmark to the article...<BR><BR>maybe Bill can tell if calculations seem to work or not?<BR>I really havent played with it at all...<BR><BR>Just filed it away for future references.

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

## That's the code I argued AGAINST

It is WAY WAY TOO SLOW!<BR><BR>It means you have to test EVERY SINGLE ZIP CODE in the entire zip code table by using that VERY slow and VERY cumbersome SQL Query.<BR><BR>Have you ANY idea of how slow SIN and COS are, compared to simple arithmetic? Especially compared to my suggested BETWEEN code?<BR><BR>Go read all of my posts in that other thread.<BR><BR>And the entire idea of using SIN and COS is almost ludicrous, anyway, given how inaccurate zip codes are, in general. As I said in my other post.<BR><BR>

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

Store the SIN and COS of the radians!!!!<BR><BR>You never USE the actual radians in the calculations.<BR><BR>But the important part is *STILL* to limit the actual radius calculations to a relatively small number of zip codes by *first* doing a WHERE filter using a simple rectangular area!<BR><BR>

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

## If you store...

...the precalculated values<BR> SIN(latitude)<BR>and<BR> COS(latitude)<BR>then the only trig caculation that has to be done (assuming you use this [to me overly complex] forumla is<BR> COS(a.longitude - c.longitude)<BR>so, yeah, you&#039;d then also want the longitude in radians.<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
6,476

## Just goes to show you...

anything can be sold if wored right...<BR><BR>The article did sound good, or sound viable when i read it...<BR><BR>it really wasnt something i studied or even reserched...<BR>Just came accross it, read it and saved it...<BR><BR>didnt know it was not good. ;-)<BR><BR>One would think you could get generic codes from somewhere...<BR>so many sites are doing this, one would think.<BR><BR>Off to bed.<BR>Nite

#### Posting Permissions

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