mon, 19-nov-2012, 19:55
Footprints frozen in the Creek

Footprints frozen in the Creek

Reading today’s weather discussion from the Weather Service, they state:

LITTLE OR NO SNOWFALL IS EXPECTED IN THE FORECAST AREA FOR THE NEXT WEEK.

The last time it snowed was November 11th, so if that does happen, it will be at least 15 days without snow. That seems unusual for Fairbanks, so I checked it out.

Finding the lengths of consecutive events is something I’ve wondered how to do in SQL for some time, but it’s not all that difficult if you can get a listing of just the dates where the event (snowfall) happens. Once you’ve got that listing, you can use window functions to calculate the intervals between dates (rows), eliminate those that don’t matter, and rank them.

For this exercise, I’m looking for days with more than 0.1 inches of snow where the maximum temperature was below 10°C. And I exclude any interval where the end date is after March. Without this exclusion I’d get a bunch of really long intervals between the last snowfall of the year, and the first from the next year.

Here’s the SQL (somewhat simplified), using the GHCN-Daily database for the Fairbanks airport station:

SELECT * FROM (
    SELECT dte AS start,
        LEAD(dte) OVER (ORDER BY dte) AS end,
        LEAD(dte) OVER (ORDER BY dte) - dte AS interv
    FROM (
        SELECT dte
        FROM ghcnd_obs
        WHERE station_id = 'USW00026411'
            AND tmax < 10.0
            AND snow > 0
    ) AS foo
) AS bar
WHERE extract(month from foo.end) < 4
    AND interv > 6
ORDER BY interv DESC;

Here’s the top-8 longest periods:

Start End Days
1952‑12‑01 1953‑01‑19 49
1978‑02‑08 1978‑03‑16 36
1968‑02‑23 1968‑03‑28 34
1969‑11‑30 1970‑01‑02 33
1959‑01‑02 1959‑02‑02 31
1979‑02‑01 1979‑03‑03 30
2011‑02‑26 2011‑03‑27 29
1950‑02‑02 1950‑03‑03 29

Kinda scary that there have been periods where no snow fell for more than a month!

Here’s how many times various snow-free periods longer than a week have come since 1948:

Days Count
7 39
10 32
9 30
8 23
12 17
11 17
13 12
18 10
15 8
14 8

We can add one more to the 8-day category as of midnight tonight.

tags: Fairbanks  snow  SQL  weather 
Meta Photolog Archives