Advertisements

An original point of view

GIS

Reverse geocoding in Excel without loosing money

Reverse geocoding OpenCages

An example of reverse geocoding in OpenCages

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:

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).

VBA Excel Nominatim variable not defined

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) have 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).

Grabbing coordinates from Google Maps

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.

Nominatim reverse geocode in Excel steps

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).

Nominatim reverse geocode vs gridreferencefinder.com

Pic. 4 The reverse geocode result in Nominatim macro vs gridreferencefinder.com website tool.

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.

OpenCage desktop reverse geocoding

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.  An instruction of the code running you can find here.  When you are ready, you can start fiddle with bulk data by providing coordinates for more than 1 place. Remember, that your API kay 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 OpenCage tool looks as per below (Pic. 6).

OpenCage reverse geocoding with google Sheets

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 the gridreferencefinder.com (Pic. 7).

OpenCage reverse geocoding address check

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.

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.

Geocod.io Address geocode

Pic. 8 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. 9).

MapLarge reverse geocoding

Pic. 9 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. 10).

Maplarge reverse geocoding2

Pic. 9 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. 10) as well as a detailed address.

LocationIQ geocoding

Pic. 10 Reverse geocoding with Location IQ.

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.

Links:

  1. https://gridreferencefinder.com/
  2. https://latlngfinder.com/b.php
  3. https://www.geocod.io/
  4. https://rapidapi.com/collection/reverse-geocoding-apis
  5. https://www.geodose.com/2019/11/how-to-do-geocoding-and-reverse-free.html
  6. https://www.wisegeek.com/what-is-reverse-geocoding.htm
  7. https://maplarge.com/geocoder

Forums:

  1. https://stackoverflow.com/questions/13869828/reverse-geocode-in-google-docs
  2. https://stackoverflow.com/questions/63338515/bing-maps-reverse-geocode-by-landmark-or-building-name
  3. https://stackoverflow.com/questions/11616169/looking-for-a-good-and-free-reverse-geocoding-api

Wiki:

  1. Reverse_geocoding


Read also:

  1. The costless way to geocoding addresses in Excel – part1, via Google Sheets
  2. The costless way to geocoding addresses in Excel – part 2, via Bing Maps API
  3. The costless way to geocoding addresses in Excel – part 3, bulk data geocoding with Nominatim and other geocoding tools
Advertisements
%d bloggers like this: