Our address and zip code analyzer, CDXZipStream, includes a function called CDXRouteMP that performs a wide variety of tasks involving driving calculations, including route optimization.  We’d like to summarize here the variety of output information CDXRouteMP currently provides since it’s grown to be quite a substantial list that not all of our users may be familiar with.

First, let’s take a quick look at all the route calculation choices available through the input box.  This can be accessed by right-clicking on any cell in your Excel worksheet, then selecting CDXRouteMP from the CDXZipStream functions list:

Route calculations based on the shortest and quickest driving route options will result in the shortest driving distance and quickest driving time, respectively.  Calculations based on the preferred route will take into account preferred road types (such as highways, toll roads, arterial roads), which you can specify under the Route Settings Road Types tab.  Also see our post Using Microsoft MapPoint Settings with CDXZipStream for more information.

One more item worth noting:  If you are optimizing a route, the order of the intermediate stops is always optimized based on achieving the quickest driving time.  However, all the other output, such as driving time, distance, trip duration, and cost will be based on the selected route option:  shortest, quickest, or preferred.   Driving directions from waypoint to waypoint will also be based on the selected route option, although the order of the waypoints will again be based on the quickest driving route.

Over the last several months we've been developing Microsoft Excel templates that use CDXZipStream to perform a variety of tasks, ranging from route optimization to zip code radius analysis.  They are pre-formatted and automated so that users simply input their data, click a button or two, and the desired output is returned to the worksheet. The templates are free, work in Microsoft Excel 2003 and up, and can be evaluated with free trials of CDXZipStream and Microsoft MapPoint, which is also used in some of the templates.  (See the top of our links page to download.) At this point there are enough templates that we thought it would be a good idea to review in one place all their functionality and provide links to their YouTube tutorials:

Radius Calculations Template:  Input a list of addresses or zip codes, and this template will identify which ones are within a specified radius distance of a target zip code.  The calculations are based on the straight-line distance between the centroid (center) locations of the zip codes.  This is a one of our more popular templates and can be used for a variety of applications, such as filtering customer addresses around a store or other key location.  You can view the tutorial here.

Nuclear Facility Radius Calculator:  An example application of our Radius Calculations template that identifies nuclear facilities in a radius around a target zip code.   You can view the tutorial here.

Driving Distance Template:  This template works with Microsoft MapPoint to calculate driving time, driving distance, cost, or total trip duration between two lists of addresses. Input the same address for one of the lists if you need driving data around a single location.  This is a very easy way to validate travel expenses.  Please view the tutorial here.

Route Optimization Template (with GPX export):  This template also works with Microsoft MapPoint, to re-order and optimize intermediate stops on a driving route to achieve the shortest possible driving time.  The optimized route can then be exported to a GPX file that is compatible with many GPS devices.  This is a very easy-to-use, economical approach to route optimization that can reduce transportation costs for applications ranging from food delivery services to customer sales calls.  You can view the tutorial here.

Driving Matrix Template:  Input a matrix of addresses or zip codes to calculate driving distance, driving time, cost or trip duration between all combinations of routes.  For instance, the input matrix would look something like this:

Driving calculations will be performed for the route between Address 1 and Address A, Address 1 and Address B, Address 1 and Address C, etc., until all combinations are covered.  This is a quick and easy way to assign driving destinations, such as for delivery, taxi, or limousine services.  You can view the tutorial here.   

Geocoding Template:  Get latitude and longitude data for a long list of addresses.  This template works with Microsoft MapPoint, and can also be used to verify the accuracy of mailing lists based on the quality of the address match to the MapPoint database.   View the tutorial here.   

Reverse Geocoding Template:  Working with Microsoft MapPoint, this template reverse geocodes by finding the closest address to a geocode (latitude and longitude) point. This can be extremely useful for processing points from a gps device.  You can view the tutorial here.   

Although these templates cover many uses and will fit the bill for most clients, please contact us at customsolutions@CDXTech.com if you have a special need and require template customization. 

The CDXZipStream function CDXRouteMP is used to calculate driving distance, time, cost, and total trip duration for two or more stops on a driving route.  It can also optimize a route by reordering intermediate stops on the route in order to achieve the shortest driving time.  Please see our YouTube videos on route optimization and driving calculations for more information.

CDXRouteMP works in conjunction with Microsoft MapPoint, and it's important to make sure that MapPoint settings properly reflect the requirements of the route so the calculated values and route order (when optimizing) are accurate.  It's easy to set MapPoint settings using the Route Settings button on the main dialog box, shown below:

The main dialog box can also be accessed, just by right-clicking on a worksheet cell, in CDXZipStream free templates that are downloadable from our links page.  (This applies to the templates related to driving calculations which employ Microsoft MapPoint.)

After pressing Route Settings, the following dialog appears:

Each tab corresponds to a particular area of interest: driving speeds, fuel, driving costs, road types, and schedule.  There are also some general data to the right which are always visible regardless of the selected tab.

The data on the right covers route optimization and output preferences.  If more than four stops are part of the specified address range being analyzed, you can specify whether the stops will be optimized, i.e. the intermediate stops will be placed in the order that results in the shortest driving time.  Note that route optimization assumes that the first and last stops listed will remain constant; their order in the list will remain unchanged.  If the requested output is a trip summary, you can also request to include a map of all locations and a list of the waypoints according to their order in the driving route.  For output that includes directions and waypoint lists, the lists can be provided either as an array formula (where each value occupies its own worksheet cell), or as a text string in one single worksheet cell.

Note that the order of the stops when performing route optimization is always based on achieving the quickest driving time.  However, if the route type as specified on the main dialog box is the preferred or shortest (distance), then these selections will be accounted for when calculating the actual route directions, driving distance, cost, etc.  The order of the stops will not be affected by these selections, however. 

The driving speeds tab as shown above describes the preferred maximum driving speeds as a function of road type.  When specifying preferences for arterial roads, keep in mind that these are high capacity urban roads that are not quite as large as highways, and are designed to deliver traffic from collector roads to freeways, or between urban centers.

Fuel information on the second tab can impact both driving cost calculations and driving time, the latter particularly for long trips that require multiple stops for refueling:

 Driving costs are also impacted by the settings on the driving cost tab, which can be based on actual fuel consumption or a fixed price per mile:

The road types tab provides a qualitative measure of preferred roads.  This input only has an impact on driving calculations when the route type selected on the main dialog box is "preferred".  If the calculations are based on the "shortest" or "quickest" route, changing these settings will not impact the driving calculation results:

And finally, the schedule tab is useful when calculating total trip duration (including multi-day trips), where it is necessary to account for limitations on daily driving time, required rest periods, and time spent at each stop:

Whenever you perform driving calculations, we recommend that you quickly review the route settings described here to ensure they reflect the driving conditions you expect.  It can also useful to vary the settings to explore how even minor changes to your driving habits may impact cost and time savings.  For instance, estimating the cost savings provided by optimized routing, fuel efficient vehicles, or changes in driving schedules is easy to do by adjusting these settings, and can ultimately provide a good impetus for change.

CDXZipStream, our Microsot Excel add-in, has some very useful functions that perform a variety of location-based calculations, ranging from creating zip code lists for a given city to finding latitude and longitude for an address.  These functions are readily accessible by right-clicking with the mouse on any cell in an Excel worksheet - a "custom function" (similar to standard Excel functions like SUM or AVERAGE) is then inserted into the worksheet cell and performs the desired calculation.  Please refer to our previous blog for more general information about custom functions in Excel.

To help guide CDXZipStream users when accessing these functions, here is a quick review that includes short video tutorials (in Shockwave and Windows Media formats) on their use:

CDXFindZip:  Finds all the zip codes for a given city.  CDXFindZip.swf (1.56 mb)  CDXFindZip.wmv (1.03 mb)

CDXDistance:  Finds the distance between two zip codes.  CDXDistance.swf (1.88 mb)  CDXDistance.wmv (1.15 mb)

CDXRadius:  Finds all zip codes within a radius distance of a target zip code.  When used with Microsoft MapPoint, it can also create a radius map around a target zip code.  CDXRadius.swf (4.41 mb)  CDXRadius.wmv (3.10 mb)

CDXRouteMP:  Working with Microsoft MapPoint, calculates driving time, distance, and cost between two locations, and can also create a map of the route.  CDXRouteMP.swf (5.73 mb)  CDXRouteMP.wmv (3.77 mb)

CDXLocateMP:  Working with Microsoft MapPoint, verifies latitude, longitude, and address information for a given location, and can also create a map of the location.  CDXLocateMP.swf (4.24 mb)  CDXLocateMP.wmv (3.06 mb)

CDXDistance2WP:  Working with Microsoft MapPoint, calculates the distance between two waypoints.  A waypoint is a location defined by its latitude and longitude.  CDXDistance2WP.swf (2.27 mb)  CDXDistance2WP.wmv (1.47 mb)

CDXClosest: Finds the zip code from a list that's closest to a target zip code.  CDXClosest.swf (4.10 mb)  CDXClosest.wmv (2.70 mb)

CDXZipList:  Creates a list of zip codes for any city, county, or state.  CDXZipList.swf (3.08 mb)  CDXZipList.wmv (1.82 mb)

In upcoming posts we'll be reviewing these functions in more detail, showing how they can be used to solve real-world problems when dealing with zip code, address and location-based data in general.  Stay tuned!