Zip Code Distance
This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Don't use the zip code itself. It might work for some areas, but certainly doesn't work in the western washington. It's just cut uptoo weird. We bought a zip code database from um... www.zipcodedownload.com which has a big list of cities, zips, and lat/long coordinates. Once you have the lat/long you can do the math easily... Not sure if they have a zip code only version... but the one we bought was like $30 and works just fine... -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
On 8/29/06, Jesse [EMAIL PROTECTED] wrote: This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? http://www.cryptnet.net/fsp/zipdy/ -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
Jesse wrote: This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse You need to get a table that gives you the latitude and longitude of each zip code. The location is of course some where near the center of the zip code so these calculations won't be perfect. Once you have the latitude and longitude it just takes some math to figure out the distance. Not sure where you get the data but someone here probably knows. You can find details on the calculations here... http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
On Tue, 2006-08-29 at 16:30 -0400, Jesse wrote: Does anyone have any ideas? One technique is to calculate set distances (5,10,25,50) between the zip codes in advance and stick the results in a table. Enjoy, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Zip Code Distance
That (using the zip code digits) won't work. Zip codes are assigned by population density, not by distance. A big city might have several zip codes, although they'd all likely have the same first three digits, but check Manhattan for an example. In our area, the core city (Hartford) and some of its suburbs have the same three digits (061), even though they have their own post offices; but there is a ring of suburbs that have a different prefix (060). It would be wrong to assume that all of the 060 suburbs are closer to each other than they are to the central city. I imagine that some of the farm-land areas cover a lot of territory with very few zip codes. I don't know how the mapping software does it, really. They must have big tables of some sort. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 4:31 PM To: MySQL List Subject: Zip Code Distance This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Zip Code Distance
Jesse, Back around 1995-1996 I built a VB app with an Access database that would do much of what you want - it was for shipping and terminal locations - then ported it over to ASP SQL Server a year or two later. I found several raw data files, either CSV or TSV, which were pretty easily imported into most any database. I believe I found the data files through the Census Bureau web site, http://www.census.gov. Took some searching to find, but from the files I finally found, the database listed ZIP code; Lat/Long of the rough center (centroid?) of the ZIP area: city state; population as of 1990 census, and a few other then-relevant columns that I cannot now recall. Dunno if that data is still available, but that's the first place I'd start looking. A warning, though - the terminology on the site doesn't always - or often - reflect what you find after you get where it pointed grin /. This is not necessarily SQL Specific, but it will impact my MySQL application. Using the zip code, how can you determine what's in a (say) 50 mile radius. In other words, if I'm looking for schools within a 50 mile radius of my location (Zip Code 33881), How can I determine this by the zip code? I have seen lots of search engines that do this, but don't know how they determine the distance. I figured that I can use the first 4 digits as a match, then the first 3 digits which will be a little further away, but I don't know how accurate that would be. Does anyone have any ideas? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
