Zip Code database

Folks, if you've wanted to perform zip code based calculations (find zips within a given distance from a zip code), there's a free database (well, a txt file) with that info being offered at http://www.cfdynamics.com/zipbase/. The site offers it free and looks forward to updates from users (and a note of how you're using it).

There's no code offered there showing how to use it in any particular way. There are some CF custom tags that perform distance processing with the latitude and longitude values (also there), though none are offered on that site. See below for the details.

Still other possibilities include using the zipcode to perform lookups in data entry applications to avoid the user having to enter their city and state (be careful not to rely on this data but instead simply use it as a tool to aid data entry). I can't get into the details here, but one way it to use javascript and hidden frames, a technique that has been discussed in various CFDJ article and other resources.

Also, since it's a .txt file, you either need to import that into a DBMS or leverage any of the choices available for processing text files.

Following is some code that will at least allow you to process the file as a text file, should you want to do so, using the new CF5 Merant text drivers. Note that the first line sets the file and path name for the zip_codes.txt file that you'd download at the above site.

<cfset zipcode_filepath = "E:\INETPUB\WWWROOT\cfdemo\ZIP_CODES.txt">

<cfquery name="get" dbtype="dynamic"
connectstring="Driver={MERANT 3.70 ColdFusion OEM 32-BIT TextFile (*.*)};TableType=comma;FirstLineNames=0;IntlSort=1;ApplicationUsingThreads=1;UndefinedTable=guess" cachedwithin="#createtimespan(1,0,0,1)#">
select
field_1 as ZIP,
field_2 as LATITUDE,
field_3 as LONGITUDE,
field_4 as CITY,
field_5 as STATE,
field_6 as COUNTY,
field_7 as ZIP_CLASS
from #zipcode_filepath#
</cfquery>

<cfoutput query="get" maxrows="5">
#zip# - #city# (#county#), #state# ( #latitude#, #longitude#) [#zip_class#]<br>
</cfoutput>

Notes:
- I'm using the MAXROWS attribute of the CFOUTPUT to limit how many records are displayed in the loop

- I'm using the "connectstring" approach for referring to a datasource, which means I don't need to create a DSN in the administrator.

- Since this file has no column names listed in the first line of the file, the Merant driver finds them and by default names then field_1 thru field_x. I've renamed them with an alias in the SELECT to make them more useful later in the code.

- I've used the CachedWithin attribute on the CFQUERY, since the file would not change often. Be aware that this file is 3mb, so loading the entire thing into memory may have some performance impact (likely modest in most systems).

- Finally, this Merant text driver may or may not work in CFMX. My CFMX system is unavailable at the moment so I can't test this right now myself.

Again, rather than process it as a text file, if you're going to search it often, you may as well import it into a database and leverage the indexing possible there.

If I do create or find some code to extend the process of working with these zips, latitudes, longitudes, etc, I'll share it.
For those motivated to get a head start, the GreatCircle and FreeZip custom tags at the MM Dev Exchange look promising. The former does calculations of miles between given lat/lon pairs (though I'm having some trouble with it) and the latter is tied to its own included 1990 census zip code DB. The logic in it could be pulled out to work with this zipcode file.