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
