If you're using the JCSM database, here's a simple query to pull out all the
zips within #radius# number of miles.
<!--- zip search is here --->
<CFPARAM NAME="Radius" DEFAULT="50">
<CFQUERY NAME="geoCode" DATASOURCE="Zip_Search">
SELECT lat, lon, Zip, LastLineCity, State
FROM jcszip
WHERE Zip = '#zip#'
</cfquery>
<!--- find cities close to a given zip --->
<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>
We purchased this fairly recently and we love it.
-- jon
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]]
Sent: Friday, November 15, 2002 5:04 PM
To: CF-Talk
Subject: Re: [OT] Zip Code Lookup
charlie griefer wrote:
> I'm building an application where i need to do the 'search within x
> miles of zip code 12345'
>
> Can anybody make a recommendation? The client would like something
> that offers updates fairly regularly. Maybe even some sort of XML
> feed direct from the (a) source?
>
> Any leads would be greatly appreciated.
>
> Oh, and would be nice if it integrated easily with CF (to kinda bring
> this question a little bit on topic) :)
Strategy and options depends quite a bit on the usage pattern and the
financial room. I will just presume it is for nearest outlet matching
for now.
If it is for the occasional lookup, I am sure there are webservices
where you can pay per hit. Easy and all the database maintenance is done
for you by the remote partner. But it might be a bit clumsy if you get
back a long list of ZIP codes that you then have to match against your
own database of outlets. And if you get a lot of hits, paying per hit
might not be the best model.
Another option would be to buy a ZIP database with subscriptions for
updates and a bunch of tags for running queries on them. But again, it
might be a bit clumsy if you want to match those results with another
database of outlets.
The last option is to buy the ZIP database and built your own search
system for it. It really isn't that hard to make the database do
distance calculations based on lat/lon pairs, and unless you have a very
special set of datapoints using a smart algorithm and some indexing will
make it very fast, especially since you can do the outlet matching in
the database as well.
I would probably go for option 3, unless there is a business reason for
doing number 1. Let me know if you want some code for option 3.
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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
