How do I create a store locator feature?
Many people wish to emulate a "nearest dealer" or "closest store" function. Let's assume you have a Stores table like this:
So, you can imagine a user will enter their Zip code on a web page, let's say it is 07608 (Teterboro, NJ). You can't necessarily determine which store is closest to their Zip based on the value of the Zip code alone, and a relational database doesn't have a very good facility for making such a comparison anyway. You could convert to an INT and take the absolute value of the lowest difference, but that is not a very scientific method at all, since zip codes do not represent equal parts of a grid across the nation.
The first thing you need to find is a high-quality database that contains all the ZIP codes, and correlates them to their coordinates via latitude and longitude. You get what you pay for, so if you find a free version, you will likely spend a long time cleaning it up. One example I found was horribly out of date, had missing/corrupt data, and some values were just plain wrong. It is used later in this article, and I cleaned it up as best as I could; if you want accuracy, you'll want to buy the premium version of one of these products:
Zip Code Download ($30)
Or search around for similar products from other vendors.
Now, why is it so important to have a database that contains latitude and longitude? Primarily because there are quick and dirty ways to determine the distance between two points, given their lat and long coordinates. The user-defined function (UDF) I built for SQL Server 2000 looks like this:
Go ahead and try it with some Zip codes you know are close together, and some you know are far apart:
The calculation is based on this discussion and isn't perfect, of course; it doesn't take into account the fact that Earth isn't a perfect sphere, and it also calculates distances as the crow flies. So the exact mileage won't always be 100% accurate, and occasionally you will find that a store directly across a 4-mile-long, 1-mile-wide lake is deemed closer than the store 1.5 miles the other way.
So now, we need a repository so that we can look up any ZIP in the US, and compare it to our stores. We're going to create a table called Zips:
And then populate it with Zip codes. The format of the population script will look like this:
However, rather than post it all within the article (it's over 3 MB!), I decided to zip it up so you can download and extract it on your own machine. You can download it here:
Once it's in place, you can use a stored procedure like this. The initial query is used to find the closest value match for a Zip code if it is not in our Zips table (sometimes a user makes a typo, or sometimes the Zip code database is out of date or incorrect). Usually, this should still yield a relatively accurate measurement and ranking. Feel free to change TOP 3 to whatever number of matches you think might be relevant.
This should perform quite well as long as your Stores table is not ultra-huge. If you find performance is a problem because of the UDF against the stores table, you might want to add a WHERE clause:
This will make the query only operate against a single state, rather than all ~40,000 rows.
Here is a sample execution and result set:
The distance is not calculated based on the numeric value of the zip code, it is just a label here. So the same technique will work for a system based on UK post codes, Canadian postal codes, etc. Provided you can obtain a database which maps the mailing code to a latitude and longitude, the only thing you will do differently is the name of the tables/columns.
Related ArticlesHow do I build a query with optional parameters?
How do I calculate the median in a table?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing <text>?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I page through a recordset?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?