It’s funny how the things that should be the easiest to solve can turn in a total nightmare. We offer this great feature called “Where to Buy”. When the customer clicks on the part to get more details there will be an extra button in the header called “Where to Buy.” It will then take the user to a separate window that will allow a look up for store locations in their area where they can purchase the part at a location convenient for them. This should increase the local stores visibility to the internet buyer.
To accomplish this we need to tackle three problems. First, is to secure a good source of zip code information that includes latitude and longitude coordinates. Not a problem for the United States as the internet is loaded with this information for free. We struggled to find a good source of free Canadian zip codes, so that got shelved for the time being.
Next was to find a good SQL command for calculating the distance between zip codes. We wanted to give the user that ability select a range miles to search. Again lots of good examples on the internet, we settled on:
SELECT * FROM ZIPCODE WHERE SQRT(POWER((69.1*(latitude-';
sqlline := sqlline + floattostr(myLat)+')),2)+POWER((69.1*(longitude-('+floattostr(myLong)+'))*cos(latitude/57.3)),2)) < '+myRange;
myLat = latitude of the zip code supplied by user.
myLong = Longitude of zip code supplies by user.
myRange = distance to search with in.
This query supplied a list of all the zip codes within the supplied parameters. The next operation was to build a query of all zip codes and search our store database to find all matches. We perform the query and check to make sure store carries that brand. If the list is empty, we check the list of internet suppliers. Then we build a XML result set and pass it the UI. Give it a try: http://www.showmetheparts.com/wheretobuydemo/
Now things get harder with Canada. We were able to find a list of zip codes for Canada, and swear every person has their own zip code. The database was over 800,000 records and their zip codes are 7 characters long. Our well tested routine took a serious database performance hit because the long/lat result set returned over 4000 records. The whole calculation took over 5 seconds which in a browser is a lifetime.
After closer inspection of the Canadian zip code file we noticed that the first 6 characters were the ones that were significant. So we reloaded the zip code table with unique records based on the first 6 character. We had to change our store query to use Like instead of =. Bing, bang, boom. Our “Where to Buy” option now supports Canada. Seems like a simple enough solution, but it took me about 4 days to figure out the correct solution. (that was not only thing I was working on at the time, lots of projects going on at Vertical Development).
