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
 SQL Server Development (2000)
 zips within my distance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

travisl
Starting Member

USA
12 Posts

Posted - 11/02/2005 :  09:07:46  Show Profile  Visit travisl's Homepage  Reply with Quote
I've got a table with all the zip codes and longitudes and latitudes. I've got the calc and the UDF to get a distance for any two given zip codes. But, how then do I find all zip codes that are within 25 miles of my target zip code?

Do I really run a query that uses the UDF to return all the rows where the distance is <= 25? That seems like a LOT of churn!

Or, do I pre-populate a table with all of the zip code combinations and their distance, then query off of that table? That is a HUGE table that will have to be updated every time there is a change to the zip code table.

Is there some other way that I've missed? What are any of you guys doing?

Thanks!
Travis

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/02/2005 :  09:16:06  Show Profile  Reply with Quote
A table with all possible combinations doesn't sound practical. With 50,000 zip codes, you would end up with 2,500,000,000 rows in your table.

You should be able to cut down your query by selecting everything within a square that contains the radius you are interested in. For example, if you are after a five mile radius, select everthing where longitude is within 5 miles east or west, and where latitude is within 5 miles north or south. Then run only that result set through your UDF to select the zip codes you want.


CODO ERGO SUM

Edited by - Michael Valentine Jones on 11/02/2005 09:22:01
Go to Top of Page

travisl
Starting Member

USA
12 Posts

Posted - 11/02/2005 :  09:21:23  Show Profile  Visit travisl's Homepage  Reply with Quote
Michael, thanks for the quick response! But, isn't that the same query or problem? Here is the logic that comes to my mind:

1) find the long/lat of my target zip code

2) find all longs/lats within x miles of above result

3) find all zips within x above result set

What am I missing?

Thanks,
Travis


Travis
Go to Top of Page

travisl
Starting Member

USA
12 Posts

Posted - 11/02/2005 :  09:21:23  Show Profile  Visit travisl's Homepage  Reply with Quote
Michael, thanks for the quick response! But, isn't that the same query or problem? Here is the logic that comes to my mind:

1) find the long/lat of my target zip code

2) find all longs/lats within x miles of above result

3) find all zips within x above result set

What am I missing?

Thanks,
Travis


Travis
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/02/2005 :  09:34:24  Show Profile  Reply with Quote
I assume that your UDF is very CPU intensive, and the query will take a long time to run if you run it against every zip code. The point I am making is that you can run a query that selects a subset of potential zip codes by selecting all zip codes within a square that surrounds the circle containg the actual zip codes you are after. Then you only have to run the UDF against this subset of all zip codes to select the ones you want. This will let your query use an index on longitude and latitude for good performance.

CODO ERGO SUM
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 11/02/2005 :  10:20:40  Show Profile  Reply with Quote
Are either of the following of use to you?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16859
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56494
Go to Top of Page

travisl
Starting Member

USA
12 Posts

Posted - 11/02/2005 :  11:43:33  Show Profile  Visit travisl's Homepage  Reply with Quote
Great links, thanks!

I've found and done a calc now to create the square - this is definitely the way to go.

See this article: http://www.codeproject.com/csharp/ZipCodeUtil.asp


For anyone else reading, here is the logic:

1) Find the lat/lng for the given zip code

2) calculate the lat that is 25 miles north of your lat

3) calculate the lat that is 25 miles south of your lat

4) calculate the lng that is 25 miles east of your lng

5) calculate the lng that is 25 miles west of your lng

6) do a simple query where lat >= min and <= max, etc..

Very fast, very simple.

The problem is, your calculations produce a set of data with a square shape instead of a circle shape. This may or may not be a problem in your application.

If it IS a problem, you can then go back to doing the "heavy" calculation involving finding the distances between given points. But you can limit your given points to the ones inside the square, so it's a much better performing query.

Anyone care to convert the C# calculations to TSQL? :)

Whew :)

Thanks all!
Travis



Travis
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/02/2005 :  16:57:25  Show Profile  Reply with Quote
"Anyone care to convert the C# calculations to TSQL?"

If you're looking for a "Great Circle" algorithm, this may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12572

Kristen
Go to Top of Page

codezilla94
Starting Member

6 Posts

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 11/12/2007 :  21:46:01  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.09 seconds. Powered By: Snitz Forums 2000