Friday, October 31, 2008

Geocoding by Google Spreadsheets

Tony Hirst published a highly inspiring article about doing a Google Maps mashup without JavaScript.

I would like to show that Google Spreadsheets can do geocoding as well as Pipes.

There is http-version of Google Maps geocoder. Send a request:
http://maps.google.com/maps/geo?q=1600+Amphitheatre+Parkway,+Mountain+View,+CA&output=xml
and you get a full-blown XML response. With parameter output=csv you get a simplified response
200,6,42.730070,-73.690570 Status, accuracy, lat, lng, as a four cell csv text response,  yahoo!

Google Spreadsheets can import csv-data by  =ImportData()formula. So why don't we make it fetch coordinates of the address in the next cell.

The order of the parameters is not convenient for us, so we change it. The query-parameter 'q' shall be the last one. We type in the first cell of Spreadsheets:
http://maps.google.com/maps/geo?output=csv&q=
In the next cell we type the requested address like Helsinki.

The third cell will add together the two previous cells and ask for data by ImportData() formula:
=ImportData(CONCATENATE(A2,B2))

When you finished typing that, you will see a miracle in the next cells: '200' (http status), '4' (Google accuracy parameter), 60.169879 (latitude of Helsinki) and 24.938408(longitude of Helsinki). Did we ask anything more?

And they say that there is no geocoding in Google Spreadsheets. Plus think how it can import and export. Hold my hat!

There is also automatic googling to get input data.
=GoogleLookup(entity,attribute)
can fetch your POI (entity) address(attribute), and you geocode that as above.

You can use the output via a variety of feeds or read it by ajax-api as JSON. You can for instance preload your pages GClientGeocoder cache to know the POIs. Your visitors don't find the POI by geocoding unless you took care of it.


What happens when all the pages read each other in a loop.

18 comments:

Jonathan_the_Seagull said...

very very interesting:-)
I tryed it just now but it doesn't works, why?!
I also used the same cities,
what I'am doing wrong?!
thanks :-)

tsinn said...

Love it. I've been looking for this for a while now. Working on a site where anyone can put a place into a Google form, and now that will pretty easily translate into a crowd-sourced collaborative map. Thanks!

Josh said...

AWESOME!!! Thanks so much... this is exactly what I needed for a small map project!

Richard said...

Brilliant - now I have a sheet with 3500 entries that I want to geocode and it says I can only do 50!! Do I have to split my data over 70 sheets?

Tobias S said...

thanks a lot great post

Aparna said...

very interesting post, thanks for sharing
web hosting delhi

akkad said...

thanks a lot for this short tutorial. Works great

idotter said...

I got some numbers like 473046863 76101556
instead of 47.3..... and 7.6.... whitout any "."

THX

garyg said...

This is great... it took some searching, but I was sure someone had figured it out.

Πολύβιος said...

Any idea on how to geocode on different languages?

I mean, that if I try
http://maps.google.com/maps/geo?output=csv&q=matapa%20110,peiraias

works perfect but if I try to write the address in greek i.e.

http://maps.google.com/maps/geo?output=csv&q=ματαπα 110, πειραιας

then nothing..
Any idea on how to integrate a language parameter?

Thomas said...

Hi !

Awesome trick. But I'm now stuck. I've a google form that produces addresses and I want that the importdata automatically applies to the new adresses appearing in my sheet by the way of the form.

I tried with expand and with arrayformula but it doesn't seem to work.

Any idea ?

many thanks

Movies Gallery 2011 said...

Thanks for the information about Spreadsheets its very informative.

Lower Back Pain
Superheroes

Sarah-jane said...

Awesome!

d-pabs said...

How would I get this to work if I want to query a series of restaurants in a particular city? E.g. I want to generate a table of all the McDonald's in Sacramento where I show the phone number, address, among other pertinent information.

J.A. said...

I grew up there. I remember when the geo location of Google's headquarters was a dirt field. My friends and I would catch frogs in the creeks right there. Now there are little to no frogs, but tons of Googles.

J.A. said...

I grew up there. I remember when the geo location of Google's headquarters was a dirt field. My friends and I would catch frogs in the creeks right there. Now there are little to no frogs, but tons of Googles.

J.A. said...

I grew up there. I remember when the geo location of Google's headquarters was a dirt field. My friends and I would catch frogs in the creeks right there. Now there are little to no frogs, but tons of Googles.

J.A. said...

I grew up there. I remember when the geo location of Google's headquarters was a dirt field. My friends and I would catch frogs in the creeks right there. Now there are little to no frogs, but tons of Googles.