Geographic Access Analysis Template Update

Do you have the need to identify company locations or resources that are closest to a long list of customers?  We’ve talked about our Geographic Access Analysis template as a solution to this in prior blog articles and videos such as the following:

 

The template initially produces a straight line distance report so it can rapidly sort and compare locations, tens of thousands at a time.   This latest release now includes a driving distance report so that the top locations received can be sorted by driving distance as well.  Distance can be output in kilometers as well as miles and we’ve made improvements in calculation times.   We also now support Maptitude data as well Bing and Mappoint to reduce the cost when analyzing long lists of locations.

 The Geographic Access Analysis template is now on sale at $99.95 and can be purchased in your online cdxtech.com account in the “Buy Data Update” menu. This includes support and access to the latest template for 12 months. A demo of the template is also now available by contacting sales@cdxtech.com.

How to Install the CDXGeodata Excel Add-in

Microsoft Excel add-ins with the extension (.xlam) do not install open directly in Excel as do standard spreadsheets (.xlsx) files.   Since they will run each time Excel starts you will need to install the add-in using menu commands in Excel as described in the procedure which follows below.  These instructions apply to Excel 2010 and latest versions of Excel. While this example is for CDXGeodata, it can be used to install other add-ins as well.

The first step is to download the CDXGeodata add-in which can be found at the following link.

https://www.cdxtech.com/cdxgeodata/excel-addin/

Make sure that you remember the location where the file is downloaded. 

If you are using the Mac all you need to do is click on the Tools – Add-in menu in Excel. Browse to the file and then press OK to load the add-in.  The CDXGeodata file should now be active.

If you are using Windows you will need to “Unblock” the file.  This step is not necessary if you are using Excel for the Mac.

In Windows to unblock a file use the File Explorer to browse to the file Location.  Then right-click on the file and select “Properties” in the menu that appears.   If you see “Unblock” in the lower right as shown in the image below click on it and then press “OK” and close the dialog.

For Windows 7 systems the “Unblock” option will be presents as a button rather than a check box.

We recommend copying this file to a trusted location that Excel recognizes and then installing the file from that location.  You can determine this by clicking on “Trust Center Settings” and then “Trusted Locations” in the Excel File-Options-Trust Center menu.  Placing the file in the XLSTART folder suggested for “Excel Startup” in this list should install the add-in when Excel restarts.

If the add-in is still not visible after restarting Excel or if the file is in another location you can load the add-in manual using the “File-Options-Add-ins” menu in Excel.  At the bottom of the dialog you will see a “Manage” function as shown in the picture below.

 

Make sure that the dialog is set to manage “Excel Add-ins” and press “Go”.   In the dialog that appears press “Browse”.

 

 

The screen shown below will appear.  Switch to the directory where you have placed the unblocked “CDXGeodata” addin and press “OK”.

 

 

The CDXGeodata add-in will be added to your Excel add-in list as shown below.  Click OK to close the dialog.

 

 

The Geodata add-in is now successfully installed.  You should now see the CDXGeodata menu in the top menu of Excel and have access to the right click functions and formulas.

CDXZipStream Templates Updated to Support Maptitude.

We updated all CDXZipStream templates that involve geocoding and routing to include Caliper Maptitude support. In addition to supporting Bing and Mappoint a third option is now available for Maptitude.  Just select Maptitude from the data source dropdown to use it as shown below.

Adding Caliper Maptitude allows you to make an unlimited number of requests for a one-time cost of a license.  Just copy in your address data and press a button to calculate.  There is no need to learn the details about CDXZipStream or its formulas.

The templates updated include.

  1. Geocoding Template
  2. Reverse Geocoding Template
  3. Driving Calculations Template
  4. Driving Calculations Matrix Template
  5. Census Tract FIPs Codes
  6. Route Optimization Template
  7. Route Optimization with Geocodes Template
  8. Geocode and Sort By Distance Template

They are available to both registered versions of CDXZipStream and demo users.  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 as described in prior blog articles. These applications are available for download at the following link.

The templates are a more efficient way to deal with calculating long lists. This is especially important when dealing with routing as this can involve significant calculation times. 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.    

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. Bing enterprise licenses which can cost .5 to 2.5 cents per address request depending upon the quantity. 

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.