Author 
Topic 

travisl
Starting Member
USA
12 Posts 
Posted  11/02/2005 : 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 prepopulate 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

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 


travisl
Starting Member
USA
12 Posts 
Posted  11/02/2005 : 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 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



travisl
Starting Member
USA
12 Posts 
Posted  11/02/2005 : 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 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



Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
7020 Posts 
Posted  11/02/2005 : 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
Flowing Fount of Yak Knowledge
Ireland
2916 Posts 

travisl
Starting Member
USA
12 Posts 
Posted  11/02/2005 : 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.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



Kristen
Test
United Kingdom
22431 Posts 

codezilla94
Starting Member
6 Posts 
Posted  11/12/2007 : 20:09:54

(spam removed graz) 


SwePeso
Patron Saint of Lost Yaks
Sweden
30281 Posts 


Topic 
