Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

Building a Zip Code Proximity Search with ColdFusion

The new challenges and possibilities are numerous

Recently I was tasked with improving our Web site's Reseller Locator application. This tool helps potential customers in the U.S. find a product reseller in their state. By choosing a state from a drop-down box, a listing of all resellers located in that state is displayed.

Over the years, as more and more resellers have signed on to sell our products, some problems with this application have surfaced:

  • Some states, such as California, display a very long list of resellers, and customers may never contact those listed near the bottom.
  • The states with smaller populations such as North Dakota may not have any resellers to list.
  • Competitors could use the tool to easily find all our resellers, and may steal these valuable partner relationships.
The Solution Is Defined
We needed a solution to solve these problems, and searching by zip codes appeared to be the answer. The new tool would ask the customer to enter their five digit zip code in a text box and select a search radius of 25, 50, or 100 miles. We wanted to limit the search to 100 miles, to keep the results to an appropriate number and prevent our competitors from entering 2,000 miles and getting a huge list all at once. For example, I might search for all resellers within 50 miles of 55113. This time a list of resellers in close proximity to St. Paul, MN, is displayed, ignoring those 200 miles north in the city of Duluth. Implementing this approach addressed each problem, respectively:
  • A shorter list would result from the search, containing only the resellers located near the customer's zip code.
  • The list can cross state boundaries, now that it will find all resellers located within the specified radius to the customer, and hopefully would show results for zip codes in states like North Dakota.
  • Competitors can still use the tool to find our resellers, but they'll have to work much harder to get the information with the 100 mile limit.
The "Webmonkey" Demo
I've used many store locator Web applications, such as finding the nearest Quiznos or Best Buy, and always wondered how they worked. Now I had the opportunity to learn something brand new, and I began my quest for how to accomplish the Reseller Locator zip code search by starting where everybody does: Google! I quickly found a great tutorial article with sample ColdFusion code on webmonkey.com. The article was written by Robert Capili and titled Proximity Searches for Fun and Profit. The stars were aligned that day, since Robert published his article three weeks before I started my project. It was perfect timing, because as I started reading, it became apparent that this was exactly what I needed to get my feet wet. In the article Robert discusses four primary ways to calculate zip code proximities:
  1. Pythagorean Theorem (remember this trig equation? a2 + b2 = c2)
  2. Spherical Law of Cosines (Pythagorean theorem for triangles drawn on a sphere)
  3. Haversine Formula (most accurate way to calculate distance on a sphere)
  4. Square Search (Robert's speedy solution)
For each method, he explains some background information, how it calculates results, how it performs (speed versus accuracy), and his usage suggestions. I encourage you to read it when you finish this article (or now if you feel the need) to dive deeper into this topic: www.webmonkey.com/webmonkey/05/32/index4a.html?tw=programming. In a nutshell, Robert recommends method #4. He improved upon the speed produced by the Pythagorean method and developed the Square Search. It performs the fastest, but does not take into account the spherical shape of the earth, which the Haversine formula is best at. However, as long as you are not in need of absolute pinpoint accuracy, the Square Search is the way to go for most applications.

Testing the Sample Code
Included in the sample code is a ColdFusion Component, zipfinder.cfc, that implements each of the four search methods. It also contains a testing template, zip.cfm, that has a basic search form, containing a zip code text box and radius text box. Finally, it contains a database file, zipDB. My first step to getting set up was a quick visit to our company's DB Admin, who helped me perform a restore of the SQL database file, zipDB. The database restore creates a single table that contains 29,470 zip code records. Next, I opened Application.cfm and modified the variable application.dsn to use the correct value for our datasource name. Now on to some real testing. I pointed my browser to the zip.cfm template and saw the search form. After entering a zip code and radius in the text boxes, what you get are four cfdumps of the recordsets returned from the CFC, as well as the execution times. Each query gets the city, state, zip code, and distance from the specified zip code. Table 1 and Table 2 show the partial output from a search of 55113.

I soon realized why he recommended the Square Search. The execution time was faster than each of the other methods, and the distance results were very close to the Haversine Formula. After a few more tests, it was soon time to put the final pieces together.

Integrating the Solution
Now I needed to take the recordset returned by the Square Search method and put it to use in my application. We have a database table (actually a view) of resellers that includes the field: zipcode. Nothing special here; most of us are familiar with SQL tables that contain address information broken out in separate fields. All I needed to do was create a new query against the reseller table, and make sure a reseller's zip code was found among the zip codes in the recordset returned by the CFC. The code and query looked like this:

<cfinvoke component="zipfinder" method="squareSearch" radius="#URL.miles#"
zip="#URL.zip#" returnvariable="results"></cfinvoke>

<cfif NOT results.recordcount>
   Sorry, no zip codes found within #URL.miles# miles of #URL.zip#.
   <cfabort>
</cfif>

<cfquery name="get_resellers" datasource="#dsn#">
select *
from PartnerView
where zipcode IN (#ListQualify(ValueList(results.zip),"'")#)
and Country = 'USA'
order by CompanyName
</cfquery>

The first block of code invokes the CFC, calling the SquareSearch method. Next, I verify at least one record is returned. If not, I stop right there and let the user know with a friendly error message. Last, I perform a search against our resellers, using the clause:

where zipcode IN (#ListQualify(ValueList(results.zip),"'")#)

Based on the sample records I listed earlier, the actual query might look like:

select *
from PartnerView
where zipcode IN ('55113','55108','55117','55103','55114','55104','55414','55101','55418')
and Country = 'USA'
order by CompanyName

The ListQualify() and ValueList() ColdFusion functions come in handy here. ValueList() converts a query column into a comma-delimited list, then ListQualify() slaps a single quote around each item in the list. This is exactly what is needed to use as the expression on the right-hand side of the IN clause. All that's left is to display the results from the get_resellers query in a nice HTML format, and the customer has the information he or she needs to make a couple of phone calls that will hopefully lead to a future sale!

More Stories By Troy Pullis

Troy Pullis works as a senior Web developer for Secure Computing Corporation (www.securecomputing.com) and also manages the Twin Cities ColdFusion Users Group (www.colderfusion.com). He is a Certified Advanced CFMX Developer. Back in 1999, Troy shifted his client/server Java programming career to focus on the Internet boom. He immediately started using ColdFusion, began attending CFUG meetings, and has never looked back.

Comments (3) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
CFDJ News Desk 12/15/05 06:33:24 PM EST

Building a Zip Code Proximity Search with ColdFusion. Recently I was tasked with improving our Web site's Reseller Locator application. This tool helps potential customers in the U.S. find a product reseller in their state. By choosing a state from a drop-down box, a listing of all resellers located in that state is displayed.

CFDJ News Desk 12/15/05 06:03:25 PM EST

Building a Zip Code Proximity Search with ColdFusion. Recently I was tasked with improving our Web site's Reseller Locator application. This tool helps potential customers in the U.S. find a product reseller in their state. By choosing a state from a drop-down box, a listing of all resellers located in that state is displayed.

@ThingsExpo Stories
In his keynote at 19th Cloud Expo, Sheng Liang, co-founder and CEO of Rancher Labs, discussed the technological advances and new business opportunities created by the rapid adoption of containers. With the success of Amazon Web Services (AWS) and various open source technologies used to build private clouds, cloud computing has become an essential component of IT strategy. However, users continue to face challenges in implementing clouds, as older technologies evolve and newer ones like Docker c...
Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.
DXWorldEXPO LLC announced today that ICC-USA, a computer systems integrator and server manufacturing company focused on developing products and product appliances, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City. ICC is a computer systems integrator and server manufacturing company focused on developing products and product appliances to meet a wide range of ...
Cloud-enabled transformation has evolved from cost saving measure to business innovation strategy -- one that combines the cloud with cognitive capabilities to drive market disruption. Learn how you can achieve the insight and agility you need to gain a competitive advantage. Industry-acclaimed CTO and cloud expert, Shankar Kalyana presents. Only the most exceptional IBMers are appointed with the rare distinction of IBM Fellow, the highest technical honor in the company. Shankar has also receive...
Michael Maximilien, better known as max or Dr. Max, is a computer scientist with IBM. At IBM Research Triangle Park, he was a principal engineer for the worldwide industry point-of-sale standard: JavaPOS. At IBM Research, some highlights include pioneering research on semantic Web services, mashups, and cloud computing, and platform-as-a-service. He joined the IBM Cloud Labs in 2014 and works closely with Pivotal Inc., to help make the Cloud Found the best PaaS.
In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, led attendees through the exciting evolution of the cloud. He looked at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering m...
DXWorldEXPO LLC announced today that All in Mobile, a mobile app development company from Poland, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. All In Mobile is a mobile app development company from Poland. Since 2014, they maintain passion for developing mobile applications for enterprises and startups worldwide.
We are seeing a major migration of enterprises applications to the cloud. As cloud and business use of real time applications accelerate, legacy networks are no longer able to architecturally support cloud adoption and deliver the performance and security required by highly distributed enterprises. These outdated solutions have become more costly and complicated to implement, install, manage, and maintain.SD-WAN offers unlimited capabilities for accessing the benefits of the cloud and Internet. ...
Headquartered in Plainsboro, NJ, Synametrics Technologies has provided IT professionals and computer systems developers since 1997. Based on the success of their initial product offerings (WinSQL and DeltaCopy), the company continues to create and hone innovative products that help its customers get more from their computer applications, databases and infrastructure. To date, over one million users around the world have chosen Synametrics solutions to help power their accelerated business or per...
Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
Founded in 2000, Chetu Inc. is a global provider of customized software development solutions and IT staff augmentation services for software technology providers. By providing clients with unparalleled niche technology expertise and industry experience, Chetu has become the premiere long-term, back-end software development partner for start-ups, SMBs, and Fortune 500 companies. Chetu is headquartered in Plantation, Florida, with thirteen offices throughout the U.S. and abroad.
Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: Driving Business Strategies with Data Science," is responsible for setting the strategy and defining the Big Data service offerings and capabilities for EMC Global Services Big Data Practice. As the CTO for the Big Data Practice, he is responsible for working with organizations to help them identify where and how to start their big data journeys. He's written several white papers, is an avid blogge...
I think DevOps is now a rambunctious teenager - it's starting to get a mind of its own, wanting to get its own things but it still needs some adult supervision," explained Thomas Hooker, VP of marketing at CollabNet, in this SYS-CON.tv interview at DevOps Summit at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"MobiDev is a software development company and we do complex, custom software development for everybody from entrepreneurs to large enterprises," explained Alan Winters, U.S. Head of Business Development at MobiDev, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
Major trends and emerging technologies – from virtual reality and IoT, to Big Data and algorithms – are helping organizations innovate in the digital era. However, to create real business value, IT must think beyond the ‘what’ of digital transformation to the ‘how’ to harness emerging trends, innovation and disruption. Architecture is the key that underpins and ties all these efforts together. In the digital age, it’s important to invest in architecture, extend the enterprise footprint to the cl...
Data is the fuel that drives the machine learning algorithmic engines and ultimately provides the business value. In his session at Cloud Expo, Ed Featherston, a director and senior enterprise architect at Collaborative Consulting, discussed the key considerations around quality, volume, timeliness, and pedigree that must be dealt with in order to properly fuel that engine.
Two weeks ago (November 3-5), I attended the Cloud Expo Silicon Valley as a speaker, where I presented on the security and privacy due diligence requirements for cloud solutions. Cloud security is a topical issue for every CIO, CISO, and technology buyer. Decision-makers are always looking for insights on how to mitigate the security risks of implementing and using cloud solutions. Based on the presentation topics covered at the conference, as well as the general discussions heard between sessio...
IoT is rapidly becoming mainstream as more and more investments are made into the platforms and technology. As this movement continues to expand and gain momentum it creates a massive wall of noise that can be difficult to sift through. Unfortunately, this inevitably makes IoT less approachable for people to get started with and can hamper efforts to integrate this key technology into your own portfolio. There are so many connected products already in place today with many hundreds more on the h...
No hype cycles or predictions of zillions of things here. IoT is big. You get it. You know your business and have great ideas for a business transformation strategy. What comes next? Time to make it happen. In his session at @ThingsExpo, Jay Mason, Associate Partner at M&S Consulting, presented a step-by-step plan to develop your technology implementation strategy. He discussed the evaluation of communication standards and IoT messaging protocols, data analytics considerations, edge-to-cloud tec...
DXWorldEXPO LLC announced today that the upcoming DXWorldEXPO | CloudEXPO New York event will feature 10 companies from Poland to participate at the "Poland Digital Transformation Pavilion" on November 12-13, 2018.