Re: zip code locator problems

Like, adjacent zip codes numerically are only usually "close" geographically, and the converse doesn't even come close to reality. In highly populated areas in particular, your locater will most likely send somebody 10 miles away instead of across the street, and you'll look pretty silly. Search the code archives for the TIGER data (free) and use longitude/latitude and "do it right" :-) You basically can download the free, but large, dataset from the US Census Bureau to associate (most) zip codes with longitude/latitude. With longitude/latitude you can then use any of a half-dozen readily available easy distance functions (Great Circle, planar distance etc). Since you are only worried about dealerships that are close, the differences between the distance functions won't matter a whole lot, so just pick one. If possible, encode the distance function as a "Stored Procedure" or "User-Defined Function". In MySQL, this would require coding it in C and recompiling MySQL itself (ick)... Fortunately, your application just needs the closest, so you could probably get away with: #Look up user's input zip code in TIGER data and get $longitude/$latitude $query = "select (abs(longitude - $longitude) + abs(latitude - $latitude)) as distance from dealers order by distance limit 1"; Actually, use limit 3, and let the humans worry about things like roads and rivers and lakes and they'll know which address is closest. Getting all that kind of data and sorting it out would be, like, rebuilding all of MapsOnUs (et al) and is a Major Project. ----- Original Message ----- From: Tom Beidler <tbeidler1@home.com> Newsgroups: php.general Sent: Tuesday, November 28, 2000 5:29 PM Subject: [PHP] zip code locator problems > I have a dealer locator that attempts to find dealers thru a range of zip > codes. The end user enters a zip, two variables are created $high=$zip+10 > and $low=zip-10 and then runs the following query on a MySQL database; > > $query = "SELECT name, bill_address1, bill_address2, bill_city, bill_state, > bill_zip, phone, email from dealer WHERE bill_country = 'USA' AND (zip <= > '$high' AND bill_zip > '$low') ORDER BY name"; > $result = mysql_query ($query) > or die ("Cannot execute query"); > > Everything works fine except for zips with a 0 preceeding, ie 01095 finds > 10951 and 11021. > > Obvious issues include the zip column in the MySQL database is a varchar and > not an int. I did that to accomidate for hyphenated zip codes, ie. > 44012-0987. > > I can I fix this? > > Any help is greatly appreciated, > Tom > > >>.>>.>>>.>>>>>.>>>>>>>>> > Tom Beidler > Orbit Tech Services > 805.455.7119 (cell) > 805.682.8972 (phone) > 805.682.5833 (fax) > tbeidler@mindspring.com > >>.>>.>>>.>>>>>.>>>>>>>>> > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net >