Reading a post about how geolocation can be used as electronic evidence, a task that I recently had the good fortune to start came to my mind, perhaps not very relevant, but quite attractive, since, even if in a second phase it was somewhat automatic, it is always nice to do work where you end up with new ideas and/or knowledge.
The objective of the task was to obtain from the addresses of an Excel document (complete addresses, names of countries, communities, provinces or municipalities…) their coordinates on the map. This transformation is known as Geolocation and although at first you might think that it would be something complicated or expensive to obtain, the truth is that it is quite fast and effective results.
All this thanks to the Google API for Geolocation, with which we get that, making a query in which we send a URL with the data of the address we want, we get an XML with all the information of that location, including what we needed: latitude and longitude.
If we click on the following link we will see that we obtain the geolocation of the office where I am 🙂
http://maps.googleapis.com/maps/api/geocode/xml?address=Glorieta%20Fernando%20Qui%C3%B1ones&sensor=false
Next, I am going to explain the steps that we must take to obtain our document with all the necessary coordinates.
To do this, first indicate that we will use the 2010 version of Excel.
We will need to have the “Scheduler” tab visible since this is where we are going to start the geolocation process.
To enable this tab, go to the File menu, Options.
Click on “Customize ribbon”, select “Main tabs” in the drop-down menu under the heading of the same name, select the “Scheduler” checkbox and click on the “OK” button.
Once these steps have been completed, we can see the Scheduler option as another tab.
We now need to run the VisualBasic command in Excel to create our function.
Therefore we click on the icon that gives us this option:
A new screen opens.
Select “Insert” and then “Module“.
And we copy the following code in the module that has been created.
On the internet there are many variants of code to get what we want. I used this one and I can say that it worked quite well.
Function GoogleGeocode(address As String) As String
Dim strAddress As String
Dim strQuery As String
Dim strLatitude As String
Dim strLongitude As String
strAddress = URLEncode(address)
'Assemble the query string
strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
strQuery = strQuery & "address=" & strAddress
strQuery = strQuery & "&sensor=false"
'define XML and HTTP components
Dim googleResult As New MSXML2.DOMDocument
Dim googleService As New MSXML2.XMLHTTP
Dim oNodes As MSXML2.IXMLDOMNodeList
Dim oNode As MSXML2.IXMLDOMNode
'create HTTP request to query URL - make sure to have
'that last "False" there for synchronous operation
googleService.Open "GET", strQuery, False
googleService.send
googleResult.LoadXML (googleService.responseText)
Set oNodes = googleResult.getElementsByTagName("geometry")
If oNodes.Length = 1 Then
For Each oNode In oNodes
strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
GoogleGeocode = strLatitude & "," & strLongitude
Next oNode
Else
GoogleGeocode = "Not Found (try again, you may have done too many too fast)"
End If
End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
Dim StringLen As Long: StringLen = Len(StringVal)
If StringLen>0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String
If SpaceAsPlus Then Space = "+" Else Space = "%20"
For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function
The code makes use of the Microsoft XML library so we will have to add a reference to it. To do this we click on the “Tools” menu option and select “References”.
A list of checkboxes will appear. We need to find “Microsoft XML v6.0” in the list and check that it works. Click “OK“.
Save the document as “Excel workbook enabled for macros”.
If we have done all the steps correctly we should be able to use the newly created functions as we would use the default Excel loaded functions.
That is, by standing in any cell and typing the following:
=GoogleGeocode(A646)
The code above serves as an example.
Of course, in this geolocation process I encountered some problems, some perfectly avoidable and others with which we can do absolutely nothing, at least as far as I know. But that will be a matter for another day, since the post is getting a little long already :).