SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 zip code search
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

-Dman100-
Posting Yak Master

209 Posts

Posted - 04/02/2006 :  18:47:06  Show Profile  Reply with Quote
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

USA
2489 Posts

Posted - 04/02/2006 :  23:06:27  Show Profile  Visit MichaelP's Homepage  Reply with Quote
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.
Go to Top of Page

-Dman100-
Posting Yak Master

209 Posts

Posted - 04/03/2006 :  00:45:19  Show Profile  Reply with Quote
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-
Go to Top of Page

Pat Phelan
Posting Yak Master

187 Posts

Posted - 04/03/2006 :  08:40:19  Show Profile  Reply with Quote
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
Go to Top of Page

-Dman100-
Posting Yak Master

209 Posts

Posted - 04/03/2006 :  09:04:00  Show Profile  Reply with Quote
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-
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/03/2006 :  10:30:05  Show Profile  Reply with Quote
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.
Go to Top of Page

Pat Phelan
Posting Yak Master

187 Posts

Posted - 04/03/2006 :  10:51:05  Show Profile  Reply with Quote
Pythagorus did great stuff in the "flat earth" days, but it kind of monkeys up the spherical stuff. I prefer http://en.wikipedia.org/wiki/Great_circle_distance

-PatP
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/03/2006 :  14:18:01  Show Profile  Reply with Quote
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.
Go to Top of Page

codezilla94
Starting Member

6 Posts

Posted - 11/12/2007 :  20:06:47  Show Profile  Visit codezilla94's Homepage  Reply with Quote
(spam removed -graz)
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30217 Posts

Posted - 11/12/2007 :  21:46:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It cost $20 and you can get it for free here!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000