Recently, quite a few people pointed me out to Tony Hirst's post on "Data Scraping Wikipedia with Google Spreadsheets". This was a really cool post because it demonstrated alot of features about Google Spreadsheets that people don't take advantage of (but should, because they're awesome): importing data from webservices or websites, publishing spreadsheets, creating charts of data, etc. But, I was sad to see that Tony had to go through so much pain to just geocode the data. Most people I know wouldn't be able to create a Yahoo! Pipe to parse the CSV and geocode spreadsheet data.. there should be an easier way. Well, there is, and that's what this post will show.
Google Spreadsheets now lets people embed gadgets, just like they can on iGoogle, Orkut (OpenSocial), Google Maps (Mapplets), and other containers. Those gadgets can live within the spreadsheet, access un-published data from a worksheet, and manipulate that data into some useful or visual output. There are gadgets for creating timelines, motion charts, word clouds, and now, courtesy of me, a gadget for geocoding data. This is my secret weapon for geocoding small sets of data, and can hopefully help other people out there.
Here are the steps for geocoding a spreadsheet with the gadget:
-
Create a new spreadsheet, and put the addresses into rows. If your
address is composed of multiple columns, just concatenate those into one
single column using the "&" operator. The image below shows a
spreadsheet of Australian beaches:
-
Click on the "Insert" menu and then select "Gadget...":
-
This presents you with various categories of gadgets to choose from
(similar to the iGoogle directory). My gadget isn't yet in the gallery,
so you'll need to select "Custom" and then type in the URL to the gadget:
-
The gadget will appear embedded in the current worksheet, and it will
prompt you to select a range of data to send to the gadget. Select all
the rows for the column with your address data, and you should see the
Range text field update with the range. If it doesn't work, you can
always manually type it in. After doing that, click the Apply button:
- Since I always hate having charts or gadgets cluttering my sheets (and I like taking advantage of screen real estate), I then usually select the "Move to own sheet" option from the gadget menu. If you don't opt to do that, atleast resize the gadget to give it a decent amount of space in the worksheet. (Image omitted because I accidentally wrote over it :).
-
When the gadget loads, it will present instructions, a blank map area,
and a "Find Addresses" button. Click the "Find Addresses" button. It
will start sending geocoding calls using the Maps API, and filling in
the sidebar and map with results. It will stop at 99 results, for both
technical and legal reasons (just use the gadget multiple times if you
have more data than that). Any addresses that failed to geocode will
have a red marker and show up in the bottom left of the map. All the
markers are draggable, so you can move them around to tweak the location
(this was a really important feature to me- geocodes can often use a
nudge or two). In this step, you may notice some of the data didn't
geocode at all, and perhaps you'll go back to your spreadsheet, edit the
address a bit, and do this step again. For example, I had to add
"Australia" to some of the beaches in my spreadsheet for disambiguation
with other beaches.
-
When you're satisfied with the marker locations, then select all in the
text field below the map, and copy the text. The text contains the
latitude/longitudes in row-order for all the markers, and is formatted
so that it's easy to paste into spreadsheet columns.
-
Finally, create two empty columns in your worksheet, and paste that text
into the cells. If you put your cursor in the first row/column, then it
should paste perfectly and align with the address data.
- You're done! Now you can use the latitude/longitude data in your Google Maps API mashup, perhaps by using the Spreadsheets -> Map Wizard or the Spreadsheets Mapper.
Nguồn:
http://blog.pamelafox.org/2008/11/geocoding-with-google-spreadsheets-and.html
