Re: Calculations based on zip code

[James Perkins <jrperkinsjr@xxxxxxxxx>]

As far as I know you would have to do this via geolocating. Google and Yahoo
both offer a service for this.

I've done something like this with the Google Maps API to show the closed
locations. What I did was create a table to store the latitude and longitude
of each zip code then used SQL to calculate the distance using, roughly I
should say, the Haversine formula.

Here is a sample SQL statement that might work for you.

SELECT f.lat, f.lng, to_lat, t.lng,
       (3960 * cos(cos(Radians(t.lat)) * cos(Radians(f.lat))
         * cos(Radians(t.lng) - Radians(f.lng))
         sin(Radians(t.lat))
FROM lat_lng AS f, lat_lng AS t
WHERE f.zip = ZIP AND t.zip = SOME_OTHER_ZIP

--
James R. Perkins
http://twitter.com/the_jamezp


On Wed, Nov 11, 2009 at 13:51, Alan Shore <AlanShore@xxxxxxxx> wrote:

> Hi everyone
> I have a couple of problems/challenges/situations that I hope someone can
> point me in the right direction
>
> Challenge 1 :   Calculate the number of miles between 2 zip codes.
>                As the zip code pertains to a post office (I think), what is
> required is the distance between each post office as the crow flies
>                Now I'm sure that I came across something to do with this
> actual situation for the AS/400 many, many, many moons ago, but I cannot
> find it
>
> Challenge 2.    List the zip codes within a certain radius near one
> particular zip code (The radius can change)
>
>
>
> As always, any leads MUCH appreciated
>
>
>
> Alan Shore
> Programmer/Analyst, Distribution
> E:AShore@xxxxxxxxxxx <E%3AAShore@xxxxxxxxxxx>
> P:(631) 200-5019
> C:(631) 880-8640
> "If you're going through Hell, keep going" - Winston Churchill
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.