Click here to close now.




















Welcome!

You will be redirected in 30 seconds or close now.

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

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
As more and more data is generated from a variety of connected devices, the need to get insights from this data and predict future behavior and trends is increasingly essential for businesses. Real-time stream processing is needed in a variety of different industries such as Manufacturing, Oil and Gas, Automobile, Finance, Online Retail, Smart Grids, and Healthcare. Azure Stream Analytics is a fully managed distributed stream computation service that provides low latency, scalable processing of streaming data in the cloud with an enterprise grade SLA. It features built-in integration with Azur...
WebRTC has had a real tough three or four years, and so have those working with it. Only a few short years ago, the development world were excited about WebRTC and proclaiming how awesome it was. You might have played with the technology a couple of years ago, only to find the extra infrastructure requirements were painful to implement and poorly documented. This probably left a bitter taste in your mouth, especially when things went wrong.
Through WebRTC, audio and video communications are being embedded more easily than ever into applications, helping carriers, enterprises and independent software vendors deliver greater functionality to their end users. With today’s business world increasingly focused on outcomes, users’ growing calls for ease of use, and businesses craving smarter, tighter integration, what’s the next step in delivering a richer, more immersive experience? That richer, more fully integrated experience comes about through a Communications Platform as a Service which allows for messaging, screen sharing, video...
SYS-CON Events announced today that IceWarp will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. IceWarp, the leader of cloud and on-premise messaging, delivers secured email, chat, documents, conferencing and collaboration to today's mobile workforce, all in one unified interface
The Internet of Things (IoT) is about the digitization of physical assets including sensors, devices, machines, gateways, and the network. It creates possibilities for significant value creation and new revenue generating business models via data democratization and ubiquitous analytics across IoT networks. The explosion of data in all forms in IoT requires a more robust and broader lens in order to enable smarter timely actions and better outcomes. Business operations become the key driver of IoT applications and projects. Business operations, IT, and data scientists need advanced analytics t...
With the proliferation of connected devices underpinning new Internet of Things systems, Brandon Schulz, Director of Luxoft IoT – Retail, will be looking at the transformation of the retail customer experience in brick and mortar stores in his session at @ThingsExpo. Questions he will address include: Will beacons drop to the wayside like QR codes, or be a proximity-based profit driver? How will the customer experience change in stores of all types when everything can be instrumented and analyzed? As an area of investment, how might a retail company move towards an innovation methodolo...
As more intelligent IoT applications shift into gear, they’re merging into the ever-increasing traffic flow of the Internet. It won’t be long before we experience bottlenecks, as IoT traffic peaks during rush hours. Organizations that are unprepared will find themselves by the side of the road unable to cross back into the fast lane. As billions of new devices begin to communicate and exchange data – will your infrastructure be scalable enough to handle this new interconnected world?
Too often with compelling new technologies market participants become overly enamored with that attractiveness of the technology and neglect underlying business drivers. This tendency, what some call the “newest shiny object syndrome,” is understandable given that virtually all of us are heavily engaged in technology. But it is also mistaken. Without concrete business cases driving its deployment, IoT, like many other technologies before it, will fade into obscurity.
Consumer IoT applications provide data about the user that just doesn’t exist in traditional PC or mobile web applications. This rich data, or “context,” enables the highly personalized consumer experiences that characterize many consumer IoT apps. This same data is also providing brands with unprecedented insight into how their connected products are being used, while, at the same time, powering highly targeted engagement and marketing opportunities. In his session at @ThingsExpo, Nathan Treloar, President and COO of Bebaio, will explore examples of brands transforming their businesses by t...
SYS-CON Events announced today that Micron Technology, Inc., a global leader in advanced semiconductor systems, will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Micron’s broad portfolio of high-performance memory technologies – including DRAM, NAND and NOR Flash – is the basis for solid state drives, modules, multichip packages and other system solutions. Backed by more than 35 years of technology leadership, Micron's memory solutions enable the world's most innovative computing, consumer,...
SYS-CON Events announced today that Pythian, a global IT services company specializing in helping companies leverage disruptive technologies to optimize revenue-generating systems, has been named “Bronze Sponsor” of SYS-CON's 17th Cloud Expo, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Founded in 1997, Pythian is a global IT services company that helps companies compete by adopting disruptive technologies such as cloud, Big Data, advanced analytics, and DevOps to advance innovation and increase agility. Specializing in designing, imple...
Akana has announced the availability of the new Akana Healthcare Solution. The API-driven solution helps healthcare organizations accelerate their transition to being secure, digitally interoperable businesses. It leverages the Health Level Seven International Fast Healthcare Interoperability Resources (HL7 FHIR) standard to enable broader business use of medical data. Akana developed the Healthcare Solution in response to healthcare businesses that want to increase electronic, multi-device access to health records while reducing operating costs and complying with government regulations.
SYS-CON Events announced today that HPM Networks will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. For 20 years, HPM Networks has been integrating technology solutions that solve complex business challenges. HPM Networks has designed solutions for both SMB and enterprise customers throughout the San Francisco Bay Area.
For IoT to grow as quickly as analyst firms’ project, a lot is going to fall on developers to quickly bring applications to market. But the lack of a standard development platform threatens to slow growth and make application development more time consuming and costly, much like we’ve seen in the mobile space. In his session at @ThingsExpo, Mike Weiner, Product Manager of the Omega DevCloud with KORE Telematics Inc., discussed the evolving requirements for developers as IoT matures and conducted a live demonstration of how quickly application development can happen when the need to comply wit...
The Internet of Everything (IoE) brings together people, process, data and things to make networked connections more relevant and valuable than ever before – transforming information into knowledge and knowledge into wisdom. IoE creates new capabilities, richer experiences, and unprecedented opportunities to improve business and government operations, decision making and mission support capabilities.
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at @ThingsExpo, James Kirkland, Red Hat's Chief Architect for the Internet of Things and Intelligent Systems, described how to revolutionize your archit...
MuleSoft has announced the findings of its 2015 Connectivity Benchmark Report on the adoption and business impact of APIs. The findings suggest traditional businesses are quickly evolving into "composable enterprises" built out of hundreds of connected software services, applications and devices. Most are embracing the Internet of Things (IoT) and microservices technologies like Docker. A majority are integrating wearables, like smart watches, and more than half plan to generate revenue with APIs within the next year.
Growth hacking is common for startups to make unheard-of progress in building their business. Career Hacks can help Geek Girls and those who support them (yes, that's you too, Dad!) to excel in this typically male-dominated world. Get ready to learn the facts: Is there a bias against women in the tech / developer communities? Why are women 50% of the workforce, but hold only 24% of the STEM or IT positions? Some beginnings of what to do about it! In her Opening Keynote at 16th Cloud Expo, Sandy Carter, IBM General Manager Cloud Ecosystem and Developers, and a Social Business Evangelist, d...
In his keynote at 16th Cloud Expo, Rodney Rogers, CEO of Virtustream, discussed the evolution of the company from inception to its recent acquisition by EMC – including personal insights, lessons learned (and some WTF moments) along the way. Learn how Virtustream’s unique approach of combining the economics and elasticity of the consumer cloud model with proper performance, application automation and security into a platform became a breakout success with enterprise customers and a natural fit for the EMC Federation.
The Internet of Things is not only adding billions of sensors and billions of terabytes to the Internet. It is also forcing a fundamental change in the way we envision Information Technology. For the first time, more data is being created by devices at the edge of the Internet rather than from centralized systems. What does this mean for today's IT professional? In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists addressed this very serious issue of profound change in the industry.