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
