On Wednesday I reported the results of my analysis examining the average date of first snow recorded at the Fairbanks Airport weather station. It was based on the snow_flag boolean field in the ISD database. In that post I mentioned that examining snow depth data might show the date on which permanent snow (snow that lasts all winter) first falls in Fairbanks. I’m calling this the first “true” snowfall of the season.

For this analysis I looked at the snow depth field in the ISD database for the Fairbanks station. The data was present for the years between 1973 and 1999, but isn’t in the database before that date. I’m not sure why it’s not in there after 1999, but luckily I’ve been collecting and archiving the data in the Fairbanks Daily Climate Summary (which includes a snow depth measurement) since late 2000. Combining those two data sets, I’ve got data for 27 years.

The SQL query I came up with to get the data from the data sets is a good estimate of what we’re interested in, but isn’t perfect because it only finds the date of first snow that lasts at least a week. In a place like Fairbanks where the turn to winter is so rapid and so dependent on the high albedo of snow cover, I think it’s close enough to the truth. Unfortunately, the query is brutally slow because it involves six (!) inner self-joins. The idea is to join the table containing snow depth data against itself, incrementing the date by one day at each join. The result set before the WHERE statement is the data for each date, plus the data for the six days following that date. The WHERE clause requires that snow depth on all those seven dates is above zero. This large query is a subquery of the main query which selects the earliest date found in each year.

There must be a better way to deal with conditions like this where we’re interested in the consecutive nature of the phenomenon, but I couldn’t figure out any other way to handle it in SQL, so here it is:

SELECT year, min(date) FROM
    (
        SELECT extract(year from a.dt) AS year,
            to_char(extract(month from a.dt), '00') ||
                '-' ||
                ltrim(to_char(extract(day from a.dt), '00')) AS date
        FROM isd_daily AS a
            INNER JOIN isd_daily AS b
                ON a.isd_id=b.isd_id AND
                    a.dt=b.dt - interval '1 day'
            INNER JOIN isd_daily AS c
                ON a.isd_id=c.isd_id AND
                    a.dt=c.dt - interval '2 days'
            INNER JOIN isd_daily AS d
                ON a.isd_id=d.isd_id AND
                    a.dt=d.dt - interval '3 day'
            INNER JOIN isd_daily AS e
                ON a.isd_id=e.isd_id AND
                    a.dt=e.dt - interval '4 day'
            INNER JOIN isd_daily AS f
                ON a.isd_id=f.isd_id AND
                    a.dt=f.dt - interval '5 day'
            INNER JOIN isd_daily AS g
                ON a.isd_id=g.isd_id AND
                    a.dt=g.dt - interval '6 day'
        WHERE a.isd_id = '702610-26411' AND
            a.snow_depth > 0 AND
            b.snow_depth > 0 AND
            c.snow_depth > 0 AND
            d.snow_depth > 0 AND
            e.snow_depth > 0 AND
            f.snow_depth > 0 AND
            g.snow_depth > 0 AND
            extract(month from a.dt) > 7
    ) AS snow_depth_conseq
GROUP BY year
ORDER BY year;

See what I mean? It’s pretty ugly. Running the result through the same R script as in my previous snowfall post yields this plot:

Between 1973 and 2008 we’ve gotten snow lasting the whole winter starting as early as September 12th (that was the infamous 1992), and as late as the first of November (1976). The median date is October 13th, which matches my impression. Now that the leaves have largely fallen off the trees, I’m hoping we get our first true snowfall on the early end of the distribution. We’ve still got a few things to take care of (a couple new dog houses, insulating the repaired septic line, etc.), but once those are done, I’m ready for the Creek to freeze and snow to blanket the trails.

I just got back from walking Nika and Piper on the Creek. I’d looked at my GPS track from yesterday’s walk on the Creek and saw an obvious shortcut (from point A to B on the map) to cut off some distance. My objective was to take the Creek out to a section line (at points D and E—click on the image to see a full size version) that I also saw from the satellite imagery for the area, and wanted a way to make the route shorter. Yesterday’s walk was more than five miles, even though a raven could have covered the distance in a little over a half mile.

We walked down the Creek, came to the start of the shortcut at point A, walked overland through the forest to point B back on the Creek, and I immediately turned the wrong direction. I’d already walked about halfway back to point A before I realized I went the wrong way. Next time, turn left!

It is amazing how disorienting the Creek is, even with a GPS. Because it winds back and forth so much, even if you know where you’re going and can see it on the little map your GPS is showing you, it’s really difficult to tell if you are getting closer to your objective. The good thing is that the Creek only goes two ways. If you went the wrong way, just turn around and go back.

The blueberries on the left cost $8 at the Farmer’s Market this morning. The blueberries on the right cost me two hours of hiking around the trails with Nika and Piper. We’ve got a lot of blueberry bushes on our property, but it’s been so cloudy and wet that ours didn’t get enough sun to produce very many berries. So I had to go farther afield.

It was a grand time, except for when Piper refused to cross over a two-foot wide area of deep water. She’s extremely food motivated, but no amount of coaxing would get her to jump across once she’d discovered there was deep water in between her and the treat. All the while, Nika kept jumping in and swimming around while Piper looked eagerly at the treat in my hand, trying to figure out how to get it without jumping or swimming. I finally had to step into the deep water (filling my left knee-high rubber boot with water) and help her across. She could have jumped the gap easily, but I don’t think there is a treat large enough to overcome her dislike of water.

Along the way we saw a flicker, four spruce grouse, gray jays and lots of smaller birds I couldn’t identify. I’ve still got the wonderful smell of Ledum palustre on my clothes.

Tomorrow morning: blueberry pancakes!

I wish there were stronger ways than words to express how ruinous George W. Bush has been to our country. From McSweeney’s Where to Invade Next:

So I came back to see him a few weeks later [a few weeks after 9/11], and by that time we were bombing in Afghanistan. I said, ‘Are we still going to war with Iraq?’ And he said, ‘Oh, it’s worse than that.’ He reached over on his desk. He picked up a piece of paper. And he said, ‘I just got this down from upstairs’—meaning the Secretary of Defense’s Office—‘today.’ And he said, ‘This is a memo that describes how we’re going to take out seven countries in five years.’
—General Wesley Clark.

The seven countries Bush, Cheney and Rumsfeld wanted to “take out” are: Iran, Pakistan, Uzbekistan, Venezuela, Syria, Sudan and North Korea. This book, edited by Stephen Elliot lays out the case the Bush Administration would like to have made had their plans in Iraq not gone so horribly wrong (“Mission Accomplished”, jackass). It’s quite sobering reading, because these really are bad places, and in the case of Sudan, something really needs to be done. But most of these countries (all of them?) were made far more dangerous by the invasions of Iraq and Afghanistan, and it’s clear (to me anyway) that this is pretty much our fault. We elected Bush (sort of). Frigging twice (kinda). We elected the Congress that rubber stamped everything he did. We allowed the media to become a corporate shill for the Administration. And now all of the good will we might have had to effect change in the world is lost. To help in Sudan.

I took Nika out skijoring both days this weekend. She’s still doing really well, although she wasn’t pulling as hard on the way back as she did last weekend. We had a lot of wind on Friday and it blew debris all over the trail, so not only did that slow us down, but there’s a lot more distractions for Nika. Andrea took a few photos as we came back to the house. If you click on the images you can see larger versions. There’s a sled dog team behind me in the first photo.