The costless way to geocoding addresses in Excel – part 3, bulk data geocoding with Nominatim and other geocoding tools
The 3rd part of this vast article about geocoding is slated for discussion about another free geocoding tool for MS Excel, namely the OpenStreetMap Nominatim geocoding and other remaining tools, which could be worth attention.
- NOMINATIM GEOCODING TOOL
The usage of this small tool is very simple, even simpler than Bing Maps API geocoding, however, it requires a bit of knowledge about VBA Excel.
The situation looks analogically to the Bing Excel geocoding tool, which appears separately in the file, ready for quick work. The file is available here, along with the whole tutorial. Because everything has been explained roughly there likewise in the Bing Geocoding case I will go straight to the option, in which we can launch this tool in our own workbook. I would like just to point, out that the file includes also the Google API geocoding, which is on the top. It’s not for free unlike the Nominatim tool underneath (Pic. 1), which we can use.
Pic. 1 The Geocoding tool for Excel with Google API option on the top and Nominatim option underneath (marked red).
Unlike the Bing geocoding tool, the Nominatim is in my opinion not comfortable enough to use externally. However, the tool is a perfect fit for our own worksheet, because it’s basically confined to one formula. The same as previously, VBA Excel knowledge is required to make this tool run. I am going to show you shortly how to accomplish it.
Firstly we must open the Geocode file and launch the VBA console (Pic. 2), from where you will enter the module with functions (Pic. 3).
Pic. 2 Opening VBA Excel in our Nominatim Geocode file.
Pic. 3 Nominatim geocoding VBA function
Because the Geocode file is tailored also for geocoding in Google, you will encounter the Google API geocoding functions on the top. Just move your slider down in order to see the Nominatim functions.
The second step is copying this code into our workbook. You don’t need to drag or export a whole module, as shown in the previous part of this article. You can just select the Nominatim function, marked red above (Pic. 3), and copy it into your own workbook creating a new module for it. The image below (Pic. 4) shows all these steps:
Pic. 4 The steps show copying the Nominatim geocoding VBA function into our own worksheet.
- Or own a worksheet with examples of location.
- Creating (Inserting) a new module in the VBA console.
- The new module (default Module1) has been created, you can see it now empty with Option Explicit on the top.
- Select the whole NominatimGeocode function from your Geocode file and copy it.
- Paste this code into a module in your workbook.
Now theoretically you can start geocoding in your own worksheet. Theoretically, but in practice, it’s still impossible unless you have your code and VBA library prepared correctly. The major thing, you should do here is load the specified library in order to avoid the “User-defined type not defined” error.
In your VBA Excel console, you should select “Tools” from the main toolbar and next choose “References” at the very top. Next, you must find the Microsoft XML v3.0 library and switch it on (Pic. 5). It will be loaded shortly.
Pic. 5 Adding XML v.3 library to your VBA project in Excel.
You can also add this reference programmatically if you are more skilled with VBA Excel. It’s quicker because you only must run the macro instead of searching in the library list.
The example of the VBA macro for attaching this library is below:
Sub XML3library()
With ThisWorkbook.VBProject.References
Application.VBE.ActiveVBProject.References. _
AddFromFile “C:\WINDOWS\system32\msxml3.dll” ‘ the file of Microsoft XML v3.0 library
End With
End Sub
If you run the following XML3library macro, your reference will be added.
Next, you can carry on with validating your code. It’s still not the proper time for geocoding I am afraid, as you might get an error stating that “Variable is not defined” as per in the image below(Pic. 6).
Pic. 6 The “Variable not defined” error in the VBA Excel code.
It simply means, that there is a lack of Dim statements for some variables. We should define one variable in our code:
Function NominatimGeocode(address As String) As String
Application.Caller.Font.ColorIndex = xlNone
Dim xDoc As New MSXML2.DOMDocument
Dim vbErr As String
xDoc.async = False
xDoc.Load (“https://nominatim.openstreetmap.org/search?format=xml&q=” + WorksheetFunction.EncodeURL(address))
If xDoc.parseError.ErrorCode <> 0 Then
Application.Caller.Font.ColorIndex = vbErr
NominatimGeocode = xDoc.parseError.Reason
Else
xDoc.SetProperty “SelectionLanguage”, “XPath”
Dim Loc As MSXML2.IXMLDOMElement
Set Loc = xDoc.SelectSingleNode(“/searchresults/place”)
If Loc Is Nothing Then
Application.Caller.Font.ColorIndex = vbErr
NominatimGeocode = xDoc.XML
Else
Application.Caller.Font.ColorIndex = vbOK
NominatimGeocode = Loc.getAttribute(“lat”) & “,” & Loc.getAttribute(“lon”)
End If
End If
End Function
The red line indicates the element, which you should add to your code in order to run it properly – simply define the variable, which appears to be missing at the initial stage.
Next, you can finally use the Nominatim geocoding function. In your formula bar or in the cell you can type the NominatimGeocode() and calculate the location as you see below (Pic. 7). Since now, the Nominatim geocoding tool is ready to use in your own workbook.
Pic. 7 The Nominatim function in our Excel workbook enables us to proceed with location geocoding.
2. OTHER EXCEL GEOCODING TOOLS WORTH ATTENTION
Apart from the tools described by myself, there are other appliances useful in geocoding the addresses in MS Excel. One of them is provided by OpencageData, where you can register your API key and make 2500 requests per day. It’s very useful and much quicker than Nominatim. The geocoding can be done both with VBA Excel as well as via Google Sheets. Under these links, you can learn more about how to do it.
Pic. 8 OpenCage geocoding example in Google Sheets.
I wish I could present it in the 1st part of this article, fully dedicated to geocoding with Google Sheets’ assistance. I didn’t, because here we are coming up with an external application, written by JavaScript code and defined by a custom function rather than a built-in plugin. Visit the home website and learn more about how to geocode 2500 addresses per day with OpenCage.
Another way of geocoding in Excel is the Mapcite add-in installation. You can do some geocoding for free, but registration is required. The amount of 5000 addresses monthly is not too big at all, but still, something, when you run out of the other options. Once the plugin is installed you can see it in the main Excel ribbon (Pic 9).
Pic. 9 The Mapcite tools in Excel ribbon.
Another tool, where we can do the geocoding quickly is Maplarge.com. Attaching the .csv file with our address we can get the first 100 results for free.
Pic. 10 Maplarge.com geocoding tool.
In the end, I would like to mention LocationIQ.com, where geocoding is also possible and goes along with a nice .json code.
3. SUMMARY
In these, all parts of the big article are fully dedicated to costless geocoding in Excel I have shown all the useful tools, which help you to do it. Personally, I think, that the best is the Bing Excel geocoding tool, which we can conveniently integrate with our workbook and geocode 10k addresses. Creating the new profile in order to gain the new Bing Maps API code and input it into our workbook won’t be a problem at all. If you wish to have no limit with your address geocoding, then Nominatim will be the best, but remember, that this tool is quite slow (1 request per second), so you will have to wait. It’s good to consider the OpenCage geocoding, which gives you 2500 records daily.
This is geocoding, where coordinates are gained from the addresses. In the near future, I would like to find the best and explain to you the costless reverse geocoding appliances for MS Excel, where at the basis of coordinates, the address string will be provided.
Mariusz Krukar
References:
- Kounadi O, et al., 2013, Accuracy and privacy aspects in free online reverse geocoding services, (in:) Cartography and Geographic Information Science, vol. 40, issue 2, p.140-153
- Zandbergen P.A., 2-11, Influence of street reference data on geocoding quality, (in:) Geocarto International 26(1), p.35-47
Links:
- https://nominatim.org/
- https://www.geocod.io/how-to-geocode-addresses-in-excel/
- 7-free-geocoding-apis-google-bing-yahoo-and-mapquest
- http://www.tushar-mehta.com/publish_train/data_visualization/10a%20Dashboards-Geocoding.shtml
- blog.batchgeo.com/open-data-open-source-geocoders/
- https://giswiki.hsr.ch/Geocoding
- github.com/gramener/geocode-excel
- https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically
- https://opencagedata.com/tutorials/geocode-in-googledocs
- https://opencagedata.com/tutorials/geocode-in-excel
- Excel – geocoding with MapCite plugin
- https://www.mapcite.com/2020/04/06/putting-your-data-on-the-map-an-introduction-to-geocoding/
- https://maplarge.com/geocoder
- https://locationiq.com/
Forums:
- Nominatim-mass-geocoding
- Bulk-geo-coding-of-business-addresses
- https://stackoverflow.com/questions/13232613/automatic-geocoding-using-vba
- https://stackoverflow.com/questions/46682798/error-when-using-microsoft-xml-v3-0-or-v6-0
Wiki:
Youtube:
