Poor man’s solution for geocoding

IMG_0907

Need to do a data map with tons of location markers? Got only addresses, but no coordinates? Don’t know how to get them? Deadline tomorrow? I think I have a solution for you, my friend.

For a while a go, my colleague gave me a challenge. He wanted me to put 5000 markers on a Google map.

Simply put, I was after this:

Screen Shot 2014-04-09 at 1.03.27 AM

Too bad the excel had only addresses of the locations, but no latitude and longitude values.

As I’m not very familiar with Google Maps api and everything involved, I was surprised that I couldn’t find solid, ready made solution for solving my little task. You know, upload .csv with addresses, download another with coordinates, have a coffee break while waiting… no, I couldn’t find one, so I had to make my own.

Let’s go through it step by step. All you need is Gmail account and little bit of knowledge of scripting. Actually you don’t even need to code, you can just copy and paste.

1. Open Google docs and create new spreadsheet

Paste your address info into one or more columns. Like this:

Screen Shot 2014-04-06 at 3.49.49 PM

2. Get MapBox geocoder

Because we got thousands of addresses, we obviously can’t go through them one by one, but we need a script to do the work for us. Luckily, these guys have made a splendid script to do the dirty work for us.

If you have never installed scripts for Google spreadsheets, follow the instructions on GitHub.

After the installation refresh the spreadsheet and you should see “Geo” tab at the menu.

3. Implement Google geocoder to the MapBox

Now if you run the script you might notice that there is Yahoo, Mapquest and Cicero geocoders available to choose. But wait a minute, where is Google? I have no idea why the authors didn’t want to have the big G included, but I got some use for it.

I first ran the Yahoo geocoder, but it didn’t find all the coordinates. The reason might be the fact I live in Finland. However, I had 5000 locations, so Google wouldn’t be enough alone. Google geocoder is limited to 2500 searches per day. So I had to take benefit of both, Yahoo and Google.

Let’s fork the code a bit. Go to “tools” > “script editor” again and open the MapBox script.

Paste following code after the line 7:

google: {
  query: function(query, key) {
    return 'maps.google.com/maps/api/geocode/json?sensor=false&address=' + query + '&key=' + key;
  },
  parse: function(r) {
    try {
      return {
        longitude: r.results[0].geometry.location.lng,
        latitude: r.results[0].geometry.location.lat,
        accuracy: r.results[0].geometry.location_type
      }
    } catch(e) {
      return { longitude: '', latitude: '', accuracy: '' };
    }
  }
},
yahoo: {
  query: function(query, key) {
    return 'http://where.yahooapis.com/geocode?appid=' +
      key + '&flags=JC&q=' + query;
  },
  parse: function(r) {
    try {
      return {
        longitude: r.ResultSet.Results[0].longitude,
        latitude: r.ResultSet.Results[0].latitude,
        accuracy: r.ResultSet.Results[0].quality
      }
    } catch(e) {
      return { longitude: '', latitude: '', accuracy: '' };
    }
  }
},

Now find row 248 and put the following line in place

.addItem('google')

Now when you click the “Geo” tab again, you should have Google as an option.

 4. Find the coordinates!

Now go back to the spreadsheet and first run the Yahoo geocoder. Now if the results suck, run the Google geocoder for the rest of the addresses. Now you should have all the latitudes and longitudes!

The result should look something like this:

Screen Shot 2014-04-09 at 12.59.18 AM

That’s all folks and feel free to ask anything! Probably I won’t know the answer anyways!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s