GeoNames Forum

Here's a really nice feature of the spreadsheet in Google Docs. The "IMPORTXML" feature can grab XML from a URL. Which means you can perform queries against the GeoNames foums from within a google docs spreadsheet.

For example, paste this into a spreadsheet cell:
=IMPORTXML("http://ws.geonames.org/search?q=Sydney&maxRows=1&style=FULL", "/geonames/geoname[1]/timezone")

This will grab the xml at "http://ws.geonames.org/search?q=Sydney&maxRows=1&style=FULL", and use the XPATH query in the second parameter to grab the timezone.

The becomes a lot more useful when combined with a CONCATENATE function. Try this, paste three cities into cells A1-A3: "Sydney, Paris, Damascus". In B1 paste in a function to get the timezone for the city in cell A1:
=IMPORTXML(CONCATENATE("http://ws.geonames.org/search?q=",A1,"&maxRows=1&style=FULL"), "/geonames/geoname[1]/timezone")

..and you get the timezone. Now drag the formula down to the next two cells to get all the timezones. Nice!

Hopefully the Geonames webservice can handle everyone playing around with this feature.

Note: google docs limits you to 50 IMPORTXML queries per spreadsheet : http://documents.google.com/support/bin/answer.py?hl=en&answer=75507 .

Thanks for sharing this tip with us.

Marc

Hi Russau,

Sure this works?? It gives me a parse error!!!

Thanks,
Ron

I just tried it again, and it works fine. Did you definitely copy/paste the full IMPORTXML function?

Yes, did a paste in a spreadsheetcel...

=IMPORTXML("http://ws.geonames.org/search?q=Sydney&maxRows=1&style=FULL", "/geonames/geoname[1]/timezone")

thanks,
Ron

This is the link..

https://spreadsheets.google.com/ccc?key=0Aq4-NxQ0NzNDdENHa3hyY1pEZjlvM0dsRDRnRHRpUVE&hl=nl

Works now...

It has to do with the default language for the spreadsheet. Changed it from Dutch to US.

Thanks..