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:
Reverse Geocode Template for Microsoft Excel:
Gecoder in Excel:
Get Latitude and Longitude for Addresses in Excel: