6/13/14

Using Google BigQuery to find the temperature history for your birthday or the best day for your wedding

Want to know what the average temperature has been on your birthday for the past several years or maybe the most sunny day of the month for your wedding?

Log into google and wander over to:

https://bigquery.cloud.google.com/table/publicdata:samples.gsod

This will let you query the GSOD (weather observatory station records) thanks to google big data.

Then execute this query:

SELECT year, mean_temp, max_temperature FROM
 [publicdata:samples.gsod] where wban_number=14742 and month=X and day=YY order by year asc;

You'll have to determine the nearest wban_number for the weather station closest to you - to get this yead over to:

http://academic.udayton.edu/kissock/http/Weather/citywbanwmo.txt

And search for your city or state.

Once you are done, you'll end up with numbers than can be plotted as a trend thusly:


If anyone can find a public dB of WBAN lat/lon let me know.

have fun.

bill