|
 
Find
us on Facebook and Twitter
|
|
7777 Welcome to the CDXZipStream August
2011 Newsletter
This
month's newsletter highlights the different types
of maps that can be created using versions of
CDXZipStream that work with Microsoft MapPoint,
including a soon-to-be-released drive time map
that outlines the perimeter for a specified
driving time around a zip code. We also
continue our series on how to access CDXZipStream
functionality using Visual Basic for performing
route optimization, and provide a tip for finding
the closest zip code for large data
sets.
There
are two new blog articles for August:
-
Finding the Closest Zip Code
| |
|
Creating
Maps with CDXZipStream
The
CDXZipStream versions that work with Microsoft
MapPoint (MapPoint, Basic, Premium, and Premium
A CS)
all have the capability to create different types
of maps as picture objects in Excel. These
can be invaluable for visualizing a driving route
or depicting a radius area around a location.
We're also about to introduce new
functionality in CDXZipStream that will include
the ability to map the perimeter of a drive time
around a location. For a quick tour of our
current mapping capability, please see our YouTube
video Map It in
Excel.
Radius
Mapping: Use the right-click function
CDXRadius to create a radius map. Just input
the target zip code and radius distance in either
miles or kilometers, and specify "Insert Radius
Map" as the resulting output. (This function
is also available in CDXZipStream
Lite.)
Route
Mapping: With the right-click function CDXRouteMP,
specify the driving parameters (beginning and end
points, route options, etc.) and select "Insert
Route Map". You can create a map for a
simple route with beginning and end, or even for a
more complex multi-stop route that has undergone
optimization. A route map is also
automatically generated whenever you create a Trip
Summary using the same
function.
Location
Mapping: The right-click function
CDXLocateMP is often used for address verification
and geocoding, but can also generate maps of a
specific location and its surroundings. Just
input the location or geographic entity (full
address, county, state, or zip code) and specify
"Insert Location Map".
And
finally, in the next few weeks we expect to
introduce drive time mapping as an option in the
CDXRadius function. In this case you input
the target zip code and radius time in minutes,
and specify "Insert Drive Time Map" as the output.
The resulting map will show a polygon
covering the area containing all those places
that can be reached by road within the specified
time from the
target. |
Route
Optimization Using Visual Basic
Code
This
is part of our continuing series showing how you
can use Visual Basic programming code to grab
CDXZipstream data without entering a custom
function formula into a worksheet cell. This
approach is faster for very large sets of data,
with no delays associated with Excel recalculating
a lot of formulas within a worksheet. Since VB
works so quickly, it is used in all of our
CDXZipStream templates available as free
downloads
from our website. Let's review one more
programming example that provides multiple data
output in the form of an array: route
optimization for multiple stops on a driving
route.
For
optimizing a set of stops using the normal custom
function formula, the worksheet formula would be
something like this, using the CDXRouteMP
function::
=CDXRouteMP(0,8,A1:A10)
In
this case we are requesting the quickest route
(the first parameter, "0'), and the list of
optimized stops or waypoints (the second
parameter, "8") as the output. We also list
the worksheet range of input stops that are
located in cells A1 through
A10.
To
do this easily in Visual Basic, just use the
createobject statement to connect to
CDXZipStream:
Set
oAdd =
CreateObject("CDXZipStreamCF.Connect")
Instead
of using a worksheet range, we need to define a VB
array that contains all of the stops on the route.
We can just loop through the stops located
in the worksheet, like this:
For
N = 1 to 10
Input_array(N)
= Sheets(1).Range("A" & N &
"")
Next
N
Use
the newly defined Input_array to get the optimized
list of stops:
Output_array
= oAdd2.CDXRouteMPWrapper(0, 8,
Input_array))
Then
simply loop through the elements of the returned
array to send the optimized stops back to the
worksheet.
Also
note that this approach can be used in Microsoft
applications beyond Excel, such as Word, Access,
or Sharepoint. If you need additional
programming support to make this happen, please
contact us for a free quote at customsolutions@cdxtech.com.
|
Tip:
Getting the Closest Zip for Large Data
Sets
CDXZipStream
has a built-in function called CDXClosestZip which
identifies which zip code out of a list is the
closest distance (as the crow flies) to a target
zip code For very large lists, we do
recommend an alternative approach (available with
our CDXZipstream MapPoint and Basic versions) that
works in conjunction with Microsoft MapPoint.
In this case, an Excel matrix is set up for
each zip code combination. Using the
function CDXLocateMP, the latitude and longitude
of each zip code location is found (this process
is called geocoding), then the function
CDXDistance2WP uses this data to calculate the
distance between each combination of zip code.
This approach is faster than CDXClosestZip
since no database calls to get the zip code
locations need be made once the geocoding is
completed. It's then very easy to use
Excel's sorting function to find the closest
distance to each target zip. For more
information, you can download a
spreadsheet that applies this approach to a
real-life car dealership location
analysis. |
August
Data Updates
CDXZipStream
data feeds were updated as of September
6. There was an unusually large number
of changes (9,377) to the zip code database this
month due to updated information provided by the
USPS. If you would like to update your
database to reflect these changes, click on the
"License Information and Software Updates" icon on
the CDXZipStream toolbar, and select "Data
Updates" to login to your account. If you've
forgotten your user name or password, please
contact us at support@cdxtech.com.
We
hope you find the information here helpful for you
and your organization. Please contact us
with your feedback and suggestions by replying to
this email.
The
Team at CDX
Technologies
| |
| |
|
In This
Issue
Creating
Maps with
CDXZipStream
Route
Optimization Using Visual Basic
Code
Getting
the Closest Zip for Large Data
Sets |
|
