| Author |
Topic |
|
travisl
Starting Member
12 Posts |
 Posted - 2005-11-02 : 09:07:46
|
| 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)
7020 Posts |
Posted - 2005-11-02 : 09:16:06
|
| 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 |
 |
|
|
travisl
Starting Member
12 Posts |
Posted - 2005-11-02 : 09:21:23
|
| 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 code2) find all longs/lats within x miles of above result3) find all zips within x above result setWhat am I missing?Thanks,TravisTravis |
|
|
|
travisl
Starting Member
12 Posts |
Posted - 2005-11-02 : 09:21:23
|
| 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 code2) find all longs/lats within x miles of above result3) find all zips within x above result setWhat am I missing?Thanks,TravisTravis |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-02 : 09:34:24
|
| 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 |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
|
|
travisl
Starting Member
12 Posts |
Posted - 2005-11-02 : 11:43:33
|
| 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.aspFor anyone else reading, here is the logic:1) Find the lat/lng for the given zip code2) calculate the lat that is 25 miles north of your lat3) calculate the lat that is 25 miles south of your lat4) calculate the lng that is 25 miles east of your lng5) calculate the lng that is 25 miles west of your lng6) 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!TravisTravis |
|
|
|
Kristen
Test
22859 Posts |
|
|
codezilla94
Starting Member
6 Posts |
Posted - 2007-11-12 : 20:09:54
|
| (spam removed -graz) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
zips within my distance