Home » Category » Database & Application Miscellaneous

Database & Application Miscellaneous: zipcode distance calc

212| Thu, 22 May 2008 13:31:00 GMT| gsp_dcs| Comments (5)
I'm using the formula below to calculate the distance between 2 zipcodes. It
is fairly accurate most of the time but occasionally mis-calculates (ie 35
miles between 31901 Columbus, GA and 30303 Atlanta,GA). Does anyone have a
better/more reliable formula?

3959*Atan( (1-
(Sin(BeginLongLat::lattitude/57.3248)*Sin(EndLongLat::lattitude/57.3248)
+Cos(BeginLongLat::lattitude/57.3248)*Cos(EndLongLat::lattitude/57.3248)*Cos
(EndLongLat::longitude/57.3248-BeginLongLat::longitude/57.3248))^2)^.5
/
(Sin(BeginLongLat::lattitude/57.3248)*Sin(EndLongLat::lattitude/57.3248)
+Cos(BeginLongLat::lattitude/57.3248)*Cos(EndLongLat::lattitude/57.3248)*Cos
(EndLongLat::longitude/57.3248-BeginLongLat::longitude/57.3248)))

Keywords & Tags: zipcode, distance, calc, database, application

URL: http://database.itags.org/database-application/158221/
 
«« Prev - Next »» 5 helpful answers below.
In article <b7135$453fd847$d1a8fa82$21167...EDELTACOM.COM>,
"GSP...DCS" <info...dcs.com> wrote:

> I'm using the formula below to calculate the distance between 2 zipcodes.
It
> is fairly accurate most of the time but occasionally mis-calculates (ie 35
> miles between 31901 Columbus, GA and 30303 Atlanta,GA). Does anyone have a
> better/more reliable formula?
>
> 3959*Atan( (1-
> (Sin(BeginLongLat::lattitude/57.3248)*Sin(EndLongLat::lattitude/57.3248)
> +Cos(BeginLongLat::lattitude/57.3248)*Cos(EndLongLat::lattitude/57.3248)*C
os(E
> ndLongLat::longitude/57.3248-BeginLongLat::longitude/57.3248))^2)^.5
> /
> (Sin(BeginLongLat::lattitude/57.3248)*Sin(EndLongLat::lattitude/57.3248)
> +Cos(BeginLongLat::lattitude/57.3248)*Cos(EndLongLat::lattitude/57.3248)*C
os(E
> ndLongLat::longitude/57.3248-BeginLongLat::longitude/57.3248)))

Have a look at <http://en.wikipedia.org/wiki/Great-circle_distance>.
Tom Stiller

PGP fingerprint = 5108 DDB2 9761 EDE5 E7E3
7BDA 71ED 6496 99C0 C7CF

tomstiller | Thu, 22 May 2008 13:33:00 GMT |

Version of FileMaker? If in FM6 or earlier, you will need to make some
tweaks or workarounds. This calc requires accuracy to 16 decimal places
and FM6 only gives you 15 places. Should work beautifully in FM7 or later.

GSP...DCS wrote:
> I'm using the formula below to calculate the distance between 2 zipcodes.
It
> is fairly accurate most of the time but occasionally mis-calculates (ie 35
> miles between 31901 Columbus, GA and 30303 Atlanta,GA). Does anyone have a
> better/more reliable formula?
>
> 3959*Atan( (1-
> (Sin(BeginLongLat::lattitude/57.3248)*Sin(EndLongLat::lattitude/57.3248)
> +Cos(BeginLongLat::lattitude/57.3248)*Cos(EndLongLat::lattitude/57.3248)*C
os(EndLongLat::longitude/57.3248-BeginLongLat::longitude/57.3248))^2)^.5
> /
> (Sin(BeginLongLat::lattitude/57.3248)*Sin(EndLongLat::lattitude/57.3248)
> +Cos(BeginLongLat::lattitude/57.3248)*Cos(EndLongLat::lattitude/57.3248)*C
os(EndLongLat::longitude/57.3248-BeginLongLat::longitude/57.3248)))
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

howard_schlossberg | Thu, 22 May 2008 13:34:00 GMT |

Many thanks to Tom and Howard.

I anticipate moving to 8.5 but for now am using version 6 pro. Howard you
mentioned some tweaks and workarounds for version 6. Can you provide more
insight there? Is it a matter of factoring the long/lat so that most of the
math is performed on integers instead of decimals then reversing the factor
afterward?

Tom I tried the wikipedia link and the formula that appeared least prone to
rounding errors (the third one which uses arctan, arcsin and arccos are not
available in FM6 or 8.5). I cannot get that formula to produce more than a
single result. Have rechecked the formula repeatedly to no avail. I'm fairly
handy with math but certainly do not profess to be a trig wiz.

btw - I created 4 calc fields which use the radians function to convert
long/lat to radians. This helped to clean up the original formula.

Atan(
Sqrt(
(Cos(REndLat) * Sin(REndLon - RBegLon) ^2)
+
(Cos(RBegLat)*Sin(REndLat) - Sin(RBegLat) * Cos(REndLat) * Cos(REndLon -
RBegLon)^2)

/
Sin(RBegLat) * Sin(REndLat)
+
Cos( RBegLat) * Cos(REndLat) * Cos(REndLon - RBegLon)
)
)

"GSP...DCS" <info...dcs.com> wrote in message
news:b7135$453fd847$d1a8fa82$21167...EDELT
ACOM.COM...
> I'm using the formula below to calculate the distance between 2 zipcodes.
> It is fairly accurate most of the time but occasionally mis-calculates (ie
> 35 miles between 31901 Columbus, GA and 30303 Atlanta,GA). Does anyone
> have a better/more reliable formula?
>
> 3959*Atan( (1-
> (Sin(BeginLongLat::lattitude/57.3248)*Sin(EndLongLat::lattitude/57.3248)
> +Cos(BeginLongLat::lattitude/57.3248)*Cos(EndLongLat::lattitude/57.3248)*C
os(EndLongLat::longitude/57.3248-BeginLongLat::longitude/57.3248))^2)^.5
> /
> (Sin(BeginLongLat::lattitude/57.3248)*Sin(EndLongLat::lattitude/57.3248)
> +Cos(BeginLongLat::lattitude/57.3248)*Cos(EndLongLat::lattitude/57.3248)*C
os(EndLongLat::longitude/57.3248-BeginLongLat::longitude/57.3248)))
>

gsp_dcs | Thu, 22 May 2008 13:35:00 GMT |

Well, I'm no math expert, and it was a while ago that I set it up for a
client. As I recall, I tried making some slight tweaks on the calc but
found that it was more accurate at closer distances but much less
accurate at further distances.

In my solution, I let the user enter a zip code and the desired radius.
FileMaker then finds and displays all "store" locations with zip codes
that are within that radius.

In the end, I chose to work it so that it finds all zip codes in a
slightly wider radius and then loops through the found set of records
and omits the ones that are actually too far.

Sorry I can't provide details -- it's been too long. But maybe that
explanation will help point you in the right direction.

GSP...DCS wrote:
> Many thanks to Tom and Howard.
> I anticipate moving to 8.5 but for now am using version 6 pro. Howard you
> mentioned some tweaks and workarounds for version 6. Can you provide more
> insight there? Is it a matter of factoring the long/lat so that most of th
e
> math is performed on integers instead of decimals then reversing the facto
r
> afterward?
> Tom I tried the wikipedia link and the formula that appeared least prone t
o
> rounding errors (the third one which uses arctan, arcsin and arccos are no
t
> available in FM6 or 8.5). I cannot get that formula to produce more than a
> single result. Have rechecked the formula repeatedly to no avail. I'm fair
ly
> handy with math but certainly do not profess to be a trig wiz.
> btw - I created 4 calc fields which use the radians function to convert
> long/lat to radians. This helped to clean up the original formula.
> Atan(
> Sqrt(
> (Cos(REndLat) * Sin(REndLon - RBegLon) ^2)
> +
> (Cos(RBegLat)*Sin(REndLat) - Sin(RBegLat) * Cos(REndLat) * Cos(REndLon -
> RBegLon)^2)
> /
> Sin(RBegLat) * Sin(REndLat)
> +
> Cos( RBegLat) * Cos(REndLat) * Cos(REndLon - RBegLon)
> )
> )
> "GSP...DCS" <info...dcs.com> wrote in message
> news:b7135$453fd847$d1a8fa82$21167...EDELT
ACOM.COM...
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

howard_schlossberg | Thu, 22 May 2008 13:36:00 GMT |

In article <12k26sp8j45vu0f...corp.supernews.com>,
Howard Schlossberg <howard...antispahm.fmprosolutions.com> wrote:

> Well, I'm no math expert, and it was a while ago that I set it up for a
> client. As I recall, I tried making some slight tweaks on the calc but
> found that it was more accurate at closer distances but much less
> accurate at further distances.

A common error is to assume that the sin(angle) is an approximation for
(angle); close enough for very small angles, but not for larger ones.
Tom Stiller

PGP fingerprint = 5108 DDB2 9761 EDE5 E7E3
7BDA 71ED 6496 99C0 C7CF

tomstiller | Thu, 22 May 2008 13:37:00 GMT |

Database & Application Miscellaneous Hot Answers

Database & Application Miscellaneous New questions

Database & Application Miscellaneous Related Categories