Geocode right from Excel

Esin S sends along this clever script that uses Yahoo’s REST geocoding service to geocode right in Excel (replace appid=XXXX with your own appID):

Function gCode(Street As String, city As String, state As String, zip As String) As String

Dim xm, xm2 As IXMLDOMNodeList
Dim init, ConnectURL As String
Dim temp As IXMLDOMNode
Dim doc As MSXML2.IXMLDOMElement
Dim l As String

init = “
Street = Replace(Street, ” “, “+”)
state = Replace(state, ” “, “+”)
city = Replace(city, ” “, “+”)
init = init & “street=” & Street & “&city=” & city & “&state=” & state & “&zip=” & zip

Set XMLHTTP = CreateObject(“Msxml2.XMLHTTP.4.0”)
Set XMLDOC = CreateObject(“Msxml2.DOMDocument.4.0”)
strUrl = init
XMLHTTP.Open “GET”, strUrl, False
If XMLHTTP.Status = 200 Then XMLDOC.loadXML (XMLHTTP.responseXML.XML)
Set doc = XMLDOC.documentElement
l = XMLDOC.childNodes(1).childNodes(0).Attributes(0).nodeValue
gCode = l
End Function

for other elements place this
where X =
0 = Latitude
1= Longitude
2 = address
3 = city
4 = state
5 = zip
6 = country

This is great because you will have access to the “precision” field which is not available to However, using the REST interface you will be subject to the 5,000 per IP address limit. Assuming you don’t need to geocode more than that, your styling!

Thanks Esin!

UPDATE: Esin points out “Phillip, forgot to mention. Whoever uses the script must include the XML library (Tools -> References) in Visual Basic: “Microsoft XML, v X.0″ where X is the latest version number that user has on their PC.”

Now with Worldwide Satellite Imagery

I had some time to incorporate the latest features from Yahoo’s version 3 of their AJAX API.

The main feature is international satellite imagery, this can be enabled by selecting “SAT” from the menu on the left. There is also an option on the batch geocoder for defaulting the view to either Street, Satellite, or Hybrid. This will be good for saved maps especially.

Version 3 seems to be quite a bit snappier performance wise as well. Let me know what you all think of the new features.

About Data Security and Privacy

I get quite a few questions related to how secure and private data ran through the bulk geocoder will be kept. The quick answer is: Your data never really leaves your computer.

To explain, here’s what happens when you use You paste your data into the field on the screen and validate it. You then select which field is which and select what map options you want and so on. So far so good, you haven’t even accessed the internet since you loaded the page (you can prove this on your own by unplugging your network cable after loading the page.)

The step of actually running the geocoder (and drawing the map) is the only step that actually interacts with the internet. However the only data that is actually transmitted is your address data, and it is not attached to any other information you may have included. So if someone out there happened to be interested enough to sniff the HTTP traffic, all they would see is a bunch of addresses, with no contextual information to place them in.

Even the process of drawing the map keeps your data a secret. The only request that goes to the internet is to Yahoo to download the few map tiles that surround your geocoded points. So if our persistent GIS data hacker is still listening, all he sees is a few tiles fly by for Paris, Texas or Sacramento, California… Or wherever. Point is even when your little map points show up on the map, your data is still kept local to your computer. The entire process is just done in JavaScript client side in your browser.

Now, say you choose to use the “Save Map to Web Page” feature… Well this will actually send your data to our server where they will be saved. So if you just can’t have your data go over the Internet, stay away from this option. “Download to Google Earth” sends the data as well, but it is never saved, just put into a KML which gets sent back to you and then destroyed.

A bit more on “Save Map to Web Page,” doing this will not automatically publish your information to the Internet so to speak. It will be available to anyone who knows the URL, but guessing the URL is impossible. So unless you choose to share it with anyone, it will remain a secret. You can use it in this way to create maps that are of personal interest to you, but no one

Hope that clears things up, if you have any questions please feel free to use the comments feature. If your one of those secretive types, you can even post anonymously.