, zip code

assume i have contractor database with contractor address and zip code (around 1/2 million records)
and when zip code is entered like 90032 on the webpage and I try to search contractor within 5 miles.

how the query should look like? I just need to get some ideas and best approach.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Easwaran ParamasivamCommented:
What is the criteria to identify the distance beteen the two zip codes?

If there is no obvious way then you have to create one more table to metion what are the locations with in 5 mile location.


Location1 Location2 DistanceInMiles  Zipcode1    Zipcode2
Loc1             Loc2         4.8                    900032       900033    
Loc3             Loc4         4.7                    900034       900035          

and so on..

Then query the table by providing the location or zip code to verify extance in the table. If Yes then you could show in page accordingly.
Dave BaldwinFixer of ProblemsCommented:
The most common way is to use Google Maps and Geocoding.  Here are their pages on doing that:  While the database part is written in PHP/MySQL, it could probably be rewritten in C#/SQL Server.
solution1368Author Commented:
actually for now, all i have is contractor name and address included zip code. it is in MS SQL Server.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Dave BaldwinFixer of ProblemsCommented:
Name and address with zip code is where I started.  My project only had 600 rows though and Google limits the number of requests per hour to get latitude and longitude so it might take a while to get them for your database.

Without fairly precise latitude and longitude, you may have trouble locating the nearest contractor.  In addition, there are zip codes in the US that are more than 5 miles across.  Here's some interesting info about zipcodes:
solution1368Author Commented:
Dave: I think you still don't understand what i am asking. I don't need zip list at all.
I just need a way to find the shortest location based on the user providing zip code and distance.
Dave BaldwinFixer of ProblemsCommented:
when zip code is entered like 90032 on the webpage and I try to search contractor within 5 miles.
Without Geo-location, you can't know the distance.  Many Zip codes are more than 5 or 10 miles across.  If you don't want to do Geo-location, you should just return a list of the contractors with the same zip.  You can't use adjacent zipcodes like 12345 and 12346 and 12347 because they won't be geographically adjacent.  They could be 50 miles away or more.  The example in that article above shows the largest zipcode area is 10,000 square miles.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.