Geocoding and Mapping a Spreadsheet Addresses
A simple way to use Google Maps Geocoding API
Geocoding is the process of converting a physical address or location (such as a street address, city, or zip code) into geographic coordinates, typically latitude and longitude. These coordinates can then be used to place the address or location on a map.
Geocoding is commonly used in mapping applications and geographic information systems (GIS) to help users find locations, calculate distances between places, and analyze spatial data. It can also be used to match addresses with other data sources, such as demographic data or sales data, for analysis and visualization purposes.
Geocoding is usually performed using geocoding software or services that use various sources of data, such as postal databases, satellite imagery, and digital maps. The accuracy of geocoding can vary depending on the quality and completeness of the input data and the algorithms used to process it.
Tipically addresses are contained in a Excel spreadsheet, this story explains how to geocode a list of addresses in no time using the google maps geocoding api. This can be done for free in one go as long as the list is smaller than 5000 addresses average, a higher number of records will need a premium subscription to the api. The following figure shows an example of addresses located in the US, these will be used for the geocoding.
For privacy concerns, addresses were obtained randomly using this web service:
https://www.bestrandoms.com/random-address-in-us
Follow the steps:
- Create a folder with any name within your Google Drive
- Upload your Excel file containing addresses to previous folder
3. Open your excel file and save it as a Google Spreadsheet
4. Add x and y fields to the spreadsheet
5. Go to Extensions / Apps Script, delete what is there and paste the following code in Go.
function getGeocodingRegion() {
return PropertiesService.getDocumentProperties().getProperty('GEOCODING_REGION') || 'us';
}
/*
function setGeocodingRegion(region) {
PropertiesService.getDocumentProperties().setProperty('GEOCODING_REGION', region);
updateMenu();
}
function promptForGeocodingRegion() {
var ui = SpreadsheetApp.getUi();
var result = ui.prompt(
'Set the Geocoding Country Code (currently: ' + getGeocodingRegion() + ')',
'Enter the 2-letter country code (ccTLD) that you would like ' +
'the Google geocoder to search first for results. ' +
'For example: Use \'uk\' for the United Kingdom, \'us\' for the United States, etc. ' +
'For more country codes, see: https://en.wikipedia.org/wiki/Country_code_top-level_domain',
ui.ButtonSet.OK_CANCEL
);
// Process the user's response.
if (result.getSelectedButton() == ui.Button.OK) {
setGeocodingRegion(result.getResponseText());
}
}
*/
function addressToPosition() {
var sheet = SpreadsheetApp.getActiveSheet();
var cells = sheet.getActiveRange();
// Must have selected 3 columns (Address, Lat, Lng).
// Must have selected at least 1 row.
if (cells.getNumColumns() != 3) {
Logger.log("Must select at least 3 columns: Address, Lng, Lat columns.");
return;
}
var addressColumn = 1;
var addressRow;
var latColumn = addressColumn + 2;
var lngColumn = addressColumn + 1;
var geocoder = Maps.newGeocoder().setRegion(getGeocodingRegion());
var location;
for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
var address = cells.getCell(addressRow, addressColumn).getValue();
// Geocode the address and plug the lat, lng pair into the
// 2nd and 3rd elements of the current range row.
location = geocoder.geocode(address);
// Only change cells if geocoder seems to have gotten a
// valid response.
if (location.status == 'OK') {
lng = location["results"][0]["geometry"]["location"]["lng"];
lat = location["results"][0]["geometry"]["location"]["lat"];
cells.getCell(addressRow, lngColumn).setValue(lng);
cells.getCell(addressRow, latColumn).setValue(lat);
}
}
};
function positionToAddress() {
var sheet = SpreadsheetApp.getActiveSheet();
var cells = sheet.getActiveRange();
// Must have selected 3 columns (Address, Lng, Lat).
// Must have selected at least 1 row.
if (cells.getNumColumns() != 3) {
Logger.log("Must select at least 3 columns: Address, Lng, Lat columns.");
return;
}
var addressColumn = 1;
var addressRow;
var latColumn = addressColumn + 1;
var lngColumn = addressColumn + 2;
var geocoder = Maps.newGeocoder().setRegion(getGeocodingRegion());
var location;
for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
var lng = cells.getCell(addressRow, lngColumn).getValue();
var lat = cells.getCell(addressRow, latColumn).getValue();
// Geocode the lat, lng pair to an address.
location = geocoder.reverseGeocode(lat, lng);
// Only change cells if geocoder seems to have gotten a
// valid response.
Logger.log(location.status);
if (location.status == 'OK') {
var address = location["results"][0]["formatted_address"];
cells.getCell(addressRow, addressColumn).setValue(address);
}
}
};
function generateMenu() {
// var setGeocodingRegionMenuItem = 'Set Geocoding Region (Currently: ' + getGeocodingRegion() + ')';
// {
// name: setGeocodingRegionMenuItem,
// functionName: "promptForGeocodingRegion"
// },
var entries = [{
name: "Geocode Selected Cells (Address to Long, Lat)",
functionName: "addressToPosition"
},
{
name: "Geocode Selected Cells (Address from Long, Lat)",
functionName: "positionToAddress"
}];
return entries;
}
function updateMenu() {
SpreadsheetApp.getActiveSpreadsheet().updateMenu('Geocode', generateMenu())
}
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
*
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
SpreadsheetApp.getActiveSpreadsheet().addMenu('Geocode', generateMenu());
// SpreadsheetApp.getActiveSpreadsheet().addMenu('Region', generateRegionMenu());
// SpreadsheetApp.getUi()
// .createMenu();
};6. Save and Execute the code in the Apps Script panel. Then accept the permissions.
7. Refresh the Google Spreadsheet and a Geocode window will show up
8. Select the Address, x and y fields, click on Geocode and choose the firt option “Geocode Selected Cells (Address to Long Lat)”
See how coordinates populate x and y fields, at around two addresses per second, until all the rows are filled. The inverse process, this is, obtaining addresses from x and y values is also available with the second option of the menu.
Once coordinates are ready we can produce a map in the following manner:
Download the file in Microsoft Excel format to your local disk, use the following code to read and plot the data on a map.
!pip install geopandas
!pip install folium# import main libraries
import folium
import pandas as pd
import webbrowser
# reading file
df = pd.read_excel("file directory")
# Calculate the mean of xy input coordinates
xymean = df[['y', 'x']].mean().values.tolist()
# Creating a map with a terrain look
imap = folium.Map(xymean, control_scale=True, zoom_start = 5, tiles='Stamen Terrain')
# Plot addresses as Markers
lon = df['x']
lat = df['y']
nom = df['ID']
for i, j, k in zip(lat, lon, nom):
folium.CircleMarker(location=[i, j], popup= 'id: ' + str(k),
radius = 5, fill_color= 'blue', color="white", fill_opacity = 0.5).add_to(imap)
# Adding Layer control
folium.LayerControl().add_to(imap)
# Calculating map borders
sw = df[['y', 'x']].min().values.tolist()
ne = df[['y', 'x']].max().values.tolist()
# Centering map based on borders
imap.fit_bounds([sw, ne])
# saving the map as a html file
imap.save(r"file directory\US address example.html")
# opening map on the web browser
webbrowser.open(r"file directory\US address example.html")
imap
Conclusions
- It was never that easy to geocode a list of addresses for any country of the world.
- Geocoding can be done as fast as two addresses per second average and for free as long as the list of addresses is no larger than 5000 records.
- Input and output files are in Excel without any problem.
- Python can be used to plot an interactive map of the example addresses.
Support me
Enjoying my work? Show your support with Buy me a coffee, a simple way for you to encourage me and others to write. If you feel like it, just click the next link and I will enjoy a cup of coffee!
