|
Subject:
|
US zip codes - distance calculator
|
|
Posted By:
|
itHighway
|
Post Date:
|
6/3/2008 5:03:36 AM
|
I have a database having, US zipcodes, longitude and latitude. What I want to do is LIST THOSE ZIPCODES which are at a distance of given miles from a particular zip code.
For Exmaple:
I want to find the zip codes which are at a distance of 15 miles from zipcode 10001 (NYC).
I have the script that uses Zipcodes, latitudes and longitudes of two cities and caculate the distance. But it calculates distance.
www.ithighway.co.uk
|
|
Reply By:
|
dparsons
|
Reply Date:
|
6/3/2008 7:46:19 AM
|
I wrote a similar application a few years ago but I can't seem to find the script at the moment to share the equation with you. What I employed was a bit of Trig to get the distance between points x1,y1 & x2,y2; the minimal short coming of this was that it is an "as the crow flies" distance measurement so the aforementioned points may be 10 driving miles apart but only 9.5 miles as the crow flies.
Further, this is not something you are going to want to do on the fly since this calculation can be quite intensive since you would need to compare 1 set of points to all the other points in your database (I think my Zip Code database was about 90K but I don't remember exactly).
I would suggest writing a sproc that would do this calculation on all the zip codes in your database and then drop the results into another table with a structure similar to:
OriginZipCode 15MileZipCode
10001 10001,10002,10003
You could then wire your UI up to simply select the 15MileZipCode column based on your OriginZipCode, Split() the results and then do any further processing. If I can turn up my old script I will be happy to share it with you but, for the time being, this should give you some direction.
http://codeidol.com/sql/sql-hack/Number-Crunching/Calculate-the-Distance-Between-GPS-Locations/
hth.
-Doug
===========================================================
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
===========================================================
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
===========================================================
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/3/2008 12:45:46 PM
|
You mean something like this?
http://www.mywhizbang.com/zipLocatorAndMapDemo.asp?zipcode=10001&radius=15&Submit1=Find+matching+zip+codes+in+that+range
Although given that this is NYC, even a radius of *ZERO* miles gives you more zips than I map for you!
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/3/2008 12:48:10 PM
|
Incidentally, that's all done with an Access DB. The "trick" is in pre-calculating certain values and stuffing them in the DB, rather than going with raw latitude/longitude.
And that's running on a GoDaddy.com shared host, so you *know* it's underpowered. And yet it still performs adequately.
|
|
Reply By:
|
itHighway
|
Reply Date:
|
6/3/2008 2:04:50 PM
|
Yes exactly like this one.
www.ithighway.co.uk
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/3/2008 2:21:42 PM
|
I'll send you the code when I get home tonight. Your email address in your profile is correct?
As I said, the only major "trick" here is adding a couple of fields to the DB.
I paid a whole $5 for the DB and can't redistribute it, so you'll need to pay your own $5 to get it. Hope that won't break you.
|
|
Reply By:
|
itHighway
|
Reply Date:
|
6/3/2008 2:25:59 PM
|
The email is info@ithighway.co.uk, it's also in my profile. I can pay for the db. Thanks!
www.ithighway.co.uk
|
|
Reply By:
|
itHighway
|
Reply Date:
|
6/3/2008 2:42:23 PM
|
From where can I get the DB?
www.ithighway.co.uk
|
|
Reply By:
|
dparsons
|
Reply Date:
|
6/3/2008 7:40:20 PM
|
For everyone else who is not getting a copy of the source code, here is the calculation that I used. You can determine the (rough) distaince between 2 sets of Lat. and Long. points using the following code:
Const radian = .01745329252
Dim x1, y1, x2, y2 'x = Lat. y = Long.
Dim TotalDistance, TempDistanceValue
'This operation could be preformed in the following equation but doing it this way simplifies the equation.
x1 = x1 * radian
x2 = x2 * radian
y1 = y1 * radian
y2 = y2 * radian
TempDistanceValue = (sin(x1) * sin(x2)) + (cos(x1) * cos(x2)) * cos(abs(y1 - y2))
TempDistanceValue = atn(-TempDistanceValue / Sqr(-TempDistanceValue * TempDistanceValue +1)) + 2 * Atn(1)
TotalDistance = (TempDistanceValue / radian) * 80
hth.
-Doug
===========================================================
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
===========================================================
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
===========================================================
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/3/2008 7:58:16 PM
|
I'll post the code on that site in the next day or two (put a link to "View Source" at the bottom of the page and include a link to a small sample ".mdb" file).
But...
But the formula you are using there is really WAY over-complex!
You would use it when you needed accuracy to a few meters and/or good accuracy over distances of more than 500 or more miles (800 KM).
It's called the "Haversine Formula" and is used for accurately measuring distances on a sphere. But the Earth is *NOT* a sphere, so even it will introduce inaccuraces on the order of a kilometer or so when talking about trans-continental distances.
The real reason it's way overkill is simply that it is about 1000 times more accurate than the latitudes and longitudes you get from typical zip code databases!!
Consider my own situation: The latitude/longitude listed for my zip code is about 3 miles from me. And the zip code for the nearest town is listed as about 12 miles from that. In reality, the town is only 6 miles from me! Further, when I do a radius calculation ("get all zips within 10 miles of me") that *misses* ALL THREE of the closest towns!! All because the lat/long that is in the "books" for a given zip code is (typically) the geographically weighted "center" of the zip code area. If you, like I, live near the edge of the zip code area, and if you don't happen to live in downtown New York...well, it's pointless to use the Haversine Formula to get within a few meters when the data in the DB is off by KILOMETERS!!
So...
Stick with the pythagorean theorem. You can then do the calcs in the SQL query and get reasonably good performance!!
I'll show the other (really minor) tricks I pull when I post the code.
|
|
Reply By:
|
dparsons
|
Reply Date:
|
6/3/2008 8:11:05 PM
|
Quite right, Haversine it is. When I wrote this though accuracy is what I had in mind because of the type of application it was powering and the data we were getting was extremely accurate, or so the client claimed (without a GPS who am I to say it was not accurate!) since they were paying an astronomical amount of money for this and other GeoSpatial data.
I agree that the Pythagorean theroem is the way to go if accuracy is not of the utmost importance and, as you said, it is less resource intenstive.
As with anything in programming there is more then one way to solve a problem and I think that both solutions presented here achieve that with varying pros and cons.
-Doug
===========================================================
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
===========================================================
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
===========================================================
|
|
Reply By:
|
Old Pedant
|
Reply Date:
|
6/5/2008 1:21:49 AM
|
*** FULL DEMO NOW AVAILABLE ***
The full demo of my zip code locator that also maps found zip codes on a Google Map is now available.
Just go to the URL
http://www.ClearviewDesign.com/Newbie
and then click on "demos".
You'll see this demo listed as the first and newest one on that page.
You can click on the link to try the demo or you can download a ".zip" file with all the needed content, including a SAMPLE database.
There is a "README.txt" file in the zipped directory that tells you how to obtain a fully functional database and how to modify it to make queries via the web page faster.
I'm not claiming this is commercial quality software. It is *MEANT* as a demo, and that's all it is. A better database and better human interface are left as an exercise to the reader.
Bill Wilkinson
The Old Pedant
|
