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 saw this quote on BoingBoing from Michael Massing of the Columbia Journalism Review.

Katie Couric’s annual salary is more than the entire annual budgets of NPR’s Morning Edition and All Things Considered combined. Couric’s salary comes to an estimated $15 million a year; NPR spends $6 million a year on its morning show and $5 million on its afternoon one. NPR has seventeen foreign bureaus (which costs it another $9.4 million a year); CBS has twelve. Few figures, I think, better capture the absurd financial structure of the network news.

Pretty unbelievable. Imagine how much actual news CBS could report if they didn’t have to sink all that money into the mouthpiece of the drivel they do report on.

We got our first dusting of snow last night. It stuck around until after noon, allowing me to take the photo on the right when I went for a walk with Nika around the peat bog. You can really tell where the permafrost is by the thick layer of insulating moss that keeps the ground frozen, and is keeping the snow from melting in the photo.

Every year when the first snow falls it seems like it’s earlier than the last, and there’s usually some discussion at the office about how short the summer turned out to be. The early snows of 1992 that knocked out power for days all over town are also normally mentioned. I decided to look and see if I had some data that could place this year’s first snowfall in a historical context.

One of the few free^† long-term weather datasets that’s available from the National Climate Data Center is the Integrated Surface Dataset (ISD), which contains daily weather observations for more than 20,000 stations. The Fairbanks Airport station has been in operation for more than 100 years, but it moved in 1946, so I only used data from 1946–2008. In addition to a series of numerical observations (minimum and maximum temperature, pressure, wind speed, etc.), the dataset contains several fields used to indicate whether a particular phenomenon was observed during that day. One of them, snow_flag, is defined as: “True indicates there was a report of snow or ice pellets during the day.”

That’s perfect. Snow depth is another parameter I considered, but this data wasn’t collected until the mid-70s, and it doesn’t really help us answer the question because most of the time the first snowfall of the year doesn’t last long enough to be recorded as snow on the ground.

Here’s the SQL query to find the earliest snowfall date for each year for the Fairbanks Airport station:

SELECT year, min(date) FROM (
        SELECT extract(year from dt) AS year,
            to_char(extract(month from dt), '00') ||
                '-' ||
                ltrim(to_char(extract(day from dt), '00')) AS date,
            snow_flag
        FROM isd_daily
        WHERE isd_id = '702610-26411'
            AND extract(month from dt) > 7
            AND snow_flag = 't'
    ) AS snow_flag_sub
GROUP BY year
ORDER BY year;

Mix in a little R:

fs <- read.table("first_snow_mm-dd", header=TRUE, row.names=1)
fs$date<-as.Date(fs$date, "%m-%d")
png("first_snow_mm-dd.png", height=500, width=500, units="px", pointsize=12)
hist(fs$date, breaks="weeks", labels=FALSE,
    xlab="Date of first snowfall",
    main="First snowfall reported, Fairbanks Airport (PAFA) station",
    plot=TRUE, freq=TRUE,
    ylim=c(0, 20), col="gray60")
text(as.Date("2009-09-23"), 19, "⇦ 2009", srt=90, col="darkred")
dev.off()

And you get this plot:

You can see from the plot that the first snowfall comes somewhere between August 3rd and October 26th, with the week of September 21st being the most common. So we’re right on schedule this year.

Another analysis that I’ve been meaning to do is to find the average date when the snow that falls lasts the entire winter. Since I’ve been in Fairbanks, my estimate of this date is the second week of October, but I’ve never actually looked it up to see if that’s true or not. Unfortunately, this requires good snow depth data, and the ISD dataset doesn’t have snow depth for Fairbanks prior to 1975. It’s also a bit more complicated than looking for the earliest snow_flag = 't' because you need to examine future rows to know if the snow depth observation you’re examining lasted more than a few days.

^†Why isn’t all the data collected by the Weather Service freely available? Public money was used to collect, analyze, and archive it, so I think it should be made available to the public that paid for it.

It’s been a few days since Andrea finished her first marathon, and I’ve had a chance to clean up the photos (I didn’t realize how dark they were while I was out, blogging from my iPhone). They’re part of a Picasa web gallery here. Andrea has written up her experiences on her blog.

For my part, I had a good time supporting Andrea, except for my time on top of Ester Dome waiting for her to come down from the out and back. When she left me, her legs were tired, but she wasn’t injured. Shortly after that, however, the teammates ahead of her in the race started coming up to me to warn me that her knee was hurt and that she might not be able to finish the marathon. It was a long, lonely wait wondering how she was doing above me.

Despite that, watching her cross the finish line with three of her teammates, and all the rest of her team and coaches whooping, cheering, and clapping was awesome.