Using CDXZipStream and Maptitude From Other Programs

Now that CDXZipStream works with Calipers's Maptitude software did you know that you can use the same CDXZipStream functions in applications other than Excel?  This includes Office 365 applications such as Access that support VBA (Visual Basic for Applications) or any application programming environment that supports COM (Component Object Model) connections such as Python.  CDXZipStream does not have to be running in Excel for this to work.

We’ve talked about programming in VBA with CDXZipStream in other articles which include.

Calling CDXZipStream with Visual Basic – Part 1

Calling CDXZipStream with Visual Basic – Part 2

Using CDXZipStream with VBA in Microsoft Access

So rather that cover this again will talk about what is involved to use the CDXZipStream Maptitude fuctions with VBA.

Caliper Maptitude does have extensive documentation with how to interact with the program using C#, Python VBA and other environments.  This is the information we used to get the program to work with Excel.  But this environment is primarily suited to programmer’s and we wound up generating several thousand lines of code to handle the geocoding and routing calls in CDXZipStream.  Rather than doing this work yourself you can use CDXZipStream and VBA to access each these functions in 20 lines of code.

As an example the spreadsheet Maptitude VBA Calls.xlsm contains code in VBA Module 1 for both the routing and geocoding CDXZipStream calls as follows:

  • Public oAdd As Object
  • Public Function GeocodeMaptitude(Output As Integer, Address As String, Optional City As String, Optional State As String, Optional PostalCode As String, Optional Country As String, Optional Ambiguous As Integer) 
  • On Error GoTo Err
  •   If oAdd Is Nothing Then Set oAdd = CreateObject("CDXZipStreamCF.Connect")  
  •    GeocodeMaptitude = oAdd.CDXLocateMaptitude(Output, Address, City, State, PostalCode, Country, Ambiguous)
  •    Exit Function
  • Err:
  •    GeocodeMaptitude = "An Error Number " & Err.Number & "Occurred: " & Err.Description 
  • End Function
  • Public Function RouteMaptitude(iMPType As Integer, Output As Integer, ParamArray WPoints())
  •    Dim TmpArray As Variant
  •    On Error GoTo Err
  •  TmpArray = WPoints  
  • On Error GoTo Err  
  •  If oAdd Is Nothing Then Set oAdd = CreateObject("CDXZipStreamCF.Connect")
  •   RouteMaptitude = oAdd.CDXRouteMaptitudeWrapper(iMPType, Output, TmpArray)
  • Exit Function
  • Err:
  •  RouteMaptitude = "An Error Number " & Err.Number & "Occurred: " & Err.Description
  • End Function

These calls work exactly like the CDXZipStream so you can use the CDXZipStream documentation for CDXRouteMaptitude and CDXLocateMaptitude to set the parameter required.   For instance to return the latitude and longitude of zip code 07869 the following formula would be used:

=GeocodeMaptitude(3,"07869")

You can also geocode by full address or city/state as CDXZipStream will automatically manage the individual requests to Maptitude layers for these parameters.  The function to reverse geocode as follows

=GeocodeMaptitude(0,40.84,-74.56)

As an example of routing to determine driving distance you can use the following call

=RouteMaptitude(1,0,"07869","07860")

that will determine the driving distance in miles between these two zip codes using the quickest route.  This can also be used with full addresses and more than just two stops.

CDX Technologies can also provide consulting services if you want to build these functions directly into your application.

We think you will find this approach will save you considerable time, expense and aggravation. 

 

 

 

CDXZipStream Version 4 with Maptitude Support

We have just released CDXZipStream version 4 which works with Caliper’s Maptitude mapping software. Maptitude is the most powerful desktop mapping solution available and is an excellent replacement for Microsoft's discontinued Mappoint software. Two new functions have been added for geocoding and routing named CDXRouteMaptitude and CDXLocateMaptitude.  These functions will work in a similar fashion to those used for Mappoint and Bing. You can easily access Maptitude data right in Excel or from other applications. Existing CDXZipStream customers will be able to optionally add them to any current version of CDXZipStream. 

While Bing Maps offers greater accuracy and worldwide coverage, it is a transaction-based system with a very complex licensing model.  As with Mappoint, Maptitude installs locally on your machine and your only cost is the one-time license fee regardless of the number of times you use it.  Large-scale analysis projects can be performed with Maptitude that otherwise would have been prohibitively expensive with Bing enterprise licenses which can cost .5 to 1.5 cents per address request. 

The price to add this Maptitude capability to any version of CDXZipStream is $99.95.  This does not include Maptitude itself which is a separate purchase.   Our first release will only support the US version of Maptitude although we expect to support other countries shortly. 

The cost of the latest version of Caliper Maptitude 2019 is $695 and includes country data for the US.   A demo of Maptitude is available at the following link.  We do highly recommend that you use the latest version of Caliper Maptitude because of significant speed, reliability and routing improvements.  If you have a previous version of Maptitude upgrades are available at a reduced price.

Our latest demo now includes a demonstration of the new Maptitude functions. If you would like to test Maptitude with your currently activated copy of CDXZipStream please email sales@cdxtech.com.

New Free Online Tool for ZIP Code Distance

We just expanded our offering of free online tools with a ZIP Code Distance Report.  All you need to enter is two US ZIP Copes in this mobile-friendly web application.   The output report is shown below and contains the straight-line distance, driving distance, distance type, route map and the latitude and longitude for both ZIP Codes.

 

 
 
The distance can be optionally specified in miles, kilometers or nautical miles. Bing Maps is used for the driving distance calculation and displayed map. CDXGeodata is used for the straight-line distance calculation.  The results will match the CDXZipStream formula for straight-line distance CDXDistance and driving distance with CDXRouteBing.  

You'll find this to be a useful tool if you are away from your PC and Excel and want to look up a route on your phone or other device.  Our website also has other free online tools for demographics, ZIP Code lookups and radius reports. 

CDXZipStream Template Update

We currently offer 16 free templates to help you automate CDXZipStream and reduce your learning curve to get up and running quickly. Templates are available for all CDXZipStream functions including geocoding, radius, routing, and distance calculations.  Simply drop your data into the template and press a button to get your results. The templates are available for download at this link. They are available to both registered versions of CDXZipStream and demo users. 

We've converted all of the templates to the .xlsm format so that they will be fully compatible with the latest versions of Excel and Windows 10. The templates will run in versions of Excel 2010 and later. All are digitally signed so they can be run in secure environments that require this. Visual Basic for Applications (VBA) macro language provides the automation that powers the templates. For more information on enabling macros in Excel please see the following article.

The templates are a more efficient way to deal with calculating long lists.  Copying large groups of formulas in Excel can be quite cumbersome. Excel doesn't calculate formulas linearly and can appear to be "hung" even though calculations are proceeding in the background. The templates calculate in the order your data is entered and have an indicator showing progress to completion.    

We urge you to take advantage of this free resource.

 

Fast Distance Calculations with CDXDistance2WP

CDXDistance2WP is the fastest distance calculation available in CDXZipStream. It calculates the straight-line ("as the crow flies") distance between two locations as defined by their latitude and longitude coordinates. This function is available in the Bing and higher versions of CDXZipStream. 

CDXDistance2WP is an order of magnitude faster than the standard CDXDistance calculation and several order of magnitude faster than the Bing and Mappoint driving distance formulas.   There are no database calls as in CDXDistance to determine coordinates as they are already predefined.   And there are no delays waiting for Bing Maps or Mappoint to respond.

A question we often get asked is how to calculate determine the closest business or source locations to a long list of customer locations.   Attacking this type of problem with just the driving distance calculations can take many hours to run in a grid of 100K location pairs or more.  Once you geocode all the locations involved you can use the use the CDXDistance2WP function to determine straight line distance.  CDXDistance2WP is very fast and the analysis for a 100K grid should only take a few minutes. You can then determine the closest few locations for each client and then run a driving distance calculation for these top locations.  This dramatically cuts down on the calculation times.

We’ve developed an example template which shows how set up CDXDistance2WP on a simple five by five grid.  Just copy the formulas to new cells to expand it.  You can geocode locations using Bing or Mappoint or if only using zip code use the CDXZipCode formula to determine latitude and longitude.  Once calculated, set all the formulas to values and then sort the various columns/rows to determine the closest locations.  Setting the formulas to values avoids recalculation delays when sorting the lists.

 We’ve also developed a “Geographic Analysis Template" that automates the calculation and sorting process described above and produces a custom output report that can be tailored by a specified radius.  Here is a video on how it works. 

 https://www.youtube.com/watch?v=qcMVMRqvsMg&t=24s

We have a variety of other articles on this blog describing the distance calculations available with CDXZipStream.  Here are some of the most popular ones.

 https://blog.cdxtech.com/post/straight-line-distance-as-an-estimate-for-driving-routes

 https://blog.cdxtech.com/post/Choosing-the-Right-Function-to-Calculate-Distance-in-CDXZipStream

 https://blog.cdxtech.com/post/How-to-Very-Accurately-Filter-Addresses-Based-on-Distance

 https://blog.cdxtech.com/post/Geocode-and-Sort-Locations-by-Distance-from-a-Target-Free-Excel-Template1

https://blog.cdxtech.com/post/cdxzipstream-zip-code-distance-matrix-template