the easiest way is to use lat and lon with arc tangents. The best database
for this is at http://www.jcsm.com/
He has everything, zipcodes, area codes and I am sure if you asked he could
prolly even tell you the distance between shoe sizes.
The formula I use is this...
ret_state.lat and lat are beginning and ending lattitudes and the same
applies for longitude.
I easily pull these from bud's databases using zipcode or state or city or
whatever else he breaks it down by.
<!--- use lat and lon to get formula for calculating distance --->
<cfset formula = sin(ret_state.lat)*sin(lat) + (cos(ret_state.lat)) *
cos(lat) * cos(lon - ret_state.lon)>
<!--- calculate distance --->
<cfif formula is 1>
<cfset miles = 0>
<cfelse>
<cfset miles = 3963 * (atn(-formula / sqr(-formula * formula + 1)) + 2
* atn(1))>
</cfif>
----- Original Message -----
From: "Tom Nunamaker" <[EMAIL PROTECTED]>
To: "Fusebox" <[EMAIL PROTECTED]>
Sent: Sunday, May 06, 2001 12:30 AM
Subject: Re: Mileage Search (spelling corrected)
> Correction (it was after midnight when I wrote that)
>
> SearchDistance = form.distance / (1.15 * 60)
>
> Tom
>
> At 12:39 PM 5/5/01, you wrote:
> >If you know that 60 nautical miles is the maximum distance one degree
will
> >ever be, then you can divide the desired distance by 60 NM (1 NM = 1.15
> >Statute miles) to get the degrees that you're searching. For example, if
the
> >desired distance is 414 statute miles, that's about 360 NM (414 / 1.15)
> >That's 6 degrees of the earth's arc.
> >
> >The way I did this a few years ago for our flight plan software (I'm a
USAF
> >pilot) was to use an SQL BETWEEN to get the square around the point
> >instead of a circle. For example, use something like this:
> >
> ><cfscript>
> > SearchDistance = form.distance / 1.15;
> > Lat_low = form.targetlat - searchdistance;
> > Lat_high = form.targetlat + searchdistance;
> > Lon_low = form.targetlon - searchdistance;
> > Lon_high = form.targetlon + searchdistance;
> ></cfscript>
> >
> ><cfquery .....>
> > SELECT zipcodecenters
> > FROM zipcodetable
> > WHERE lat BETWEEN #lat_low# AND#lat_high#
> > AND lon BETWEEN #lon_low# AND #lon_high#
> ></cfquery>
> >
> >Now you have a box around the point instead of a circle. With SQL server
> >2000 you can write a UDF to calculate the distance in your query so your
> >query could even add another line in the WHERE clause:
> >
> > AND CalcDistance(lat,lon,targetlat,targetlon) <= #form.distance#
> >
> >I would check with the query analyzer to make sure SQL server wasn't
> >calculating distances for every row in your database. If it was, just
store
> >the results in a temporary table and do the calculations on the temporary
> >table like Ben Forta does.
> >
> >My older code just looped through the data that was left and calculated
the
> >distance and displayed results if the distance was LTE the desired
distance.
> >We're dealing with airports with at least 5000 feet of runway so there
aren't
> >that many (4700 in our database). The speed is pretty good with this
method.
> >Our planners only needed 50 or 100 mile radii so our results are normally
a
> >few airports. Your mileage may vary... :)
> >
> >You're basically narrowing the data down quickly with the two between
> >statements which are going to be MUCH faster than calculating the
distance
> >for EVERY point in your database.
> >
> >Tom Nunamaker
> >
> >
> >At 11:50 PM 5/4/01, you wrote:
> >
> > >FYI, Ben Forta described how he did exactly this (anecdotaly) the
> > >ColdFusion conference last fall. He was giving an example of how he
> > >actually used his high school trigonometry in the real world!
> > >
> > >No, really he was giving an example of code that started out in CF
(where
> > >the logic was good, but it ran like a dog), an he ended up in stored
> > >procedures, using temporary tables, and ran quite nicely.
> > >
> > >Sorry, he did not give actual code :-( He was just talking
anecdotally.
> > >
> > >A good example of using the power of the database to the fullest, where
CF
> > >just does not cut it.
> > >
> > >
> > >At 10:30 PM 5/2/01, John Quarto-vonTivadar wrote:
> > >
> > >
> > > > > I did this once. It's pretty easy. The trick was to find a
> > > > > table that related zip codes to coordinates (longitude &
> > > > > latitude). Then I found a formula on the net that calculates
> > > > > distances between two points on the globe. My query then
> > > > > found the distance between the base zip code an all other
> > > > > zip codes, and filtered out the ones that were too far.
> > > > >
> > > >
> > > >fortunately this wasn't my question :)
> > > >
> > > >my question was, suppose you make the mileage between you and the
> > potential
> > > >match (or Nearest Store or closest McDonald's etc.) really large,
like
> > Marc
> > > >suggested 8000 miles. Now if you've got a full ZIP/Postal DB the
resultant
> > > >codes list is going to be quite large, in the tens of thousands. This
is
> > > >then the filter for finding matches along the lines of "select
MatchID
> > FROM
> > > >Matches WHERE ZipPostalCode IN (#big-ass-list-here#). The question
is is
> > > >there an easier way to apply the filter, through some sort of join?
My zip
> > > >database also includes Canada so it's even worse since a good portion
> > of the
> > > >results are alphanumeric, so even the advantage of an integer filter
a
> > la US
> > > >zip codes is lost.
> > > >
> > > >On my own timing tests, the bottleneck is not in retrieving the list
of
> > > >possible ZIPs with X miles, it is in using that result set as a
filter on
> > > >the second query. I'd love to somehow let the RDBMS handle the join
> > instead
> > > >of bring the first result set back thru CF and then re-applying it
for the
> > > >second result set.
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
