The previous 3 articles were concentrated on the geocoding process in Excel for free. I considered the Google Sheets option and also the VBA Excel tools applicable to Bing, Nominatim, and others. Now it’s the highest time to explain the process from another way around. Sometimes we have a situation when reverse geocoding is more needed, than geocoding. We want to have an address from the numerical string of coordinates. There are at least two nice ways to get the address we want.
Let’s get started from the Nominatim reverse geocode tool, which features an opposite approach to the same – Nominatim geocoding tool. Both geocoding and reverse geocoding tool is to be found here. The steps at the beginning are identical to the geocoding process. We should open this file and copy the VBA code included there. We should also remember the VBA library prepared correctly for this purpose.
Our final VBA code should look like this:
Function NominatimReverseGeocode(lat As Double, lng As Double) As String
On Error GoTo eh
Dim xDoc As New MSXML2.DOMDocument
Dim Url As String, vbErr As String
xDoc.async = False
Url = “https://nominatim.openstreetmap.org/reverse?lat=” + CStr(lat) + “&lon=” + CStr(lng)
xDoc.Load (“https://nominatim.openstreetmap.org/reverse?lat=” + CStr(lat) + “&lon=” + CStr(lng))
If xDoc.parseError.ErrorCode <> 0 Then
Application.Caller.Font.ColorIndex = vbErr
NominatimReverseGeocode = xDoc.parseError.reason
xDoc.SetProperty “SelectionLanguage”, “XPath”
Dim loc As MSXML2.IXMLDOMElement
Set loc = xDoc.SelectSingleNode(“/reversegeocode/result”)
If loc Is Nothing Then
Application.Caller.Font.ColorIndex = vbErr
NominatimReverseGeocode = xDoc.XML
Application.Caller.Font.ColorIndex = vbOK
NominatimReverseGeocode = loc.Text
The same as it was previously, we should define at least one variable. It’s definitely the “vbErr” and sometimes even the “Url“. Otherwise we will get the compile error saying that our variable is not defined (Pic. 1).
The VBA code above works fine, so you can use it along with the Microsoft XML v.3.0 library active.
The reverse geocoding in Nominatim requires two separate columns, where our coordinates are provided. The example below displays how to manage it from Google Maps coordinates. There is a new way to get them, which has been developed recently. You can just simply copy the coordinates of your place to the clipboard quickly (Pic. 2).
Next, just copy them to one of the cells in your Excel file opened and follow the steps shown below (Pic. 3), which include further data manipulation and reverse Nominatim geocode use.
Because the coordinates from Google Maps come in one piece, we have to split them between longitude and latitude. When we have two separate columns including latitude in the first and longitude in the second one, we can use the NominatimGeocode function (Pic. 3). It should return the same address as we can get i.e. from the gridreferencefinder.com or latlngfinder.com websites (Pic. 4).
Another way of reverse geocode we can find at OpenCage Data server, where are effectively three ways to do so. On the main demo page, you can go with single coordinates (Pic. 5), although more effective are VBA macros available and described here as well as the Google Sheets solution.
Another thing is to check how detailed are our addresses gained by this reverse geocoding tool. I am going to consider only one from this list, and compare it as previously with the gridreferencefinder.com (Pic. 7).
Some of the addresses produced by the OpenCage tool might be not accurate enough I am afraid.
Another tool, which offers the reverse geocoding exercise is Geocod.io, but it works for the US addresses only. You can proceed up to 2500 records daily for free.
Another tool, better than above, although with 100 records for free only is Maplarge.com. The reverse geocoding process is swift and takes a few seconds only after uploading our .csv file (Pic. 9).
The result is really nice, as we have all address columns divided by the city, street, house number, etc (Pic. 10).
The last tool worth mentioning, however not directly related to Excel is LocationIQ.com. By reverse geocoding a single address there (an amount is restricted in the “Demo” version) we can get a nicely parsed .json file (Pic. 10) as well as a detailed address.
Concluding, I would choose the Nominatim reverse to geocode macro, which is the best in the range of tools available for free. Obviously, the basic demerit of the Nominatim macro is its speed, as we can proceed with about 1 request per second. However, regardless of the pace of the reverse geocoding process, we can feel free to progress as many records as we want. The other tools listed are not as generous as Nominatim is, but they work much quicker. We can always try to combine our job with the limits offered by all the applications listed and finally finish it in Nominatim, completed the stuff, which left to work with. It’s my final hint for it. Both the geocoding and reverse geocoding market is flexible and changes all the time. Probably about a few months after this article you will be able to find new approaches like this. Personally, I am done with the geocoding/reverse geocoding matter in Excel. Obviously, this thread will be continued with more advanced programming and obviously without losing any cent.
- The costless way to geocoding addresses in Excel – part1, via Google Sheets
- The costless way to geocoding addresses in Excel – part 2, via Bing Maps API
- The costless way to geocoding addresses in Excel – part 3, bulk data geocoding with Nominatim and other geocoding tools