tue, 02-apr-2013, 19:08
A’s v Mariners

Yesterday I attempted to watch the A’s Opening Day game against the Mariners in the Oakland Coliseum. Unfortunately, it turns out the entire state of Alaska is within the blackout region for all Seattle Mariners games, regardless of where the Mariners are playing.

The A’s lost the game, so maybe I’m not too disappointed I didn’t see it, but for curiousity, let’s extend Major League Baseball’s Alaska blackout “logic” to the rest of the country and see what that looks like.

First, load in the locations of all Major League stadiums into a PostGIS database. There are a variety of sources for this on the Internet, but many are missing the more recent stadiums. I downloaded one and updated those that weren’t correct using information on Wikipedia.

CREATE TABLE stadiums (
   division text, opened integer, longitude numeric,
   latitude numeric, class text, team text,
   stadium text, league text, capacity integer
);
COPY stadiums FROM 'stadium_data.csv' CSV HEADER;

Turn the latitude and longitudes into geographic objects:

SELECT addgeometrycolumn('stadiums', 'geom_wgs84', 4326, 'POINT', 2);
CREATE SEQUENCE stadium_id_seq;
ALTER TABLE stadiums
   ADD COLUMN id integer default (nextval('stadium_id_seq'));
UPDATE stadiums
   SET geom_wgs84 = ST_SetSRID(
      ST_MakePoint(longitude, latitude), 4326
   );

Now load in a states polygon layer (which came from: http://www.arcgis.com/home/item.html?id=f7f805eb65eb4ab787a0a3e1116ca7e5):

$ shp2pgsql -s 4269 states.shp | psql -d stadiums

Calculate how far the farthest location in Alaska is to Safeco Field:

SELECT ST_MaxDistance(safeco, alaska) / 1609.344 AS miles
FROM (
   SELECT ST_Transform(geom_wgs84, 3338) AS safeco
   FROM stadiums WHERE stadium ~* 'safeco'
) AS foo, (
   SELECT ST_Transform(geom, 3338) AS alaska
   FROM states
   WHERE state_name ~* 'alaska'
) AS bar;

miles
------------------
 2467.67499410842

Yep. Folks in Alaska are supposed to travel 2,468 miles (3,971,338 meters, to be exact) in order to attend a game at Safeco Field (or farther if they’re playing elsewhere because the blackout rules aren’t just for home games anymore).

Now we add a 2,468 mile buffer around each of the 30 Major League stadiums to see what the blackout rules would look like if the rule for Alaska was applied everywhere:

SELECT addgeometrycolumn(
   'stadiums', 'ak_rules_buffer', 3338, 'POLYGON', 2);
UPDATE stadiums
SET ak_rules_buffer =
   ST_Buffer(ST_Transform(geom_wgs84, 3338), 3971338);

Here's a map showing what these buffers look like:

Or, shown another way, here’s the number of teams that would be blacked out for everyone in each state. Keep in mind that there are 30 teams in Major League Baseball so the states on the top of the list shouldn’t be able to watch any team if Alaska rules were applied evenly to the country:

Number of blacked out teams using Alaska blackout rules
Blacked out teams States
30 Alabama, Arkansas, Colorado, Illinois, Indiana, Iowa, Kansas, Kentucky, Louisiana, Michigan, Minnesota, Mississippi, Missouri, Nebraska, New Mexico, North Dakota, Ohio, Oklahoma, South Dakota, Tennessee, Texas, Utah, Wisconsin, Wyoming
29 Idaho, Montana, Arizona
28 District of Columbia, West Virginia
27 Georgia, South Carolina
26 Nevada
25 Pennsylvania, Florida, Vermont
24 Rhode Island, New Hampshire, Connecticut, Delaware, New Jersey, New York, North Carolina, Virginia, Maryland, Massachusetts
23 Maine
22 Oregon, Washington, California
1 Alaska
0 Hawaii

Based on this, Hawaii would be the place to live as they can actually watch all 30 teams! Except the blackout rules for Hawaii are even stupider than Alaska: they can’t watch any of the teams on the West Coast. Ouch!

This is stupid-crazy. It seems to me that a more appropriate rule might be buffers that represent a two hour drive, or whatever distance is considered “reasonable” for traveling to attend a game. When I lived in Davis California we drove to the Bay Area several times a season to watch the A’s or Giants play, and that’s about the farthest I’d expect someone to go in order to see a three hour baseball game.

This makes a much more reasonable map:

I’m sure there are other issues going on here, such as cable television deals and other sources of revenue that MLB is trying to protect. But for the fans, those who ultimately pay for this game, the current situation is idiotic. I don’t have access to cable television (and even if I did, I won’t buy it until it’s possible to pick and choose which stations I want to pay for), and I’m more than 2,000 miles from the nearest ballpark. My only avenue for enjoying the game is to pay MLB $130 for a MLB.TV subscription. Which I happily do. Unfortunately, this year I can’t watch my teams whenever they happen to play the Mariners.

tags: baseball  SQL  GIS 
tue, 15-jan-2013, 08:48

Over the past couple days in Fairbanks, there has been a strong flow of warm, moist air from the Pacific which culminated in a record (for January 14th) 0.22 inches of precipitation, most of which fell as rain. Nasty. Similar events happened in 2011 and in November 2010, which everyone will remember for the inch or more of ice that glazed the roads for the rest of the winter that year.

The question people always ask after a series of events like this is whether this is a new weather pattern (let’s hope not!) and whether it may be the result of global climate change (which I probably can’t answer).

To look at this, I examined the historical record for Fairbanks, searching for dates that met the following criteria:

  • At least six inches of snow on the ground
  • During the winter months (October through February)
  • Daily high temperature above freezing
  • Precipitation falling as rain

The last criteria isn’t part of the historical record, but we can guess the amount of rain by comparing the amount of snow (measured each day on a snow board that is cleared after measurement) with the amount of liquid precipitation gathered in a tube and melted, if necessary. In my experience, the ratio of snow to liquid precipitation is almost always less than 10 to 1 (meaning that 10 inches of snow melts down to less than an inch of liquid), so I’m looking for dates where the precipitation amount is greater than 10 times the snowfall for that date. I’m also estimating the amount of rain by subtracting (snow × 10) from the precipitation total.

Here’s the query:

SELECT dte, tmin_f, tmax_f, prcp_in, snow_in, rain_in,
       row_number() OVER (ORDER BY rain_in desc) AS rank
FROM (
    SELECT to_char(dte, 'YYYY-MM') AS dte, round(avg(tmin_f), 1) AS tmin_f,
           round(avg(tmax_f), 1) AS tmax_f, sum(prcp_in) AS prcp_in,
           sum(snow_in) AS snow_in, sum(rain_in) AS rain_in
    FROM (
        SELECT dte, tmin_f, tmax_f, prcp_in, snow_in, snwd_in,
               round(prcp_in - (snow_in / 10.0), 2) AS rain_in
        FROM get_ghcnd('Fairbanks Intl Ap')
        WHERE extract(month from dte) IN (10, 11, 12, 1, 2)
            AND snwd_in > 6
            AND tmax_f > 32
            AND prcp_in * 10 > snow_in
        ORDER BY dte
    ) AS foo
    GROUP BY to_char(dte, 'YYYY-MM')
) AS bar
ORDER BY dte;

And the results, ordered by the year and month of the event. None of the winter rain events stretched across a month boundary, so it was convenient to aggregate them this way (although 1937 is problematic as I mention below).

Winter rains, Fairbanks Airport station
Date Min Temp (°F) Max Temp (°F) Precip (in) Snow (in) “Rain” (in) Rank
1920-02 27.7 38.4 0.26 0.9 0.17 11
1931-01 12.0 33.1 0.13 0.0 0.13 12
1932-02 7.0 33.1 0.77 7.1 0.06 16
1933-11 25.0 41.0 0.11 0.0 0.11 14
1935-11 30.4 37.2 1.51 3.2 1.19 2
1936-11 30.0 37.0 0.44 0.0 0.44 5
1937-01 24.3 36.2 2.83 16.1 1.22 1
1941-02 28.0 42.1 0.02 0.0 0.02 23
1941-11 -2.9 33.1 0.20 0.9 0.11 15
1943-02 30.5 41.0 0.12 0.0 0.12 13
1944-02 21.5 36.5 0.65 2.9 0.36 7
1948-01 7.0 33.1 0.01 0.0 0.01 26
1957-01 30.9 35.1 0.03 0.0 0.03 22
1961-01 17.1 33.1 0.04 0.0 0.04 20
1963-01 22.5 35.1 0.56 0.7 0.49 4
1967-12 20.0 33.1 0.43 0.5 0.38 6
1970-02 10.9 43.0 0.05 0.0 0.05 17
1970-10 28.0 44.1 0.04 0.0 0.04 19
1970-12 5.0 36.0 0.43 2.4 0.19 9
1986-02 10.9 37.9 0.03 0.0 0.03 21
1989-02 24.1 37.0 0.40 3.8 0.02 24
2003-02 27.0 35.0 0.29 0.0 0.29 8
2006-02 17.1 42.1 0.06 0.1 0.05 18
2010-11 26.1 34.3 0.95 0.1 0.94 3
2011-12 26.1 46.9 0.03 0.2 0.01 25
2013-01 24.0 37.0 0.22 0.4 0.18 10

The 2010 event was had the third highest rainfall in the historical record; yesterday’s rain was the tenth highest. The January 1937 event is actually two events, one on the 10th and 11th and one on the 20th and 21st. If we split them up into two events, the 2010 rainfall amount is the second largest amount and the two January 1937 rainfalls come in third and tied for fifth, with November 1935 holding the record.

Grouping the events into decades, we get the following:

Winter rains by decade
Decade Rain events
1920s 1
1930s 6
1940s 5
1950s 1
1960s 3
1970s 3
1980s 2
1990s 0
2000s 2
2010s 3

Here’s a visualization of the same data:

I don’t think there’s evidence that what we’ve seen in the last few years is exceptional in the historical record, but it does seem like the frequency of winter rainfall does come in cycles, with a peak in the 30s and 40s, and something of a decline in the 80s and 90s. That we’ve already had three events in this decade, in just over two years, seems like a bad sign to me. I wonder if there are larger scale climatological phenomena that could help to explain the pattern shown here?

tags: weather  rain  winter  SQL 
mon, 24-dec-2012, 07:37

Thinking a little more about my previous post on the cold winter we’ve been experiencing for the past three months, it would be easier to understand the analysis if I reported average temperature for the period, rather than a cumulative value. I also realized that if there were missing days in any of the record, that this would skew the results against those years with missing data. So here’s a better query and the results through December 23rd:

SELECT year, round(cum_f / n, 2) AS avg_tempf,
       rank() OVER (ORDER BY cum_f / n) AS rank
FROM (
    SELECT year,
           CASE WHEN year = 2012 THEN round(sum(tavg_f), 2) - 261.5
                ELSE round(sum(tavg_f), 2)
           END AS cum_f,
           CASE WHEN year = 2012 THEN count(*) + 7
                ELSE count(*)
           END AS n
    FROM (
        SELECT extract(year from dte) AS year,
               extract(doy from dte) AS doy,
               (tmin_f + tmax_f) / 2.0 AS tavg_f
        FROM get_ghcnd('Fairbanks Intl Ap')
        ORDER BY year, doy
    ) AS foo
    WHERE doy between extract(doy from '2011-10-01'::date)
                  AND extract(doy from '2012-12-23'::date)
    GROUP BY year
    ORDER BY year
) AS bar
ORDER BY rank;
Average, minimum, maximum temperature (°F) for October 1st through December 23rd, ranked by average temperature over the period.
Rank Year Average temp (°F) Minimum temp (°F) Maximum temp (°F)
1 1917 -4.11 -52 57
2 1956 -3.99 -50 46
3 2012 -0.74 -47 61
4 1975 -0.01 -51 57
5 1977 0.54 -52 53
6 1942 0.92 -50 63
7 1946 0.95 -57 55
8 1961 1.33 -54 53
9 1996 1.75 -36 41
10 1966 1.92 -53 61

We’ve moved to third place on the list, with an average temperature over the period of less than zero Fahrenheit.

Here’s the same analysis for November and December (through the 23rd):

Average, minimum, maximum temperature (°F) for November 1st through December 23rd, ranked by average temperature over the period.
Rank Year Average temp (°F) Minimum temp (°F) Maximum temp (°F)
1 1917 -20.46 -52 21
2 1956 -16.81 -50 44
3 1946 -16.34 -57 33
4 1942 -14.63 -50 21
5 2012 -14.34 -47 31
6 1977 -13.86 -52 21
7 1975 -13.71 -51 30
8 1966 -10.91 -53 32
9 1906 -10.72 -50 37
10 1961 -10.05 -54 42

The Weather Service is forecasting some warmer weather starting today, so it’s likely we’ll begin to drop down the list, but it will be interesting to see where we wind up at the end of December.

Regardless, it’s been an exceptionally cold October, November and December in Fairbanks. We’re all just hanging on, hoping our houses, vehicles and other equipment continues to function until we get some warmer weather.

thu, 20-dec-2012, 19:16
-46

-45.8°F outside

It seems like it’s been cold for almost the entire winter this year, with the exception of the few days last week when we got more than 16 inches of snow. Unfortunately, it’s been hard to enjoy it, with daily high temperatures typically well below -20°F.

Let’s see how this winter ranks among the early-season Fairbanks winters going back to 1904. To get an estimate of how cold the winter is, I’m adding together all the daily average temperatures (in degrees Celsius) for each day from October 1st through yesterday. Lower values for this sum indicate colder winters.

Here’s the SQL query. The CASE WHEN stuff is to include the recent data that isn’t in the database I was querying.

SELECT year,
    CASE WHEN year = 2012 THEN cum_deg - 112 ELSE cum_deg END AS cum_deg,
    rank() OVER (
        ORDER BY CASE WHEN year = 2012 THEN cum_deg - 112 ELSE cum_deg END
    ) AS rank
FROM (
    SELECT year, round(sum(tavg_c) AS cum_deg
    FROM (
        SELECT extract(year from dte) AS year,
            extract(doy from dte) AS doy,
            tavg_c
        FROM ghcnd_obs
        WHERE station_id = 'USW00026411'
        ORDER BY year, doy
    ) AS foo
    WHERE doy between extract(doy from '2011-10-01'::date)
                  and extract(doy from '2012-12-19'::date)
    GROUP BY year ORDER BY year
) AS bar
ORDER by rank;

And the results: this has been the fifth coldest early-season winter since 1904.

Rank of cumulative degrees since 1904, thru December 19th
O/N/D rank year O/N/D cumulative °C N/D rank
1 1917 -1550 1
2 1956 -1545 4
3 1977 -1451 3
4 1975 -1444 5
5 2012 -1388 7
6 1946 -1380 2
7 1999 -1337 12
8 1966 -1305 9
9 1942 -1303 6
10 1935 -1298 10

In addition to the ranks for October through today (O/N/D rank in the table), the last column (N/D rank) shows the same calculation without October temperatures. It’s always a good idea to examine how well a relationship holds up when the interval is manipulated in order to see if the results are an artifact of the choice of period. In this case, the rankings change, but not dramatically.

Tonight we may cross -50°F for the first time this year at our house, but even without the very cold temperatures predicted (but not record cold) through the weekend, the start of the 2012/2013 winter has been exceptionally chilly.

tags: weather  winter  cold  SQL 
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: weather  Fairbanks  SQL  snow 

<< 0 1 2 >>
Meta Photolog Archives