Before one can position an address (a street address or a town itself) on a map, it must be converted to geographic coordinates (latitude and longitude). Then, that latitude and longitude is mapped to a particular location on the map. Figure 1 shows several thousand U.S. cities and towns on a Mercator projection map. Well, it’s actually an Excel XY Scatter chart made to look like a map of the U.S. It plots the crime rate for a specific crime for a specific year but that’s not the focus of this note. Here we focus on geocoding an address using Excel and VBA and plotting the resulting geographic coordinates (latitude and longitude) onto a map.
Figure 1 This is a XY Scatter chart!
For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_visualization/10a%20Dashboards-Geocoding.shtml
Hi Tushar, thanks this is a useful technique. Any chance you could post a copy of the file please?
Tushar,
This is fascinating! Mapping techniques, as well as an excellent use of Slicers to segment the data!
Patrick
Thanks, Andi and Patrick.
Andi: The file is very large and not necessarily easy to understand. It contains the raw data from the Azure data marketplace, data from other web sources, PowerPivot analysis of the data, PivotTable(s) from the analyzed PowerPivot data, post-PT analysis to make the data amenable for plotting, and several different visualizations at different granularity.
I will try and make the Geocoding result available in a separate file. Of course, the code can be assembled quite easily from my post since I’ve shared all the code required to get the Geo-coordinates.
Patrick: I’ll put up the next part — converting the Geo-coordinates to XY coordinates — in a day or two. But, I’m afraid — very afraid — to interrupt DK’s posts on college football stats. He might just bite my head off for coming between him and his CFBS. {vbg}
This is excellent, thanks!
I’m trying to use your “code”. and i have an error here:
Static RE As RegExp: If RE Is Nothing Then Set RE = New RegExp
The error is “compile Error: user-defined type not defined”
thanks a lot.