by Betty Hughes
22. February 2012 07:36
We’re going to review today a special feature of the CDXZipStream Optimized Route template that allows you to send a list of optimized stops to your GPS system, using something called a GPX file. Despite the ominous-sounding acronyms used in the previous sentence, this is really a pretty simple process that can save you lots of time as compared to manually entering locations into your GPS.
First, we need to back up a bit and talk about what CDXZipStream, our Microsoft Excel add-in for address and zip code analysis, does when it performs route optimization. Let’s say you own a business that delivers a product or service to a list of locations that can change on a daily basis, and each morning you need to determine the best or optimized route between these locations in order to save time, money, and fuel. Just list these locations in Microsoft Excel, and CDXZipStream, working in conjunction with Microsoft MapPoint running in the background, can re-order these stops to achieve either the shortest (by distance) or quickest (by time) route. CDXZipStream combines the ease of using Microsoft Excel coupled with the mapping and routing power of MapPoint, and delivers the answer you need typically in just a few seconds.
As previously mentioned, we also offer a pre-formatted and automated Excel template that performs route optimization. The user just inputs their route locations, clicks a button, and the desired output is returned to the worksheet. The template is free, works in Microsoft Excel 2003 and up, and can be evaluated with free trials of CDXZipStream and Microsoft MapPoint. You can download it from our links page, and see it in action in the tutorial Route Optimization with One Click.
The results from this template can also exported to a GPX file and input to your fleet’s GPS system. Just click on the “Export to File” button on the Optimized Route worksheet and then name the file to save it on your computer. GPX stands for “GPX eXchange format” and GPS is “Global Positioning System.” Each GPX file created by the CDXZipStream template will contain the address and latitude/longitude of each stop, in its optimized order. Since GPX is a data format that allows for the exchange of location and route information between different applications and devices, it can be used or converted to a similar format, that can be plugged into your vehicle GPS. Here’s an example of what a GPX file looks like for a route of five locations in New York City:
If you’re familiar with data exchange files you may have noticed that this looks a lot like an XML (Extensible Markup Language) file, and that’s exactly what it is, albeit formatted for the exchange of location-based data such as latitude and longitude. But if this looks like Greek to you, no worries, the file contains all the information you need about your route with no manual modification required by you.
(Note: If desired, you can create a GPX file without going through the route optimization process. Just input your address list directly in the template, in column G of the Optimized Route worksheet, and press “Export to File”. The GPX file will be generated with corresponding latitude and longitude data for each location. The order of the addresses will remain unchanged.)
The next step is dependent upon the manufacturer of your GPS. To import the GPX file into a Garmin or Magellan GPS, these manufacturers provide free communications software that can be downloaded from their websites at the following links:
Garmin Communicator Plug-In
Magellan Communicator for PC
For other manufacturers please refer to the support section of their website for further information.
As an excellent general resource for the use of GPX files, we also recommend the website topografix.com.
by Betty Hughes
20. February 2012 23:38
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
by Betty Hughes
31. January 2012 06:29
CDXZipStream, our Excel add-in for address, zip code, and route analysis, has a licensing agreement that is significantly different (in a good way!) from most other software. Understanding that CDXZipStream contains a number of functions for route planning and optimization, it makes sense to allow our users to install it on multiple computers. For example, you can plan and optimize a route on your desktop, then follow the directions for the optimized route on a laptop in your car. For very long calculations, it is also helpful to use a secondary desktop or laptop so your main computer is not tied up. Since the CDXZipStream license is provided per user, you won’t have to buy multiple licenses to do this; each CDXZipStream license comes with three key codes, allowing you to install it on three separate computers.
To set up CDXZipStream on a new computer simply download and install the CDXZipStream demo on the new computer. When installation is complete and Microsoft Excel opens, press the third button on the CDXZipStream toolbar named "License Information and Software Updates". (If you are using Excel 2007 or 2010, the CDXZipStream toolbar is located under the "Add-Ins" menu.) Then press the "Buy Now" button followed by pressing "Generate KeyCode" and log into our web site using the username and password you received with your original purchase. If you did not retain your original purchase information please contact us at support@cdxtech.com
A web page allowing you to generate a new keycode should appear with your new Computer ID automatically entered. This Computer ID is unique for each PC and your registration code is based on it. Then press "Next" to generate your new registration key.
Copy your new keycode from the box that appears and then press the "x" in the upper right corner and minimize your browser. Then paste the copied code into the "Keycode" section of the "CDXZipStream Purchase and Registration" box and press "OK". (This is the box where you pressed the "Generate Keycode" button.) CDXZipStream will now be fully activated on your computer.
Your online account can also be used to purchase data updates or upgrade your product to a more powerful version. A history of all data purchases is maintained in the "Download Data Files" section.
Our license does not permit sharing with other users on additional machines. We do require that multiple users purchase multiple licenses. Each CDXZipStream license will be registered under a person’s name, which can be displayed by clicking on the “License Information and Software Updates” button on the main CDXZipStream toolbar. Also shown is the number of licenses associated with the name. If the name needs to be transferred, such as in cases where an employee has left an organization, please contact us at support@ cdxtech.com. We also offer generous volume discounts of up to 20% for 10 licenses; please contact us for details if you need more than 10 licenses.
by Betty Hughes
24. January 2012 18:49
CDXZipStream, our Microsoft Excel add-in for zip code and address analysis, has a number of built-in functions that perform area calculations around a given geographic location. These can be invaluable tools for designating sales and service territories, finding customers for a brick-and-mortar store, designing delivery routes, and more. Here’s a quick review of this capability:
Generate a zip code radius list: The CDXZipStream function CDXRadius can quickly find zip codes within a specified radius of a central zip code. This is based on straight-line distance calculations between the centroid points of the target zip code and all nearby zip codes. To use CDXRadius, right-click on any cell in your Excel worksheet, and from the menu click on CDXZipStream Functions, then Insert CDXRadius Function. The input box will look like this:
After specifying the target zip and radius distance, you can ask for the resulting list of zip codes either as an Excel array formula (see here for more information on Excel arrays), or as a text string within a single worksheet cell. The output will also include distance data from the target zip, with the data separated by vertical bars when listed as a text string:
Find locations within a zip code radius: Do you have a long list of addresses or zip codes, and want to determine if they fall within a specified radius of a store or other location? It’s very easy to do this by cross-checking your address list against the list generated by CDXRadius. First, run CDXRadius with the desired target zip code and radius distance, making sure to specify the output as an array formula. Then for each location in your original list, use the Excel LOOKUP function to see if the address zip code exists in the array generated by CDXRadius. A step-by-step tutorial of this method is shown in the YouTube video Find Zip Codes in a Radius Using Excel.
As an alternative, CDX Technologies also offers a free, downloadable Excel template that automatically identifies locations around a zip code. This template is especially well-suited for use with very long address lists, since the template code accesses CDXZipStream functions directly (versus using Excel worksheet functions) which increases calculational speed. Please refer to our links resource for CDXZipStream template downloads.
Find exact locations within a radius area: Sometimes zip codes are just not accurate enough, so when you need to use exact locations for a radius analysis, use a combination of two CDXZipStream functions. First use CDXLocateMP to find the latitude and longitude of each address, then CDXDistance2WP to calculate the straight-line distance between each latitude/longitude point and the central point of interest. The Excel autofilter feature can then quickly find the locations that meet the radius distance criteria. This multi-step process is explained in detail in our blog post How to Very Accurately Filter Adresses Based on Distance.
Map an Area: You can also use the CDXRadius function to map a radius around a zip code. Specify the target zip code and radius distance, and request the output as a radius map.
Need a drive time map that delineates the area within a specific driving time of a location? Specify the target zip and drive time, and request the output as a drive time map (using the CDXRadius function again). The drive time area is shown in red and is typically a polygon, reflecting variations in road conditions and driving speeds:
Geographic area analysis can be a critical component of activities ranging from target marketing to product delivery. Remember CDXZipStream when you need a fast and economical area analysis that meets your business needs.
by Betty Hughes
3. January 2012 05:57
The main CDXZipStream interface, developed from our patent, allows you to specify exactly where the demographic data you request is placed in your Excel worksheet. According to our client feedback, it does a very good job of managing the hundreds of variables available from the CDXZipStream database, while allowing the user to control how the data is returned so it won’t overwrite or otherwise interfere with existing worksheet entries. Let’s take a look at some of the special features of this interface:
Adding Fields
Once you’ve selected the desired data feed (such as CDXZipCode, CDXAreaCode, etc.), you can use the “Data Fields” area to pick and choose which fields from the feed you would like to return to the worksheet. Especially since some data feeds can contain over a hundred fields, remember to use the CTRL and SHIFT keys to easily select the right combination of fields:
- Hold down the SHIFT key while using the cursor to select the first and last fields of a block.
- Hold down the CTRL key while using the cursor to select individual, non-contiguous fields.
- Hold down the CTRL key while using the cursor to de-select individual fields within an already highlighted block of fields.
You can also add blank fields that will skip over an area of the worksheet if you would like to either retain the data already there, or to make room for areas you will need later for calculations involving the returned demographic data. Just click on the “Add Blank” button to add one or more blanks – it will show up as “ ------“ in the selected fields list.
You can also edit the selected fields list if necessary. As discussed above, use the SHIFT and CTRL keys with the cursor to highlight fields in this list, and then click on the “Remove” button to take them off the list. You can also change the order of the fields here using the up and down buttons on the right.
Keep in mind that after the data is returned to your worksheet, CDXZipStream will “remember” the last selected fields list and all associated settings during the current session of Excel - you won’t have to re-specify the selected fields unless you want to make a change.
Selecting Settings
The main CDXZipStream interface also has a “Settings” button that can be used to specify more general ways to insert data. These settings are also accessible from the “Applications Settings” icon on the main CDXZipStream toolbar. (Please see Using Application Settings on the Main CDXZipStream Toolbar for more information.)
The two settings applicable to our discussion here are the
- Index List: This allows for returned data to be imported as rows or columns, depending upon the configuration of the index list. The “index” is the input data you provide, such as zip codes, area codes, county, state, etc. For example, if you have indices such as zip codes listed in a single column in your worksheet, specify the indices are listed in a column and the returned data will also be returned as columns. Conversely, zip codes listed across a single row will require returned data in rows.
- Data Fields Location: In cases where there is already data next to the index list, you can specify here whether you prefer to overwrite the existing data and place the returned data immediately next to the index list, or let CDXZipStream find a blank area of the worksheet where the returned data will not overwrite any pre-existing data. Regardless of your choice here, there will be no impact on the selections made previously in the selected fields list; these selections, including any blank fields, will be retained. Selecting the second option here, where data is not overwritten, is helpful when you want to retain a historical record of the returned data.
Please also refer to our quickstart video for a short demonstration of how to use the CDXZipStream interface.
by Betty Hughes
20. December 2011 06:46
The CDXZipStream toolbar looks a little different now with the addition of the Application Settings icon that’s located fourth from the left, as shown below:
Click on the icon and you’ll see the dialog box below:
The “CDXZipStream Settings” control shown here provides access to settings associated with the main CDXZipStream dialog, used for obtaining demographic information. These are also still accessible by choosing “Settings” on the main dialog. The “Route Settings” control accesses settings for the CDXRouteMP function, for performing route calculations and optimization. Route settings are also accessible from the CDXRouteMP dialog. Note that although these controls both duplicate capabilities available outside the CDXZipStream toolbar, a number of our clients had requested an additional, centrally located approach for modifying these settings, and this is the end result.
The “MapPoint Version” setting is a new feature that supports the ability to toggle between versions of MapPoint. If you have installed both MapPoint North America and Europe on your computer, you can now choose which version will be applied when performing worksheet calculations with CDXZipStream functions CDXRouteMP and CDXLocateMP. Choosing “Default” will default to the last version used by CDXZipStream. If you have only one version of MapPoint installed, you don’t need to set this; the program automatically will look for the current version. If you don’t use MapPoint, this feature will not be visible.
Keep in mind that if you're performing an analysis for locations in Europe, and the MapPoint setting is North America, CDXZipStream will not work. If you find that "Data Item Not Found" is being returned consistently for the locations in your worksheet, please check that the correct MapPoint version is selected.
by Betty Hughes
12. December 2011 05:12
Our Microsoft Excel add-in CDXZipStream can now create drive time maps working with Microsoft MapPoint in the back ground. Drive time maps delineate areas within a specific driving time of a location, and can be very useful in planning delivery routes and customer service and sales zones. CDXZipStream makes creating drive time maps a snap, inserting the maps into Microsoft Excel for easy access and analysis.
To create a drive time map, right-click on any cell in an Excel worksheet, click on “CDXZipStream Functions”, and select “Insert CDXRadius Function”. Input a target zip code (which will act as the center of the map), the desired drive time (in minutes), and specify the resulting output as “Insert Drive Time Map.” For a 20 minute drive time around the zip code 21201 in Baltimore, Maryland, the map looks like this:
The drive time area is shown in red and is typically a polygon, reflecting variations in road conditions and driving speeds. For instance, the driving area will tend to be further from the center point along main highways where driving speeds are highest.
Drive time mapping is just one more mapping feature available with CDXZipStream, which also works with Microsoft MapPoint to create location, route, and zip code radius maps in Excel. For a short tutorial on these other mapping options, please see the YouTube video “Map It in Excel”.
by Betty Hughes
30. November 2011 01:21
In a previous post, Zip Code Demographics from the 2010 Census, we talked about how there are now new several updated fields in CDXZipStream that incorporate data from the last census. These fields cover basic areas such as population counts, median age, household size, and race. We’ve also now just introduced a more extensive, dedicated data feed that covers 115 fields from the 2010 Census. This feed is listed as “CDXCensus2010” under the “Select Data Feed” dropdown list, available in the Premium and Premium ACS version 11 of CDXZipStream. You can review a listing of all CDXZipstream data feeds and their fields, including CDXCensus 2010, from the file CDXZipStream Data Fields and Definitions, or refer to the list at the bottom of this post.
If you already own CDXZipStream Premium or Premium ACS, click on the "License Information and Software Updates" button on the main CDXZipStream toolbar (third button from the left) and select "Date Updates" to purchase the new feed through your account. Data updates for premium versions are $29.95. If you own a version of CDXZipStream that does not include this feed and would like to upgrade, please see our pricing list for more information; you can then upgrade if desired through your online account. (Please contact support@cdxtech.com if you need your account user name or password information.) Of course, if you are buying CDXZipStream for the first time, please see our "Buy Now" webpage to purchase CDXZipStream Premium or Premium ACS. New purchases already include this feed.
CDXCensus2010 fields:
- ZipCode
- Age median
- Age median male
- Age median female
- Age under 5
- Age male under 5
- Age female under 5
- Age 5 - 9
- Age male 5 - 9
- Age female 5 - 9
- Age 10 - 14
- Age male 10 - 14
- Age female 10 - 14
- Age 15 - 19
- Age male 15 - 19
- Age female 15 - 19
- Age 20 - 24
- Age male 20 - 24
- Age female 20 - 24
- Age 25 - 29
- Age male 25 - 29
- Age female 25 - 29
- Age 30 - 34
- Age male 30 - 34
- Age female 30 - 34
- Age 35 - 39
- Age male 35 - 39
- Age female 35 - 39
- Age 40 - 44
- Age male 40 - 44
- Age female 40 - 44
- Age 45 - 49
- Age male 45 - 49
- Age female 45 - 49
- Age 50 - 54
- Age male 50 - 54
- Age female 50 - 54
- Age 55 - 59
- Age male 55 - 59
- Age female 55 - 59
- Age 60 - 64
- Age male 60 - 64
- Age female 60 - 64
- Age 65 - 69
- Age male 65 - 69
- Age female 65 - 69
- Age 70 - 74
- Age male 70 - 74
- Age female 70 - 74
- Age 75 - 79
- Age male 75 - 79
- Age female 75 - 79
- Age 80 - 84
- Age male 80 - 84
- Age female 80 - 84
- Age 85 - 89
- Age male 85 - 89
- Age female 85 - 89
- Age 90+
- Age male 90+
- Age female 90+
- Households total
- Households family
- Households nonfamily
- Households average size
- Households 1-person
- Households 2-person
- Households 3-person
- Households 4-person
- Households 5-person
- Households 6-person
- Households 7+ person
- Housing units
- Housing units owned with mortage
- House units owned free and clear
- Housing units renter-occupied
- Housing units owner-occupied
- Housing units owner-occupied 1-person
- Housing units owner-occupied 2-person
- Housing units owner-occupied 3-person
- Housing units owner-occupied 4-person
- Housing units owner-occupied 5-person
- Housing units owner-occupied 6-person
- Housing units owner-occupied 7+ person
- Housing units renter-occupied 1-person
- Housing units renter-occupied 2-person
- Housing units renter-occupied 3-person
- Housing units renter-occupied 4-person
- Housing units renter-occupied 5-person
- Housing units renter-occupied 6-person
- Housing units renter-occupied 7+ person
- Housing units owner-occupied 15 - 24 years
- Housing units owner-occupied 25 - 34 years
- Housing units owner-occupied 35 - 44 years
- Housing units owner-occupied 45 - 54 years
- Housing units owner-occupied 55 - 64 years
- Housing units owner-occupied 65+ years
- Housing units renter-occupied 15 - 24 years
- Housing units renter-occupied 25 - 34 years
- Housing units renter-occupied 35 - 44 years
- Housing units renter-occupied 45 - 54 years
- Housing units renter-occupied 55 - 64 years
- Housing units renter-occupied 65+ years
- Population
- Population male
- Population female
- Population Hispanic
- Population not Hispanic
- Race White
- Race Black or African American
- Race American Indian and Alaska Native
- Race Asian
- Race Native Hawaiian and other Pacific Islander
- Race some other race
- Race two or more races
by Betty Hughes
29. November 2011 06:24
The CDXZipStream function CDXRadius can easily provide a list of zip codes around a target zip code, but there are often situations where a simple radius area is not enough. Particularly when planning for geographic coverage areas for sales reps or for sales or marketing campaigns, a more complex analysis is required. In this post we’ll cover cases where two radius calculations need to be performed.
Case 1: Include only the overlap of two radius areas
Need to focus a marketing campaign to attract customers from a competing store? In a situation where it’s important to concentrate advertising dollars in an overlapping geographic market, here’s how to do a radius analysis using CDXZipStream in conjunction with the Excel LOOKUP function.
Let’s say we have two competing stores located in zip codes 19026 and 19041. If we locate the zip codes within a 5 mile radius of each, the result (including distance in miles from the target) is:
Now we just use the Excel function VLOOKUP to determine which zip codes in the second list exist in the first list. The first equation using VLOOKUP would look something like this:
=VLOOKUP(D3,$A$3:$B$32,2,FALSE)
Where …
D3 is the cell address of the first zip code in the second list (zip code 19041)
$A$3:$B$32 is the range in the first list that is being searched
2 is the second column in the search range; the value from that column will be returned if the search is successful
False indicates an exact match must be found
Now we just copy this formula along all the zip codes in the second list, and the result is:
If the zip code exists in both lists, the VLOOKUP search will result in a numeric value; if not, #N/A is returned. Now just use Excel’s sort or autofilter functions to identify all zip codes that did not return #N/A, and you’ve just found the overlapping zip codes within a five mile radius of each store.
Case 2: Exclude one radius from another
Let’s say you need to add technical service personnel but want to avoid already covered areas. Using the example from above, if you were adding personnel to zip code 19041 but wanted to exclude those zip codes in the 19026 area, simply do the same analysis but in this case use Excel’s sort or autofilter functions to identify all zip codes that did return #N/A, as those are the ones that were not found in the 19026 area.
If you'd like more information, please follow these links:
- Find Zip Codes in a Radius Using Excel (YouTube tutorial)
- Using Excel’s sorting feature in Excel 2003 or Excel 2007-2010
- Using Excel’s autofilter feature in Excel 2003 or Excel 2007-2010
by Betty Hughes
22. November 2011 07:12
Would you like to use CDXZipStream across a variety of applications, such as Microsoft Access, Word, Powerpoint, or Sharepoint? With a little programming knowledge it’s actually quite easy to use Visual Basic to make a “data call” to CDXZipStream, get the data you need and put it in the application of choice. This allows you to grab CDXZipstream data without entering a custom function formula into a worksheet cell. There are also other advantages in that this approach can be faster for very large sets of data, and since the values are entered directly into the cells, there are no delays associated with Excel or any other application recalculating a lot of formulas. (That’s one of the reasons we use this approach in our series of one-click templates.)
Let’s see how this works with a very simple example using the CDXZipStream function CDXZipCode. This right-click function in Excel takes a zip code and returns a host of associated data for the zip, such as city, population, elevation. latitude, longitude, etc. (The data fields vary depending upon the CDXZipStream version.) The worksheet custom function formula to get the city for the zip code 07869 would be:
= CDXZipCode ("07869", "City")
But we can also do the same thing using just a few lines of Visual Basic code. First, in a Visual Basic module we use the createobject statement to connect to CDXZipStream:
Set oAdd = CreateObject("CDXZipStreamCF.Connect")
Then simply ask for the data:
City = oAdd.CDXZipCode("07869", "City")
The code can be generalized to obtain data for a long list of zip codes. In Microsoft Excel, for example, just loop through the worksheet range containing the list of zips:
For N = 1 to 100
Zip_code = Sheets(1).Range(“A” & N & “”) ‘Get each zip code from the worksheet list
City = oAdd.CDXZipCode( Zip_code, "City")
Sheets(1).Range(“B” & N & “”) = City ‘Place the returned data in the worksheet next to its corresponding zip code
Next I
Let’s take a look at another programming example that can do something a little more complex: performing a radius analysis for addresses based on their geocoded (latitude and longitude) locations.
(Note: If you'd like to do this analysis without using code, see our blog article How to Very Accurately Filter Addresses Based on Distance.)
To get latitude and longitude for an address, you can use the worksheet function CDXLocateMP with the following custom function formulas:
latitude = CDXLocateMP(1,"123 Main Street","Springfield","OH","45501")
longitude = CDXLocateMP(2,"123 Main Street","Springfield","OH","45501")
To do this in Visual Basic, again use the createobject statement to connect to CDXZipStream:
Set oAdd = CreateObject("CDXZipStreamCF.Connect")
and then get the data:
latitude = oAdd.CDXLocateMP(1, Address, City, State, PostalCode)
longitude = oAdd.CDXLocateMP(2, Address, City, State, PostalCode)
You can design the code to loop through all the addresses, assigning new values for the variables Address, City, State, and PostalCode for each data set. Then use the returned latitude and longitude data to calculate the distance to a target address that has also been geocoded, using the function CDXDistance2WP:
distance = oAdd.CDXDistance2WP(latitude1,longitude1,latitude2,longitude2)
After looping through all the address pairs, just sort or filter the distance data to find the ones that are closest to the target location. This is a very fast calculation, and is much more accurate than typical zip code radius calculations that use the centroid of the zip code area.
In our last example, we will perform route optimization for a set of driving stops, which will illustrate how to deal with data arrays for both the input and output of a CDXZipStream function. Using the normal custom function formula in Excel, the worksheet formula would be something like this, using the CDXRouteMP:
=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 on the route (the second parameter, "8") as the output. We also list the worksheet range of input destinations that are located in cells A1 through A10.
In Visual Basic, start with 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 destinations on the route. We can just loop through 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 = oAdd.CDXRouteMPWrapper(0, 8, Input_array))
Then simply loop through the elements of the returned array to send the optimized stops back to the application.
For more information about using Visual Basic, check out the Microsoft Visual Basic Developer Center.
|