Get latitude/longitude for any location through Google Sheets and plot these in Looker

google
geocode
sheets
map

(Brecht Vermeire) #1

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. As an example we’ll use a few Dublin areas, which can be found in this example spreadsheet.

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 a function that can call Google Maps service and use that service to get the location coordinates.

Let’s 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
 *
 * @param   {"address"}  address Address type.
 * @customfunction
 */
function geocode(address) {
    Utilities.sleep(1000);
    var response = Maps.newGeocoder().geocode(address);
    for (var i = 0; i < response.results.length; i++) {
        var result = response.results[i];
        return result.geometry.location.lat + ", " + result.geometry.location.lng;
    }
};

As you can see, we’re defining a function that takes an address as input. The metadata that is added on top function gives more context on how it can be used. Here we’re using the Maps.newGeocoder() class to geocode our address. Hit save and return back to the spreadsheet.

On the spreadsheet with our area names, let’s add a second column where we can add the ‘Dublin’ suffix, so Google Maps knows we’re looking for Dublin areas only. We can then add a third and a fourth column, lat & lng. In the lat column we can use the following formula:

=split(geocode(A2),", ")

You’ll see that the coordinates will fill the lat & lng columns. How cool is that?

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:


How to get the 'place name' based on Location data?
(romain.ducarrouge) #2

Using the same process as above you can also achieve approximate mapping of IP addresses.
The function below uses the free IP geolocation API from http://freegeoip.net/

function ip_geolocation(cell_reference) {

  # cell_reference is the Google Sheet value (e.g '8.8.8.8' or 'github.com')
  var response = UrlFetchApp.fetch("freegeoip.net/json/" + cell_reference);
  var result = [];
  var values = JSON.parse(response.getContentText());
  result.push([values.latitude , values.longitude, values.city, values.country_name, values.country_code]);
    
  return result
}

This will work for IP addresses and domain names.


(Alen Sebastian) #3

Hey @romain.ducarrouge,
looks like freegeoip has been deprecated, is there any other link?


(philip.martinelli) #4

Hey @alenseb,

Per

 wget http://freegeoip.net/
--2018-07-10 18:01:21--  http://freegeoip.net/
Resolving freegeoip.net (freegeoip.net)... 104.25.148.25, 104.25.149.25, 2400:cb00:2048:1::6819:9519, ...
Connecting to freegeoip.net (freegeoip.net)|104.25.148.25|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://ipstack.com [following]
--2018-07-10 18:01:21--  https://ipstack.com/
Resolving ipstack.com (ipstack.com)... 23.246.243.31
Connecting to ipstack.com (ipstack.com)|23.246.243.31|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: ‘index.html.1’

index.html.1                      [ <=>                                            ]  17.00K  --.-KB/s    in 0.08s   

2018-07-10 18:01:21 (224 KB/s) - ‘index.html.1’ saved [17406]

… it looks like the dns name has simply changed, and http://freegeoip.net/ redirects to https://ipstack.com/. Therefore, the api endpoints should still be accessible here with the changed domain.

Thanks,
Philip


(romain.ducarrouge) #5

Hey Alen, yeah the service name changed as Philip mentioned.
Another available option for personal dev work is http://ip-api.com/
their doc on using the API is here

Call: http://ip-api.com/json/208.80.152.201

response:

{
  "status": "success",
  "country": "United States",
  "countryCode": "US",
  "region": "CA",
  "regionName": "California",
  "city": "San Francisco",
  "zip": "94105",
  "lat": "37.7898",
  "lon": "-122.3942",
  "timezone": "America\/Los_Angeles",
  "isp": "Wikimedia Foundation",
  "org": "Wikimedia Foundation",
  "as": "AS14907 Wikimedia US network",
  "query": "208.80.152.201"
}

This has a limit rate of 150 requests per minute.


(Brecht Vermeire) #6

For those who frequently run into quota issues with the Google Maps geocoder, Yandex has a pretty good geocoder as well with a daily limit of 25k requests. It doesn’t seem to enforce per-minute quota either.

You can easily get a geocoder custom function in Sheets as below. One caveat, Yandex returns the location as longitude, latitude e.g. 4.478452 50.915114 (the other way around).

/**
 * Returns latitude and longitude values for given address
 *
 * @param   {"address"}  address Address type.
 * @customfunction
 */
function GEOCODE(input) {
    input = encodeURI(input)
    var options = {"method": "get"};
    var response = UrlFetchApp.fetch("https://geocode-maps.yandex.ru/1.x/?format=json&geocode=" + input + "&results=1&lang=en-US", options);
    var result = JSON.parse(response);
  try {
    result = result.response.GeoObjectCollection.featureMember[0].GeoObject.Point.pos
    return result;
  } catch(err) {
    return "" // in case we get nothing back
  }
}