How does our Microsoft Excel add-in, CDXZipStream, determine the straight-line distance between two points?  This question applies to the following right-click functions:

  CDXDistance - calculates the distance between two zip codes

  CDXDistance2WP - calculates the distance between two points of latitude and longitude, also referred to as waypoints

  CDXClosestZip – determines which zip code out of a list is closest to a target location 

(For calculating driving distance, please refer to our function CDXRouteMP, shown in the Youtube tutorial Driving Distance Calculator in Excel. )

Technically, it’s not a straight line distance in these cases, since we need to take into account the fact that the earth is (approximately) spherical in shape.  To do this, both CDXDistance and CDXDistance2WP use a mathematical equation for the shortest distance along a spherical surface, called the Great Circle formula:

d = a cos^-1[cosA1 CosA2 cos(B1 – B2) + sin A1 SinA2]

Where

d = the shortest path between two points on a sphere, also called the orthodrome

a = the radius of the earth, which is 3958.73926185 miles

A1, A2 = the latitude of points 1 and 2

B1, B2 = the longitude of points 1 and 2

For the functions CDXDistance and CDXClosestZip, which both require zip codes as input, the centroid location of each zip code area is used as the basis for the latitude and longitude points.

Note that this is an approximation of the distance, since the earth is not a perfect sphere (it's actually a lumpy ellipsoid), and changes in altitude along the earth’s surface will also impact the actual distance.  When the latitude and longitude are provided in decimal format, this equation can be converted into the following Excel-friendly formula:

d = 3958.73926185*((2*ASIN(SQRT((SIN((RADIANS(A1)-RADIANS(A2))/2)^2)+  COS(RADIANS(A1))*COS(RADIANS(A2))*(SIN((RADIANS(B1)-RADIANS(B2))/2)^2)))))

For those of you ready to unleash your inner geek, please see the Wolfram Mathworld website for more information about the Great Circle formula.

To see up close how CDXZipStream uses the Great Circle formula, refer to our Youtube tutorial Zip Code Distance Function in Microsoft Excel.

 

Available from our links webpage (under Technical Articles) is an example spreadsheet showing how to use CDXZipStream geocoding and distance functions in an analysis of car dealership locations in the United States.  To download, just click on the link "CDXZipStream - Closing Chrysler Dealers Geocoding Analysis and Distance Matrix".  Let’s review how this works, and how you may be able to use this example in support of your own organization.

In this case there are over 700 dealerships, with locations throughout the United States, which are being considered for closing.  As part of this consideration it is necessary to calculate their relative proximity to each other.  Although it would be possible to calculate the driving distance between each location using the CDXZipStream function CDXRouteMP, a simpler and faster calculation is straight-line distance, based on the latitude and longitude of each dealership.  Since the additional accuracy of driving distance is not necessary for this analysis, we will be using straight-line distance here.

The first step, as shown in the worksheet “Dealers Closing” is to find the latitude and longitude of each dealer, either based on the exact address or by zip code.  The function CDXLocateMP is used to find the latitude and longitude in each case, such as in the following formula in columns I and J, for the first row of data:

=CDXLocateMP(1,D2,E2,F2,G2,"US")

Where

CDXLocateMP is the CDXZipStream custom function formula that finds latitude and longitude

1 indicates that latitude will be calculated and returned to the worksheet; 2 is used for longitude

D2, E2, F2, G2 are the cell addresses of the street, city, state and zip code of each dealership

“US” is the country

Since cell addresses are used in this equation, the equation can be copied down the list of dealerships to obtain latitude for all locations in all rows.  

In the first row of columns K and L the formula uses zip code only:

=CDXLocateMP(1,G2)

This is also copied down the enter list of dealerships.  In cases where MapPoint cannot locate the exact dealership address, only the zip code is used where the centroid of the zip code area is the basis for the geocoding calculation.

Columns M and N uses the Excel IF and ISNUMBER functions to select the latitude and longitude of the zip code centroid when the exact address is missing:

=IF(ISNUMBER(I2),I2,K2)

In the next worksheet, called Dealer Distance Matrix, the location and latitude and longitude data (from columns M and N) of the previous worksheet are placed into a matrix to calculate the distance between all combinations of dealerships.  Straight-line distance is calculated in this matrix using the function CDXDistance2WP.  In the uppermost left hand corner of the matrix, the following formula is used:

=CDXDistance2WP($C5,$D5,E$3,E$4)

Where

CDXDistance2WP is the CDXZipStream custom function formula that calculates distance between two sets of latitude and longitude

$C5, $D5, E$3, and E$4 are the cell addresses of the latitude and longitude pairs

The dollar sign before the column letter of row number in this equation indicates that this value does not change when the formula is copied to other areas of the worksheet.  The 789 x 789 matrix contains the distance in miles between all dealerships, across the range E5 through ADM793.  Due to the number of columns required for this example, Excel 2007 or 2010 must be used.  Excel 2003 can be used for matrices that have 256 columns or less.

The distance data ican now be effectively displayed in the worksheet “Closest Dealers” , showing dealerships according to their distance from a select dealer location.  Just use the drop down box at the top left hand side of the sheet to select a location, and all other dealerships are sorted and displayed by distance using Visual Basic for Applications code.  (Make sure that macros are enabled for this workbook, by clicking on the Macro Security icon from the Developer tab on the Excel ribbon, then select Enable All Macros.)  The VBA  code is accessible by clicking on the Visual Basic icon from the Developer tab and can be customized to suit your particular application, whether it be finding the closest competitor stores, assigning sales representatives, or targeting distribution locations by customer.

For more information about this type of location analysis, please see the following:

Geocoder in Excel (Video tutorial for the CDXLocateMP function)

How to Very Accurately Filter Addresses Based on Distance (CDX Technologies blog)

 

 

Address information is a very basic component of running a business, whether you’re defining a marketing plan based on yours customer’s geographic location or routing service calls for your technical team.  Address accuracy is also critical for minimizing marketing, customer service, and delivery costs. Our Microsoft Excel add-in CDXZipStream is great at helping you perform zip code and address analysis, but we want to highlight here another good option which tends to get overlooked:   geocode (latitude and longitude) information as an alternative to text addresses.

You may remember from third grade geography how the idea of using points of latitude and longitude on the globe started a long time ago in ancient Greece.   The use of this “geocode” system was pretty much limited to navigating the seas until the general use of GPS (Global Positioning Systems) via satellite exploded in the marketplace.  Now a Garmin GPS can get you to Grandma’s house on Thanksgiving Day, and your GPS-enabled iPhone can find your morning coffee at the nearest Starbucks.

The great thing about latitude and longitude data, as opposed to a text address, is that it is an unequivocal identification of a specific place on the map.  Streets get renamed, buildings get torn down and renumbered, zip code areas get added or redefined, but latitude and longitude does not change.  Particularly when address validity is questionable, i.e. the zip code doesn’t match the city, the house number doesn’t exist, or the street name is misspelled, you may want to use latitude and longitude instead.

CDXZipStream, working in conjunction with Microsoft MapPoint, can both geocode an address (find its latitude and longitude) and reverse-geocode (find the closest address for a latitude/longitude point).  It can also use latitude and longitude as input to functions like CDXRouteMP and CDXLocateMP.   For example, if we want to calculate the driving distance to a customer location with a questionable street address, we can use CDXRouteMP with latitude and longitude, like this:

This is equivalent to using the worksheet equation:

=CDXRouteMP(0,0,"752 W End Avenue, NY, NY 10025","40.789283|-73.966078")

Latitude and longitude is provided as “40.789283|-73.966078" where the values are separated by a vertical bar "|".  Just remember to use decimal format and negative values for west and south global locations.  All locations in North America will have positive latitude and negative longitude values, and all European locations will have positive latitude and positive longitude values.

To use latitude and longitude for the function CDXLocateMP, input the geocode data as shown below:

In this case, we are requesting street information for the geocoded point, input as a single-line address.  You can also input latitude and longitude separately as a multiline address, in the street and city input boxes, respectively.  Again, this is equivalent to the worksheet formula:

=CDXLocateMP(4,"40.789283|-73.966078",,,,,1)

Do you know the location exists, but the address can’t be found by Google or MapPoint?  You can find the latitude and longitude of the location in question, or a nearby landmark, by right-clicking on the point in Google Maps and selecting the option “What’s here?”  The latitude and longitude values will automatically show up in the Google search box near the top of the screen.  You can also use resources like Itouchmap.com or Microsoft MapPoint, which shows latitude and longitude of the pointer at the lower right-hand area of the displayed map.  Once you have latitude and longitude, input these values into the CDXRouteMP or CDXLocateMP functions.

Latitude and longitude is also very useful for calculating straight-line distance between locations.  You can use the CDXZipStream function CDXLocateMP to find latitude and longitude for a list of addresses, then use CDXDistance2WP to find the distance between all desired points.  This is a very fast, very accurate calculation.  (For a more detailed description of this process, please refer to our prior blog article “How to Very Accurately Filter Addresses Based on Distance“.)  Don’t have detailed address information?  CDXLocateMP can find latitude and longitude based solely on zip code.  You can then use these points to calculate distance using CDXDistance2WP.  In this case, CDXZipStream uses Microsoft MapPoint to find the latitude and longitude of the zip code centroids. The centroid is the weighted geographic center of each zip code area.  

We also provide preformatted Excel templates which can perform geocoding and reverse-geocoding.  Just cut and paste your address or zip code list into the template and get your data at the click of a button.  These are free and can be downloaded from our links page.

For some short tutorials related to using latitude and longitude with CDXZipStream, please see the following videos:

Get Latitude and Longitude for Addresses in Excel 

Reverse Geocode Template for Microsoft Excel 

Gecoder in Excel 

If you've ever hesitated with indecision when the dessert cart comes around, we know how you feel.  Choice is a good thing when it comes to the sweet things in life, but sometimes a little help is needed to make the right decision.  We especially believe that having a choice is a good thing when it comes to software, and so we've always offered CDXZipStream in several versions to provide our clients with the functionality that best suits their needs.  Now with eight different versions and a wide (and growing) range of capabilities, we thought it would be a good idea to review in detail the functionality of the most popular versions, as an aid to current and future clients as they purchase and upgrade their software. (Note:  Pricing information for both new purchases and upgrades is available here, with discounts available for multiple licenses.  And remember, all versions of CDXZipStream come with a 30-day money-back guarantee, so don't worry, you can send back that cherry tart after taking a bite ...)

CDXZipStream Lite 

- Provides basic demographic data by zip code

  (Data feed is CDXZipCode (Basic). See here for specific data fields for this feed.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

CDXZipStream Basic 

Does everything the Lite version does, and contains additional functionality provided by Microsoft MapPoint.

- Provides basic demographic data by zip code

  (Data feed is CDXZipCode (Basic).  See here for specific data fields for this feed.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Basic:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

CDXZipStream Premium

Does everything the Basic version does, and contains extensive demographic data.

- Provides basic demographic data by zip code, city, county, area code, CBSA, and state

  (Data feeds are CDXZipCode (Premium), CDXCity, CDXCounty, CDXAreaCode, CDXCBSA, CDXState, CDXCensus, and CDXCensus2.   See here for specific data fields for    these feeds.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Premium:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

CDXZipStream Premium ACS

Does everything the Premium version does, and contains demographic data from the latest American Community Survey.

- Provides extensive demographic data by zip code, city, county, area code, CBSA, and state, from the American Community Survey, 2000 Census, latest Business Census,    and other sources.  (Data feeds are CDXZipCode (Premium), CDXCity, CDXCounty, CDXAreaCode, CDXCBSA,  CDXState, CDXCensus, CDXCensus2, CDXACSCBSA,    CDXACSCounty, CDXACSPlace, and CDXACSState   See here for specific data fields for these feeds.)

- Creates zip code lists by state, county, and city

- Performs reverse zip code lookup (finds the city and state for a zip code)

- Calculates the straight-line (as the crow flies) distance between zip codes

- Lists all zip codes within a radius area

- Finds the closest zip code to a target zip

When used in conjunction with Microsoft MapPoint, CDXZipStream Premium ACS:

- Calculates driving distance, driving time, and driving cost between addresses

- Verifies the accuracy of address information

- Optimizes the order of stops on a driving route (route optimization)

- Finds the latitude and longitude of an address

- Finds the closest address for a given latitude and longitude

- Calculates the straight-line distance between points of latitude and longitude

- Creates radius, route, and location maps

Sometimes it's particularly important to get accurate distance information between addresses, and when managing large address lists, the fastest and most accurate approach is to first geocode the locations (get their latitude and longitude) and then use this information as the basis of the distance calculations.  CDXZipStream does this particularly well with a combination of two functions, CDXLocateMP and CDXDistance2WP.  We're going to review step-by-step how you can use these two functions within Microsoft Excel, to filter an address list based on the calculated straight-line (as the crow flies) distance to a target address. 

Let's say you have a long customer address list and would like to determine which ones are closest to a specific store location.  In many cases, especially in sparsely populated areas where zip codes may cover hundreds of square miles, it's not appropriate to use the centroid location of the zip codes as the basis of the distance calculations, the approach used in many zip code-to-zip code calculators.  To get accurate address-to-address distances, first geocode all addresses using CDXLocateMP.  The input box would look like this, where Excel cell references are used as the input for each address.

This is a multi-line address where the street, city, state and zip code are listed in cells A2 through D2.  We also request that latitude is returned to the worksheet cell where the cursor is pointed, and here is the result returned to cell E2:

We repeat the process to get the longitude returned to cell F2, by simply specifying longitude as the returned data.  Now all we have to do is copy and paste the formulas in these cells to the rest of the list, and all customer addresses are now geocoded.

After also geocoding the location of the targeted store, we're now ready to calculate the actual distances to the store based on latitude and longitude, using the function CDXDistance2WP.  Since this function uses a straightforward mathematical formula, the calculations are very fast.  Here's an example of the CDXDistance2WP input:

We use cell addresses for the data, in this case the geocoded "waypoint".  For the target location, we also specify an unchanging cell location using the dollar sign ($) before the column letters and row numbers.

To get distance data for the entire list, we again just copy the resulting equation from the first row of data all along the list of geocoded points.  To see a short tutorial showing the use of both CDXLocate and CDXDistance2WP functions, please see the YouTube video Geocoder in Excel.

Now use Excel's sort capabability to sort the distances, or use the autofilter function for more advanced features.  Autofilter can easily show you all the distances that fall within a radius.  Let's say we want to narrow the list of addresses to those within a 50 mile radius of the target address.  In Excel 2007 and 2010, follow this procedure:

1. Use your cursor to select all the data (addresses and distances) you wish to filter.

2. From the Data tab, select Filter.

3. You should now see drop-down buttons at the top of each column of data.  (You should be using column headings here so the buttons don't cover the first row of data.) Click on the drop-down button in the column of distance data.

4. Click on "Number Filters"

5. Select "Less Than or Equal to"

6. Enter the value "50" in the box to the right of "Less than or Equal to"

All rows that do not meet the 50 mile or less requirement are now hidden.  You can now copy and paste the filtered data to a new workbook to save it.

For more information about autofiltering, see the following links for Excel 2003 and Excel 2007-2010.

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. 

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!

 

It can be frustrating when analyzing a long address list if one or more of the addresses can't be found in MapPoint.  Instead of returning the answer you need, CDXZipStream may return an error message stating that the address is ambiguous or invalid.  You can verify this by entering the address directly in MapPoint and checking the possible locations that return.  If MapPoint can’t find a good match for the address, it will pop up a “Find” dialog and ask you to select the best match.

There are many reasons for this.  The most obvious is that the address is not included in the MapPoint database.  This data is only updated when MapPoint releases a new version, so a new address may not be in the database yet.  Another possibility is a different spelling (such as “Ln” vs  “Lane”) or a generic address (such as “P.O. Box 58”) is being used.  There are also situations where MapPoint finds two or more good matches, or ambiguous data, and needs you to decide.  Finally, it just may be due to the address being invalid.  You can find out more about how CDXZipStream treats this in the support article: CDXZipStream Returns  "Address is Ambiguous or Invalid" Error.

In the CDXLocateMP geocoding function you can set the ambiguous flag to a value of 1 to allow ambiguous data.  (Set this flag by entering the desired value at the end of the formula in the worksheet.)  CDXZipStream will then return data for the first location it finds.   Setting the ambiguous flag to 2 allows you to return any data that MapPoint finds, even if it is a bad match.  Since completely inaccurate data can be returned in this case, it should be used with caution.

Using the CDXRouteMP function will only return data when an exact or ambiguous match is found.  So how should you proceed in cases where you have a valid address but MapPoint can’t find it?  The first step is to get a valid latitude and longitude from another source such as Google or Bing Maps.  Once you have this information you can reverse geocode using the CDXLocateMP function to find the closest valid address in MapPoint. 

For cases where the reverse geocoded coordinates aren't close enough to the desired location, we’ve added the ability in CDXZipStream 10.3.1 to use latitude and longitude into the CDXRouteMP function.  Latitude and longitude should be entered in a single worksheet cell as decimal values in a text string and be separated by a pipe character, like this:  40.8480 | -74.5851.  If MapPoint can find a road close to this location, it will be able to calculate driving distance or optimize a route. 

With CDXZipStream you now have all the tools you need to analyze complex routes, even for locations MapPoint can’t directly find.