Advertisements
Advertisements

An original point of view

GIS

The costless way to geocoding addresses in Excel – part1, via Google Sheets

Google Sheets addresses in Excel

Pic. 6 Our geocoded address list in Excel.

The address geocoding is a very desired tool nowadays. This is because we might need to know the location of many places at once.  If for example, you want to have about 500 exact coordinates of your addresses, you won’t do them one by one, otherwise, it will last forever.
So far I showed you some of the tools, which can do the address geocoding for you without any charge. The major drawback of them is the restricted upper limit of the operations. It usually cannot make more than 250 records at once. In another way, you can use Google Earth to convert 10x more. However, at this moment we are complicating our task because it starts to take longer than we would expect.
In this short part of the article, I would like to demonstrate to you how to do the address geocoding in Excel, the widest calculation software used in our offices.
Converting our addresses into the geometry supposed to be very quick and convenient in MS Excel, as our work is confined to typing and dragging down the relevant formula.
You might be itching to ask – why am I showing you only the basic tools, which don’t offer a bulk geocoding like 10000 addresses or more?  At this stage, I want to demonstrate how to deal with a smaller amount of locations and make them in the right place in our Excel Spreadsheet. Next time you can expect an extension of this work covering also the process straight from your spreadsheet, and further in the future – with respect to the bulk address data.

There used to be 2 ways to do geocoding in Excel. You can install the extension, based on the VBA Excel or write your own VBA code. Firstly, stressing the “used to” expression, because these ways are not for free anymore. Secondly, you don’t need to write the VBA code anymore, since there are tools created especially for this occasion. You can find them i.e on the GitHub page.

The sad thing is, that most of the applications aimed at the address geocoding require the API keys, which restrict the user to a small number of records. For example, if you want to geocode with Bing.com, be prepared for only 10000 records in total. After that, you must create a new account, which can be awkward. Google API, unfortunately, is ruthless and wants us to pay for any amount of records since august 2018.
In this event, even if you place your Google API key into your VBA code, you won’t be able to do anything (Pic. 1).

VBA Excel address geocoding

Pic. 1 Address geocoding in Excel (VBA Excel macro) not valid due to a lack of proper Google API Key.

Unfortunately, the “AddressGeocode” formula keeps coming with nothing for every single address. In this event, the VBA Excel code is not useful, unless we make it compatible with the Bing API key. Nonetheless, as mentioned above, Bing won’t allow you to geocode more than 10000 addresses under 1 single API key. Afterward, the new profile and new API key at once is required.
There is one, quick solution for this, but not directly related to Excel Spreadsheet.
You can easily use the Google Sheets, where the Geocode by Awesome Table plugin is available.
With this plugin, you are able to geocode up to 1000 records per day, which is good. Remember to set up your plugin first, before you attend to this process. It will prevent you from unwanted situations, leading to complaints, that the tool is simply “not working”. Remember also, that in order to use Googe Sheets you must be signed into the Google account.
When you are ready, just create the new document in your Google Sheets account, where next your addresses should be pasted (Pic. 2).

Google Sheets addresses example

Pic. 2 The address list pasted into Google Sheets document.

Copying from Excel and pasting in Google Sheets in the same position prevents the initial order of our locations.
If everything is good, you can run the plugin, which at the very beginning will ask you about the main address column. It’s good to keep the column name in the first row. At least the tool know the place to start with. Next, the plugin automatically creates the “Latitude” and “Longitude” columns for the result input. The process is quite slow but gives a decent effect (Pic. 3).

Geocoding Google Sheets

Pic. 2 The address geocoding process in Google Sheets. The Latitude and Longitude columns are created by the tool in order to place the result.

When everything is done, we can copy our data from Google Sheets (Pic. 3), and next paste them in our Excel document.

Google Sheets address copy

Pic. 3 Selection and copy geocoded addresses from Google Sheets to Excel.

If you are sure about the order of your address list, what I indicated earlier, it’s enough to copy the columns with coordinates only (Pic. 4), and paste them next to the existing address column in Excel (Pic. 5).

Google Sheets address copy2

Pic. 4 Selection of the columns with coordinates only, taking into account, that their values correspond to the address array.

Google Sheets addresses in Excel

Pic. 5 The google Sheets addresses pasted in Excel.

Make sure, that you are selecting the Match Destination Formatting option. It will help you save time with further formatting. Your result will be automatically adjusted to the format of the existing cells. The other way is saving this sheet as a .xlsx file.
Finally, you have got your problem sorted (Pic. 6).

Google Sheets addresses in Excel

Pic. 6 Our geocoded address list in Excel.

The probes are a bit awkward, as we must go out from the Excel document and use the external tool. However, treading this way we can sort this problem out quickly. It doesn’t mean, that other solutions such as this are not available. This is one of the solutions.
A similar one can be found here. This one can also do the reverse geocoding for you, also in Google Sheets.

The process above applies to a smaller amount of records. It’s not the end of my tinkering with address geocoding.  The second part of this article will bring the new ways to deal with this task. Further, as the continuation of this article, you will be taught how to deal with bulk address geocoding.

Mariusz Krukar

 

Links:

  1. Mapdevelopers.com – batch geocoding (100 addresses)
  2. MapYourList.com – batch geocoding (up to 250 addresses)
  3. https://gisgeography.com/reverse-geocoding-services-addresses-free-paid/
  4. http://more.stevemorse.org/latlonbatch2.html? (monthly limit 15000)
  5. http://excelgeocodingtool.com/
  6. https://www.ilovefreesoftware.com/12/tutorial/geocode-address-lat-long-excel.html
  7. https://weblogs.asp.net/wkriebel/using-excel-to-geocode-with-bing-maps-web-services
  8. https://www.shayatik.com/2014/04/batch-geocode-addresses-using-bing-maps-api-and-an-excel-worksheet/
  9. https://myengineeringworld.net/2014/06/geocoding-vba-google-api.html
  10. Google Sheets awesome table geocode
  11. Auto populate longitude & latitude in Excel (Google API Key required)
  12. Geocode by Awesome Table – tutorial
  13. Geocode by Awesome Table FAQ
  14. https://vilimpoc.org/blog/2013/07/11/google-spreadsheet-geocoding-macro/

Forums:

  1. https://gis.stackexchange.com/questions/70586/geocoding-using-excel-spreadsheet
  2. https://gis.stackexchange.com/questions/102839/how-to-geocode-a-google-spreadsheet/340215#340215

 

Youtube:

Read also:

1. Excel Spreadsheet batch data geocoding for .geojson and .json files

 

Advertisements
Advertisements
Follow

Get the latest posts delivered to your mailbox:

%d bloggers like this: