I need to create a query in Coldfusion that takes a user's zip code
and determines the zip codes within a specific distance. For instance
I want to take a zip code, such as 44121, and find all zip codes
within a 10 mile radius. Im using an access database and COldFusion
6.0.
The database I have was downloaded from
http://www.thedigitalmapstore.com/Free_Stuff/ZIP_Code_Database/zip_code_database.html
Thanks |
Request for Question Clarification by
mathtalk-ga
on
09 Dec 2004 14:48 PST
Hi, ohioroundtable-ga:
How fast does the query need to be?
regards, mathtalk-ga
|
Clarification of Question by
ohioroundtable-ga
on
09 Dec 2004 17:02 PST
It is going to be used in a web environment so it needs to be relatively quick.
|
Request for Question Clarification by
mathtalk-ga
on
09 Dec 2004 18:34 PST
In a previous Question I addressed a pure SQL approach to finding what
is actually a more difficult set, all the zipcodes lying within a
strip of specified width from one zipcode to another. I have some
doubts about how well Access might handle the same queries, but that
would be one approach.
Another approach would be to use a relatively simple query, picking
all the zipcodes which are within the appropriate "box" of latitude
and longitude, then loop through the "query object" in ColdFusion
picking out the ones that are really within the prescribed distance of
a target.
From what I can tell ColdFusion doesn't really support much in the way
of computations. It seems the philosophy is that if you want to do
server side calculations, you hand the job off to Java.
So, I guess there are three options, and I'll defer to you about which
one would best fit your skills and requirements as far as which one to
pursue:
1) do all the selection in SQL
2) use SQL to narrow the candidates, winnow them in ColdFusion(??)
3) use SQL to narrow the candidates, winnow them with a Java class
regards, mathtalk-ga
|
Clarification of Question by
ohioroundtable-ga
on
10 Dec 2004 06:59 PST
mathtalk
Thanks for helping out. I appreciate it.
I'm not quite sure what you mean by "winnow" in options 2 and 3.
Since I only have experience in ColdFusion that is probably the best
way to do it (option 2). However, I did some research on this topic
and found that access may not be able to handle the required
calculations. I'll leave that up to you though.
|
Request for Question Clarification by
mathtalk-ga
on
10 Dec 2004 08:51 PST
By winnow I meant separating the wheat (zipcodes we want to display)
from the chaff (ones that we will discard).
Since you are the ColdFusion expert (relative to me), here are a
couple of questions:
1) ColdFusion can hold numeric values in variables. Can it take a
square root? Can it apply a trigonometric function? I looked around
at examples of ColdFusion "templates" on the Web and didn't see any
that addressed these needs, so I began to suspect that the ColdFusion
philosophy is to do any computations on the server side in custom Java
classes.
2) The strong point of ColdFusion is getting "query objects" from
various data sources and presenting them as HTML. But I'm not clear
how much manipulation can be done on these in ColdFusion. For
example, the tutorials I read make it clear that a query object can be
treated essentially as an array of records, so you can loop through
one of these in an obvious way. What I'm wondering is whether you can
loop through a query object and either delete a subset of rows as you
go, or copy a subset of rows from that query object into a new query
object.
The reason I present these issues is to try and optimize the work in
locating the zipcodes within a given radius. I know how to push all
the work into Access, but if the functionality 1,2 above is present in
ColdFusion then it will probably be expedient to extract a superset of
rows from Access (based on indexing the latitude and longitude values)
and throw away the extra rows.
regards, mathtalk-ga
|
Clarification of Question by
ohioroundtable-ga
on
10 Dec 2004 09:53 PST
mathtalk
I wish I could claim to be an expert in Cold Fusion. Unfortunately
Cold Fusion can not handle square root computations. Cold Fusion
reference: http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Expressions5.htm#2672740
I went through some files I had here from a previous developer and
noticed that Cold Fusion can run SQL functions. DOnt know if this
helps.
I'm not against using java as long as I can get it to flow into a
website fairly easily. If Java is the best way to go Im open to
suggestions.
|
Clarification of Question by
ohioroundtable-ga
on
10 Dec 2004 10:29 PST
Mathtalk
CORRECTION
ColdFusion can make square computations (along with sin, cos and tan functions)
http://livedocs.macromedia.com/coldfusion/5.0/CFML_Reference/Functions2.htm#2184282
Look at the bottom of this page for "Mathimatical Functions"
|
Clarification of Question by
ohioroundtable-ga
on
10 Dec 2004 10:43 PST
This might help. It gives the calculation for the distance between two
zip codes. (It assumes that you know the two zip codes) As you know,
my goal is to use only one zip code and find all the other zip codes
within a specific distance... say 10 miles.)
<!-- ::: Passed to function:
lat1, lon1 = Latitude and Longitude of point 1 (in decimal degrees)
lat2, lon2 = Latitude and Longitude of point 2 (in decimal degrees)
unit = the unit you desire for results
where: 'M' is statute miles
'K' is kilometers (default)
'N' is nautical miles
<cfset radlat1 = Evaluate((pi() * lat1)/180)>
<cfset radlat2 = Evaluate((pi() * lat2)/180)>
<cfset radlon1 = Evaluate((pi() * lon1)/180)>
<cfset radlon2 = Evaluate((pi() * lon2)/180)>
<cfset theta = lat1-lat2>
<cfset radtheta = Evaluate((pi() * theta)/180)>
<cfset dist = Evaluate((60 * 1.1515) * (180 / pi()) *
(ACos((Sin(radlat1) * Sin(radlat2)) + (Cos(radlat1) * Cos(radlat2) *
Cos(radtheta)))))>
|
Request for Question Clarification by
mathtalk-ga
on
10 Dec 2004 12:25 PST
Hi, ohioroundtable-ga:
That mostly clears up the first issue, doing numeric computations in ColdFusion.
We still need to figure out what ColdFusion allows in terms of
constructing or modifying a query object, as outlined in my second
issue.
For the sake of planning, what range of distances do you wish to
allow? You mentioned a 10 mile radius, but would you allow 100 miles?
a thousand miles?
regards, mathtalk-ga
|
Clarification of Question by
ohioroundtable-ga
on
10 Dec 2004 12:36 PST
The options will be restricted to 5, 10, 20, 50 and 100 miles respectively.
|
Clarification of Question by
ohioroundtable-ga
on
10 Dec 2004 12:38 PST
Pertaining to your second question I really cant answer that. Im not quite sure.
|
Request for Question Clarification by
mathtalk-ga
on
10 Dec 2004 16:56 PST
Okay, I've found enough pieces to be convinced it can be done in ColdFusion.
I've seen examples of looping through the result set of a query object
and modifying a column, e.g. we could calculate a distance and update
a column in the base query.
Then there's a mechanism called "Query of Queries" in ColdFusion that
let's you specify a subset of rows from an existing query object:
[About using a Query of Queries]
http://livedocs.macromedia.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/using_recordsets3.htm#1157970
regards, mathtalk-ga
|
Clarification of Question by
ohioroundtable-ga
on
14 Dec 2004 11:45 PST
Im not quite sure on the server type. I dont maintain that portion of
the internet activities. I do know that it is a windows server using
ODBC datasources.
|
