| Author |
Topic |
|
-Dman100-
Posting Yak Master
210 Posts |
 Posted - 2006-04-02 : 18:47:06
|
| hello,I'm looking at a way to do a zip code search. I've done some searches and found some information on a radius type of search, which looks like it might work with a slight alteration.I have the following tables:CREATE TABLE dbo.Reps(RepId int IDENTITY (1,1) PRIMARY KEY,First_Name varchar(50) NOT NULL,Last_Name varchar(50) NOT NULL,Address1 varchar(50) NOT NULL,City varchar(50) NOT NULL,State char(2) NOT NULL,PostalCode numeric NOT NULL,Phone varchar(12) NOT NULL,Email varchar(50) NOT NULL,Longitude float NOT NULL,Latitude float NOT NULL)CREATE TABLE dbo.Zips(ZipCode numeric NOT NULL,Longitude float NOT NULL,Latitude float NOT NULL,State char(2) NOT NULL,County varchar(50) NOT NULL)the zip table contains all US zip codes by latitude and longitude. The Reps table contains the list of all our sales reps throughout the US.Here is what I'm trying to do. From our website, a user can request information from our contact form. The user enters their information along with their zip code. I want to search by the zip code entered and locate the nearest sales rep and return the email so I can use the email parameter in the application to send an auto generated email to the appropriate rep.Doing some searches, I found some stored procedures that handled finding zip codes within a certain radius, but this isn't quite what I need. For example, we have several reps within the same state and even in the same areas. Searching by a specified radius could either return multiple reps or possibly no reps depending on the zip code that is submitted by the user. So, I want to do a search where I find the nearest zip code to the zip that is entered by the user.Considering the wildly different geographical sizes and shapes of zip codes, I’m not sure there is a way to establish which zip is "closest" to another other than using a radius extending from the center of the zip code. One possibility I am considering is performing a search in a loop, starting with a very short radius and extending the search until I get a hit. If I start out with a radius of one mile with an increment of half a mile it might take many loop iterations to get a hit. I'm not sure about performance ramifications and if this is a viable solution?I want to create a stored procedure that can accomplish this. There might be a better way to structure my tables listed above as well.Any help would be greatly appreciated. I’m not sure on a solution for this?Thank you.Regards,-D- |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-04-02 : 23:06:27
|
| Well, I don't know how well a "looping" solution would perform or scale. This may be a good case for a de-normalized table that contains your information.Basically, run a query that finds the closest X number of people to EVERY zip code you have on file, and store the result of that in a table. Then, index that new table by zip code, and you should have a very fast "find closest" search. Assuming the zip codes and reps doesn't change on a daily basis, you should be able to re-calculate that table at some off peak time whenever the data does change.I hope someone can come up with a better solution than this though. I don't like these sorts of solutions, but this may be a good solution to this problem. It is A solution anyway, but I dout that it is THE solution.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
 |
|
|
-Dman100-
Posting Yak Master
210 Posts |
Posted - 2006-04-03 : 00:45:19
|
| Hi Michael,Thanks for replying to my post. To query the closest X number of people to EVERY zip code, how would I structure the where clause in that select statement?Should I use a radius search using latitude and longitude coordinates or is there a better method to calculate the nearest zip?Thanks for your input and help. I appreciate it.Regards,-D- |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2006-04-03 : 08:40:19
|
| Assuming that you have a lot more zip codes than reps (almost always a good assumption), you can take the easy answer and add a repid column to the zip code table. For each zip code, compute the closest rep (iteratively) and make that rep the default rep for that zip code. Note that you WILL need to adjust this once the assignments have been made... The population isn't even by a long shot in most cases, and you'd end up with a few reps staggering under the bulk of your customers while the rest of the reps starve!-PatP |
|
|
|
-Dman100-
Posting Yak Master
210 Posts |
Posted - 2006-04-03 : 09:04:00
|
| Thanks Pat,Is there a standard formula to compute the nearest distance between two points? So I can set each zip to the correct rep. My math is failing me.Thanks,-D- |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-03 : 10:30:05
|
| An ancient Greek marketing manager named Pythagorus came up with the following formula to allocate his reps between the closest temples and colliseums:(A*A)+(B*B)=(C*C)...where A is the longitude difference, B is the latitude difference, and C is the distance as the javelin flies. |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-03 : 14:18:01
|
| Unless he is really trying to impress the chicks, I'd stick with the old "A squared plus B squared..."By the way, this is a little known movie error. When the Scarecrow in the Wizard Of Oz finally gets his diploma, he raises his finger and recites "The square of hypoteneus of a right triangle is equal to the square of the sum of the remaining two sides." Which is, of course, incorrect. It is equal to the sum of the squares of the remaining two sides.Just goes to show you what a diploma is worth. |
|
|
|
codezilla94
Starting Member
6 Posts |
Posted - 2007-11-12 : 20:06:47
|
| (spam removed -graz) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
zip code search