Reverse geocoding in Excel without loosing money
The previous 3 articles 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 best 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 with the Nominatim reverse geocode tool, which features an opposite approach to the same – the 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 is prepared correctly for this purpose.
Our final VBA code should look like this:
Option Explicit
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
Else
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
Else
Application.Caller.Font.ColorIndex = vbOK
NominatimReverseGeocode = loc.Text
End If
End If
Exit Function
eh:
Debug.Print Err.Description
End Function
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).
Pic. 1 An example of the compile error “Variable not defined” when the definition of our variables (initially missing in the Nominatim reverse geocode macro) has been switched off from the code.
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).
Pic. 2 Grabbing coordinates from Google Maps quickly to our clipboard.
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.
Pic. 3 Important steps for Nominatim reverse geocode in Excel, where: 1 – “Text to columns” option used for dividing the coordinates from Google Maps; 2abc – The “Convert Text to Columns” wizard; 3abc – deleting unnecessary column with a comma, separating coordinates in Google Maps; 4 – Providing the “NominatimReverseGeocode” function applied in our VBA code; 5ab – our address received from coordinates.
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).
Pic. 4 The reverse geocode result in Nominatim macro vs. gridreferencefinder.com website tool.
Another way of reverse geocode is found at the OpenCage Data server, where there 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.
Pic. 5 Desktop reverse geocoding on OpenCage server, where: 1 – the address results; 2 – our address on the map; 3 – our address on the map parsed in the .json file, including the permalink (A), address details (B) and point geometry (C).
There is a dedicated JavaScript code for Excel Sheets, available here. Instructions on the code running can be found here. When you are ready, you can start fiddling with bulk data by providing coordinates for more than 1 place. Remember, that your API key allows you to proceed up to 2500 records daily. Personally, it’s still a huge amount of places, so I believe, that in your case should be also fine. The whole reverse geocoding process in Google Sheets with the OpenCage tool looks as per below (Pic. 6).
Pic. 6 The reverse geocoding process in Google Sheets with OpenCage tool, where: 1 – splitting the text to separate columns (the tool needs the following columns: longitude, latitude, and address); 2 – Separator detected automatically; 3 – Inserting a new row on the top; 4 – All columns ready for script usage; 5 – Entering our OpenCage API key; 6 – Alert about the column selection, if you have them ready just click OK; 7 – Our addresses are ready.
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 Gridreferencefinder.com (Pic. 7).
Pic. 7 Checking the OpenCagereverse geocode validity with the gridreferencefinder.com tool, where: 1 – address doesn’t match; 2 – the address is OK.
Some of the addresses produced by the OpenCage tool might be not accurate enough I am afraid.
Staying with Google Sheets, there is another nice option to extract addresses from the coordinates. This blog presents a fantastic approach to getting the addresses quickly. After pasting the code below:
function reverse_geocode(lat,lng) {
Utilities.sleep(1500);
var response = Maps.newGeocoder().reverseGeocode(lat,lng);
for (var i = 0; i < response.results.length; i++) {
var result = response.results[i];
Logger.log('%s: %s, %s', result.formatted_address, result.geometry.location.lat,
result.geometry.location.lng);
return result.formatted_address;
}
}to the Script section in Google Sheets, we just need to use the function likewise we normally do in our spreadsheet calculations. The address will come out quickly (Pic. 8).
Pic. 8 Reverse geocoding process in Google Sheets by using the reverse_geocode function.
Another tool, which offers the reverse geocoding exercise is Geocod.io, but it works for US addresses only. You can proceed with up to 2500 records daily for free.
Pic. 9 Geocoding and reverse geocoding with the geocod.io platform, which works for the US addresses only.
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. 10).
Pic. 10 reverse geocoding with Maplarge.com.
The result is really nice, as we have all address columns divided by the city, street, house number, etc (Pic. 11).
Pic. 11 reverse geocoding with Maplarge.com – results.
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. 12) as well as a detailed address.
Pic. 12 Reverse geocoding with Location IQ.
In conclusion, 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, but they work much more quickly. We can always try to combine our job with the limits offered by all the applications listed and finally finish it in Nominatim, completing the stuff, 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.
Links:
- https://gridreferencefinder.com/
- https://latlngfinder.com/b.php
- https://www.geocod.io/
- https://rapidapi.com/collection/reverse-geocoding-apis
- https://www.geodose.com/2019/11/how-to-do-geocoding-and-reverse-free.html
- https://www.wisegeek.com/what-is-reverse-geocoding.htm
- https://maplarge.com/geocoder
- https://blogs.oregonstate.edu/deanna/2020/08/01/reverse-geocode-google-sheet/
Forums:
- https://stackoverflow.com/questions/13869828/reverse-geocode-in-google-docs
- https://stackoverflow.com/questions/63338515/bing-maps-reverse-geocode-by-landmark-or-building-name
- https://stackoverflow.com/questions/11616169/looking-for-a-good-and-free-reverse-geocoding-api
- https://stackoverflow.com/questions/27607092/get-city-state-country-from-latitude-and-longitude-in-google-sheets
Wiki:
Read also: