1. Find a list of lakes in CA. Find the USGS GNIS system, and use it to...
2. Download the GNIS list of lakes, import into a Fusion Table (“Lakes in CA”), convert their LAT, LONG into decimal lat/long.
3. Find a list of the stocked lakes in CA. Find the CDFW list.
4. Download the CDFW list of stocked lakes into a spreadsheet (“Fish planting”)
5. Merge the “Fishplanting” table with “Lakes in CA” to make “Merge of Lakes…” FT
6. Check the data. Create a map of all the Horseshoe Lakes. Notice that the Horseshoe Lakes are all in the wrong places. Why? Make a plan for the next post...
7. Work backwards to discover that the lat/long conversion in step 2 was wrong. Fix that up and re-merge the tables together.
8. Map the Horseshoe Lakes again, and check that they’re correct. (They are now.)
9. Check ALL of the Horseshoe Lakes in the table and find that the one in Alameda county is missing—why?
10. Discover that it’s actually a reservoir, and not in the master list of lakes from GNIS (step 2).
11. Go back to GNIS and import a list of all the reservoirs in CA, and re-merge that table into the master list of “CA lakes and reservoirs with extracted lat/longs” and re-merge everything together.
12. Now, in the newly re-merged table, notice that all of the Horseshoe Lakes have the same lat/long. Now what? Why is this happening?
13. By looking over the data tables, find that there’s only one Horseshoe Lake that has been planted, the one in Alameda county. Figure out that the fusing step overwrites ALL of the lakes names “Horseshoe Lake” with the same lat/long value because the lake names are not unique.
|Note that I've hidden a lot of the extra columns that we don't care about.|
So... after all this analysis, we find that Little Rob and Big Jim probably don't actually need theAcetazolamide pills. (I'm sure you did a search on Acetazolamide and found that it's a commonly used prescription drug for high altitude sickness.)
|Map of Northern California showing Lake Almanor between Lassen and Tahoe National Forests|
There are a LOT of lessons here, and I'll discuss some of them in more detail tomorrow.
But the two biggest lessons are the ones I hope you take away, even if you didn't go through all of the Fusion Table details.