LinkedIn
Twitter
_In this post we’ll explore an easy way to enhance your geodata using Apps script and Google Maps service, which makes it easier to plot values on a map in Looker, without having to create a topoJSON file.
Getting latitude & longitude for any address
Google Apps Script is a powerful way of adding additional functionality to Google Sheets, enabling you to add custom menus and functions, as well as integrations with other Google services. Here, we’ll add two functions that can call Google Maps service and Yandex Maps Service, and use that service to get the location coordinates. Note that both services are subject to quota.
Open up a new Google Spreadsheet and go to the script editor, under the Tools menu and paste in the Apps Script code below:
/**
* Returns latitude and longitude values for given address using the Google Maps Geocoder.
*
* @param {string} address - The address you get the latitude and longitude for.
* @customfunction
*/
function GEOCODE_GOOGLE(address) {
if (address.map) {
return address.map(GEOCODE_GOOGLE)
} else {
var r = Maps.newGeocoder().geocode(address)
for (var i = 0; i < r.results.length; i++) {
var res = r.results
return res.geometry.location.lat + ", " + res.geometry.location.lng
}
}
}
/**
* Returns latitude and longitude values for given address using the Yandex Geocoder.
*
* @param {string} address - The address you get the latitude and longitude for.
* @customfunction
*/
function GEOCODE_YANDEX(address) {
if (address.map) {
return address.map(GEOCODE_YANDEX)
} else {
input = encodeURI(address)
var r = UrlFetchApp.fetch(
"https://geocode-maps.yandex.ru/1.x/?format=json&geocode=" +
input + "&results=1&lang=en-US", {
"method": "get"
})
var res = JSON.parse(r)
try {
res = res.response.GeoObjectCollection.featureMember[0].GeoObject.Point.pos
res = res.split(" ")[1] + ", " + res.split(" ")[0]
return res
} catch (e) {
return ""
}
}
}
As you can see, we’re defining two functions that takes an address as input. The metadata that is added on top function gives more context on how it can be used. Hit save and return back to the spreadsheet.
On the spreadsheet, add the addresses you want to return the geo coordinates for. Both geocoders are quite accurate, but it is still important to be specific about addresses. For example, “Rialto”, would most probably bring you to the famous Rialto Bridge in Venice, Italy, even though you really meant “The Bird Flanagan Pub, Rialto, Dublin”. Try appending a city, state or country name if you only have a short address.
Now we can use either one of the two custom formulas to get the coordinates:
=GEOCODE_GOOGLE(A2) // OR =GEOCODE_GOOGLE(“The Bird Flanagan Pub, Rialto, Dublin”)
or to use Yandex:
=GEOCODE_YANDEX(A2) // OR =GEOCODE_YANDEX(“The Bird Flanagan Pub, Rialto, Dublin”)
You’ll get 53.336599, -6.2992162 as the result, meaning you found your spot (and it’s a good spot). Subsequently you can split the latitude and longitude with a (=SPLIT(B2, ", ")) into two separate columns.
Bringing this data into Looker
We can now look at getting this data into looker. If you’re using BigQuery, you can use the BQ functionality to query data in Drive, described here. If you need a more database agnostic way to get your data into Looker, you can use a JDBC connection straight from Apps script and write your data to your database.
After bringing the data into Looker, I can create a view called location from this table and join this view on any table that contains Dublin area codes, which allows me to map these areas easily:
