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

__________________
Zeeshan Dar
itHighway
Get in Touch with People Texting your Website
Advanced Real Estate Listings Solution - FREE Hosting

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/Num...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
================================================== =========

You mean something like this?

http://www.mywhizbang.com/zipLocator...+in+that+range

Although given that this is NYC, even a radius of *ZERO* miles gives you more zips than I map for you!

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.

Yes exactly like this one.

www.ithighway.co.uk

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.

The email is [email protected], it's also in my profile. I can pay for the db. Thanks!

www.ithighway.co.uk

From where can I get the DB?

www.ithighway.co.uk

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
================================================== =========

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.