Geographic and climate data sources

[GoogleCodeExporter]

* Lat/lon and altitude of world airports:
  http://openflights.org/data.html
  Data file:  http://openflights.svn.sourceforge.net/viewvc/openflights/openflights/data/airports.dat

* Daily weather observations by airport code -- here, for KABQ, 
1Jan2010-1Jan2011 (1 year limit per request?)
http://www.wunderground.com/history/airport/KABQ/2010/1/1/CustomHistory.html?day
end=1&monthend=1&yearend=2011&format=0

* U.S. Census grids -- maybe population density by lat/lon?

So, my proposal is to:
1. use college zipcodes to get latlon
2. use latlon to find code of nearest airport of each college
3. for each airport code, get max/min/mean temp, precip for 2010.

Original issue reported on code.google.com by icos.atr...@gmail.com on 10 Feb 2012 at 3:38

[GoogleCodeExporter]

zipcode-to-latlon database is here:
http://zips.sourceforge.net/

Original comment by icos.atr...@gmail.com on 14 Feb 2012 at 10:09

[GoogleCodeExporter]

scratch that.  much more complete database here (that includes methods metadata 
file):
http://mappinghacks.com/data/zipcode.zip

Original comment by icos.atr...@gmail.com on 14 Feb 2012 at 10:21

[GoogleCodeExporter]

I'm attaching zipcode csv file and metadata for permanent reference.

Original comment by icos.atr...@gmail.com on 14 Feb 2012 at 10:22

Attachments:

• zipcode.csv
• README

[GoogleCodeExporter]

I've finished code to join colleges to lat/lon and closest airport.  Attaching 
an edited airport table (column names added), along with my zipcodes, the R 
code to bind them all together, and then the finished result (last file).

I'll wait until I get everyone's zips before I paste everything together and 
get a finished master file.

Original comment by icos.atr...@gmail.com on 14 Feb 2012 at 11:13

Attachments:

• airports.dat
• Geo-colleges-data-collection-xian.csv
• geo_join.R
• college_airports_example.csv

[GoogleCodeExporter]

Attached is the finished file of college zipcodes, cities, states, closest 
airport code, lat/lon, and distance to airport (in degrees, not a good 
measure!).  Also, the R code.  I'm using the same airports.dat and zipcode.csv 
from above.

Note that I changed the column name from Zipcode to zip in 
Geo-colleges-data-collection-all.csv, from issue 29.

Original comment by icos.atr...@gmail.com on 23 Feb 2012 at 10:58

• Changed state: In-Progress

Attachments:

• colleges_geo.csv
• geo_join.R

[GoogleCodeExporter]

I managed to grab weather data. R code attached for reference.

The result is a rather large table (~115k rows) of the 2010 daily weather from 
300+ US airports in the US, keyed by airport ID.  We can easily do yearly 
max/min/mean/median/variance on any of these and join the results to the 
college table.  Pick what you want (fields and level of aggregation)!

The fields are:

Date,Max TemperatureF,Mean TemperatureF,Min TemperatureF,Max Dew PointF,MeanDew 
PointF,Min DewpointF,Max Humidity, Mean Humidity, Min Humidity, Max Sea Level 
PressureIn, Mean Sea Level PressureIn, Min Sea Level PressureIn, Max 
VisibilityMiles, Mean VisibilityMiles, Min VisibilityMiles, Max Wind SpeedMPH, 
Mean Wind SpeedMPH, Max Gust SpeedMPH,PrecipitationIn, CloudCover, Events, 
WindDirDegrees

If you want to see what it looks like, I'm attaching the table from a few 
weather stations.

Original comment by icos.atr...@gmail.com on 23 Feb 2012 at 12:06

Attachments:

• weather_small.csv
• weather.R

[GoogleCodeExporter]

Thanks for compiling this Christian. I think min, max and mean temp are 
definitely going to be necessary. And also I would say either cloud cover or 
precipitation could be useful. 

Original comment by drcolm...@gmail.com on 24 Feb 2012 at 7:04

[GoogleCodeExporter]

Yeah I agree. Mean humidity might also be helpful, as some schools like to keep 
their buildings at a constant humidity that may differ greatly from the outside 
environment (eg Florida). So are we going to take the mean for 2010, and apply 
that to all the schools even though the ACUPCC data comes from a varying number 
of years? 
Also, instead of taking the yearly average, it might be helpful to perceive the 
weather on a seasonal basis. Again, this all depends on what questions we're 
asking.
-Adeline

Original comment by adelinem...@gmail.com on 28 Feb 2012 at 5:03

[GoogleCodeExporter]

I think it would be okay to compare the 2010 max, min, mean for the weather 
data as long as we don't use temporal data greater than the past 5 - 6 years. 
While many temps, and humidity have changed somewhat, they have done so fairly 
equally across the board. So differences between geographic ranges should still 
be very close.
-Kevin

Original comment by SKMcCorm...@gmail.com on 28 Feb 2012 at 8:59

[GoogleCodeExporter]

The last attached file is the cleaned-up, final csv file. 

In addition to what we discussed, I computed the diurnal temperature range 
(daily max - daily min).

For the sake of simplicity, I ended up computing the mean and sd of most 
variables -- it gives a lot more information than max and min with fewer 
variables.  I looked at the variables, and they're all normal-ish.

Precip is an exception -- most days are zero -- so I computed the yearly sum 
and the number of days with precip>0.

I looked at cloudcover, and it seemed almost meaningless.  It's mostly zero, 
always below 10, doesn't seem to go along with rain events etc.

Finally, I cleaned up the airport search code to drop unnecessary columns.  The 
end result is much cleaner.  Just keep in mind that altitude and weather are at 
the airport, not the college.

All the code files required to reproduce results are attached.  Again, the last 
file is the finished CSV file.  
P.s. I'm closing this issue now.

Original comment by icos.atr...@gmail.com on 1 Mar 2012 at 10:58

• Changed state: Done

Attachments:

• geo_join.R
• weather.R
• weather_aggregate.R
• college_weather.csv

[GoogleCodeExporter]

Last note -- I added the CSV file to downloads, and a short description of 
methods to the Data_Methods page.

Original comment by icos.atr...@gmail.com on 1 Mar 2012 at 11:21