Yeah... it's for US only, you're correct.
That's interesting about PostGres, though. I hadn't realized that.
.. another reason to support open source databases!
-- jon
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 18, 2002 4:01 PM
To: CF-Talk
Subject: Re: [OT] Zip Code Lookup
jon roig wrote:
> If you're using the JCSM database, here's a simple query to pull out
> all the zips within #radius# number of miles.
> <CFQUERY NAME="findLocal" DATASOURCE="Zip_Search">
> SELECT jcszip.Zip,
> jcszip.LastLineCity,
> jcszip.State,
> jcszip.Lat,
> jcszip.Lon
> FROM jcszip
> WHERE (((jcszip.lat)>= (#geoCode.Lat# - (#Radius# * 0.0005))
> And (jcszip.lat)<= (#geoCode.Lat# + (#Radius# * 0.0005))
> ) AND ((jcszip.lon)>= (#geoCode.Lon# - (#Radius# * 0.0005))
> And (jcszip.lon)<= (#geoCode.Lon# + (#Radius# * 0.0005))
> ))
> ORDER BY jcszip.Zip
> </cfquery>
I don't know what area the JCSM database covers, but from the looks of
it I wouldn't count on this code unless it only covers the equator
because the distance between meridians differs with the lattitude. And
if you think the difference is insignificant: chances are your search
area is only 50% of what it should be when doing a search from
Anchorage, Alaska.
Just think of the difference in distance between meridians on the
equator and on the poles. The difference between the 0-meridian and the
1-meridian (either east or west) on the equator is 60 miles (n), and on
the poles they actually overlap.
My trigo is a bit rusty, but I will give it a shot to get the code correct:
distance = acos(cos(latA)cos(latB)cos(lonB-lonA)+sin(latA)sin(latB))/360
* 2 * pi * R
R = radius earth in the appropriate units
lon from -180 to 180 degrees
lat from -90 to 90 degrees
If you are dealing with small distances and don't actually overlap one
of the poles a reasonable approximation would be to compensate only the
longitude by division with cos(#geoCode.Lat#):
SELECT *
FROM jcszip
WHERE (((jcszip.lat)>= (#geoCode.Lat# - (#Radius# * 0.0005))
And (jcszip.lat)<= (#geoCode.Lat# + (#Radius# * 0.0005))
) AND ((jcszip.lon)>= (#geoCode.Lon# - (#Radius# *
0.0005)/cos(#geoCode.Lat#))
And (jcszip.lon)<= (#geoCode.Lon# + (#Radius# *
0.0005)/cos(#geoCode.Lat#))
))
ORDER BY jcszip.Zip
(I assume your factor 0.0005 is correct for miles (s).)
Naturally you need to apply the full calculation on the resultset to get
the actual distance, but this first approximation should reduce its size
sufficiently to make that possible.
And for the PostgreSQL users: check /contrib/, this functionality is
built-in to the database.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
