We were recently asked by a customer if it is possible to use CDXZipStream, our Microsoft Excel add-in for address and zip code analysis, to determine whether a zip code is in a rural area.  Here’s our response:

A fairly stringent determination of rural zip codes, which can be done easily using CDXZipStream, is based on whether the zip code is part of a Core Based Statistical Area, or CBSA. CBSA’s are defined by the Office of Management and Budget as urban areas; a metropolitan area contains a core urban area of 50,000 or more population, and a micropolitan area contains an urban core of at least 10,000 (but less than 50,000) population. Each metro or micro area consists of one or more counties and includes the counties containing the core urban area, as well as any adjacent counties that have a high degree of social and economic integration (as measured by commuting to work) with the urban core. Based on the 2010 Census, approximately 95% of the U.S. population lives in either a micropolitan or metropolitan CBSA.   If a zip code is not associated with a CBSA then, it is a good assumption that it is in a non-urban, or rural area. Using the CDXZipCode premium feed (available in CDXZipStream Demographic version and higher), request the data field “CBSA”; if the returned value is "N/A", then the zip code is not associated with any CBSA and you can assume that it is rural.

The attached map shows the current coverage of Core Based Statistical Areas in the U.S. and Puerto Rico:

Source: http://www2.census.gov/geo/maps/metroarea/us_wall/Dec_2009/cbsa_us_1209_large.gif

Using an alternative U.S. Census Bureau definition, rural areas comprise open country and settlements with fewer than 2,500 residents.  All other (urban) areas are of two types—urbanized areas and urban clusters—identical in the criteria used to delineate them but different in size. The Census Bureau defines an urbanized area wherever it finds an urban nucleus of 50,000 or more people. They may or may not contain any individual cities of 50,000 or more.  Urban clusters have a population of at least 2,500 but less than 50,000 persons.  Using these criteria, about 16% of the U.S. population lives in what is considered to be rural areas, based on the results of the 2010 Census.  You can download from the Census website relationship files showing which ZCTA’s (the Census Bureau’s approximation of zip codes) correspond to urban and non-urban (rural) areas.

You may also want to consider population density as a rough measurement of urban/rural characteristics, Population density can be easily calculated by dividing the total population of the zip code by the land area.  Again, using the CDXZipCode Premium feed, request the data fields “Population” and “LandArea” and then use Microsoft Excel to perform the calculation.  Keep in mind, however, that population density provides only a partial picture, since there may be non-residential areas with low population counts that exist in the middle of urban centers.  

In cases where address data is missing zip codes, you can use our Microsoft Excel add-in, CDXZipStream, to quickly and easily get them for you, even for long lists of addresses.  Here’s how:

You will need to use either the CDXZipstream MapPoint or Basic versions, which work in conjunction with Microsoft MapPoint to verify the address location and determine the correct the zip code.  Just right-click on any worksheet cell in Microsoft Excel, and select the CDXZipStream function called CDXLocateMP.  

Address information can be input as a single string (single-line address), or separately as street, city, and state (multi-line address).  In the example, above, we’ve input a single text string as the address.  You can also input the worksheet cell location of the address (e.g. A1), since CDXZipStream automatically recognizes that the value of the worksheet cell is the address string.  Similarly, for a multi-line address, you can input cell locations for each street, city, and state component.

After selecting zip (Postal code) as the returned data, and clicking OK, a formula is placed in the worksheet:

= CDXLocate(7, “501 Cooper Landing Rd, Cherry Hill, NJ”)

Where the “7” parameter indicates that zip code data is being requested.  This cell displays a value of 08002 as the correct zip code.

If you have a long list of addresses that require matching zip codes, make sure you input address data using the worksheet cell locations, so that copying the formula to the rest of the list will result in zip codes being returned for all addresses.  In this case the first formula would be something like this:

= CDXLocate(7, “A1”)

Where A1 is the cell location of the first address.   Similarly, if street, city, and state information are located in cells A1, B1, and C1, then the formula would be:

= CDXLocate(7, “A1”,”B1”,”C1”)

When copying and pasting the first formula (or using Excel’s autofill feature), Excel will automatically adjust the cell references so that the formulas are correct for all rows of the list.

Note that MapPoint does not use additional address data such as suite or apartment numbers in its analysis.  If suite or apartment numbers are part of the address, the address should be formatted so this information is provided after the street number.  For example, "501 Cooper Landing Rd, Apartment 2A, Cherry Hill, NJ" is acceptable.

Since MapPoint is programmatically looking for the best match for each listed address to one in its own database, it’s important to know the quality of the match that was found.  You can do this by using CDXLocateMP to return the best match address found.  Select “best match” as the output, and then use Excel worksheet formulas to compare the result to the original.    

The best match for this address was returned to column C.  (The actual CDXLocateMP formula is shown in the second row).  In column D, a formula was used to check the match with the original address in column A; if a mismatch was found, a value of 1 would be returned; an exact match would return a 0, as shown.  Note that since the zip code was missing in the original address, the found zip code in column B needed to be appended to the original address to perform the check.

You can also use our free geocoding template to quickly determine the type of match found by MapPoint.  (All our templates can be downloaded from our links page.)  Just copy and paste your address list into the template (zip codes are optional), and click on the “Get Coordinates” control button.  The template is designed to return latitude and longitude data for each address, and the type of address match found by MapPoint is also returned.   Here are the possible match types: 

1. Exact - A unique entry was found in MapPoint for this address.

2. Allow Ambiguous-  The first of at least two matching entries was found in MapPoint.

3. Best Match - MapPoint did not find a good match, but this is the best of possible alternatives.

4. Zip Code - MapPoint could not find any matches, so the centroid of the zip code is returned from CDXZipStream

Any address that has a Best Match or Zip Code match type should be double-checked for accuracy.

For addresses in Canada, MapPoint cannot return an entire postal.  There are over 850,000 Canadian postal codes as compared to about 43,000 U.S. zip codes, and only the first three digits of the Canadian postal code (called the FSA, or Forward Sortation Area) are available using the CDXRouteMP function working with MapPoint.  For example,  the CDXLocateMP formula (which includes country data):

=CDXLocateMP(7, “1385 Bank Street, Suite 203, Ottawa, Canada”)

Will return “K1H” as the FSA, although the full six digit code is “K1H 8N4”.  Note that the first letter of an FSA code corresponds to a particular "postal district", which, outside of Quebec and Ontario, covers an entire province or territory.

For more information about the CDXLocateMP function, please also refer to our video tutorial Address Validation in Excel.

There are currently eight versions of CDXZipStream, our Microsoft Excel add-in for zip code, address, and route analysis.  Due to the broad spectrum of functionality of CDXZipStream, we’ve purposely created these versions to allow our customers to pick and choose the one that best fits their needs.  With the release of CDXZipStream 11.0 late last year we’d like to provide in today’s blog an updated review of the functionality of each version, to help you decide which one is right for you.  You can also refer to this reference table for a shorthand comparison.

Note:  We also offer a Canadian postal code database which is compatible with most CDXZipStream functionality.  Please refer to this article for more information about accessing Canadian data with CDXZipstream, or our FAQ page.)

Here are the eight versions and what they can do, along with relevant video tutorials:

FindZip:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)

Reference:  Zip Code Finder in Excel

Lite:  Provides all the basic zip code information of the FindZip Version (basic data by zip code, reverse zip code lookup, and list generation) as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip code, and find the closest zip code to a target zip. 

Reference:  Find Zip Codes in a Radius

MapPoint:  Provides all the basic zip code information of the FindZip Version (basic data by zip code, reverse zip code lookup, and list generation), but also has all the mapping and routing functions provided by Microsoft MapPoint, including creation of radius, location, drive time, and route maps, calculating driving time and distance, and route optimization.  It can also geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between two points of latitude and longitude.  In general, this CDXZipStream version is best suited for those requiring driving distance calculations, route optimization, and/or latitude and longitude determination.  Please note that CDXZipStream MapPoint requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Driving Distance Calculator in Excel

Basic:  Contains all the functionality of Lite and MapPoint versions, including basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  CDXZipStream Basic is primarily used by those who require both extensive zip code functionality as well as driving and routing calculations supplied by MapPoint.

CDXZipStream MapPoint requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Driving Distance Calculator in Excel, Geocoder in Excel

The next four versions also include demographic data.  For a detailed description of the data available in these versions, please refer to data fields information.

Population:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)   Population counts from the 2000 Census and current population estimates are also covered.

Demographics:  Contains basic zip code data, and can also perform reverse zip code lookups and create zip code lists by state (or province if you have also purchased the Canadian database), county (for the U.S. only), and city.  Basic zip code data covers city, county and state name, latitude and longitude, and region (one of four regions in the U.S. as defined by the U.S. Department of Commerce.)   Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.

Reference:  Demographic Data in Excel

Premium:  Contains all the functionality of Lite and MapPoint versions, including basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.  Additional data feeds include demographics by area code, city, county, state, and CBSA (Core Based Statistical Area), and an extensive demographic feeds of social and economic variables from the 2000 and 2010 Census.

This version, when used for driving distance calculations and route optimization, requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Premium ACS:  Contains all the functionality and data of the Premium version, plus demographic data from the American Community Survey.  Covers basic zip code data, the ability to perform reverse zip code lookups and create lists by state, as well as zip code calculation functions:  it can calculate the distance between zip codes, find all zip codes within a radius distance of a central zip, and find the closest zip to a target.  It also includes all mapping and routing functions, including creation of radius, location, drive time, and routing maps, calculating driving time and distance, and route optimization.  It can geocode (find latitude and longitude) for a location, and reverse geocode, as well as calculate the distance between points of latitude and longitude.  Demographic data included are population counts from the 2000 Census and current population estimates, as well as premium zip code data from sources such as the 2010 Census, 2009 Business Census, and the U.S. Postal Service.  Additional data feeds include demographics by area code, city, county, state, and CBSA (Core Based Statistical Area), and an extensive demographic feeds of social and economic variables from the 2000 and 2010 Census.  Finally, 5-year aggregated data from the annual American Community Survey is provided in data feeds by city (place), county, state, and Core Based Statistical Area (CBSA).   Note that the ACS is administered by the U.S. Census Bureau, and replaces the old long form that was phased out after the 2000 Census.  It contains earnings data and other social and economic information that are not currently covered by the 10-year census. 

This version, when used for driving distance calculations and route optimization, requires separate purchase of Microsoft MapPoint, and is compatible with both North American and European versions.

Reference:  Census Records in Microsoft Excel

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.