, 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?
Dave BaldwinConnect With a Mentor Fixer 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.
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.
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

solution1368Author Commented:
actually for now, all i have is contractor name and address included zip code. it is in MS SQL Server.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.