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 
fri, 05-mar-2010, 16:44

Overflow

Overflow near DNR pond

I took Friday off from work again this week and brewed Overflow Old Ale. I drained the chilled wort directly onto the yeast cake from last week’s brewing effort, so I should get a nice fast start from the yeast. The recipe was supposed to be a high alcohol (7%) lightly hopped ale, but instead of an expected 70% efficiency at extracting sugars from the grain, I wound up at 57%. That’s the lowest efficiency I’ve ever gotten. I wouldn’t mind so much if they were consistent, but they’re all over the map, and I don’t know what is causing the variation. Same base grains, same procedure and equipment, same thermometer, etc. It’s a mystery.

DNR pond location

Location of photo

The good news is that nothing went wrong, and beer is beer regardless of whether it turned into the batch you were planning to brew. I’m sure I’ll enjoy drinking it at least as much as I did brewing it.

The batch is named for the overflow that’s currently moving across the top of the frozen Creek. I took advantage of it and used it in my counterflow chiller to bring the boiling wort down to pitching temperatures in 10–15 minutes. The water is at least a foot deep, and I don’t think it’s been warm enough for it to be a natural phenomenon. Last year’s overflow event was rumored to be a large discharge from the mine several miles upstream from our house, and this event looks similar. Despite providing chiller water, it’s a drag because it’ll take at least a week for it to freeze solid again and until it snows it’ll be treacherous to walk on. I had hoped the overflow would have flooded the DNR pond again, as it was really fun ice skating on it last year, but the floodwaters aren’t high enough to cross onto the pond. The photo at the top is taken where the Creek passes the pond.

The photo on the right shows the Google Maps image of the location I took the photo using the iPhone web app I wrote. This morning I added some code to draw a blueish buffer around the GPS location that’s sized using the reported GPS accuracy. It also scales the map so that the entire property fits on the screen (I zoomed in before taking the photo on the right). I think the app is pretty much finished, except I want to add Fish and Game Management Units to it. Unfortunately, they don’t have GIS files for the smaller Management Areas, so it won’t be as useful as it could be. You can see I’ve drained my battery playing with it!

tags: GIS  GPS  iPhone 
mon, 01-mar-2010, 18:24

Who owns it?

Updated web app
http://swingleydev.com/gis/loc.html
(for GPS enabled phones within the Fairbanks North Star Borough)

Now that my two year web hosting contract with BlueHost is coming up, I’ve decided to migrate over to Linode. It’s about twice as expensive, but you get your own Xen virtual machine to do with as you want. With BlueHost I wound up needing to compile a whole bunch of software packages (gnuplot, remind, spatialite, a variety of Python modules, etc.) just to get my site to work. And I couldn’t upgrade PostgreSQL, which left me unable to use PostGIS. With my Linode, I put the latest Ubuntu on it, and was able to install all the software I need.

As mentioned, PostGIS is one of the benefits of this. Instead of loading the Fairbanks North Star Borough tax parcel database into spatialite and using a CGI program to extract data, I’m able to query the same data in PostGIS directly from PHP. That means I can do all sorts of cool stuff with the data I’m extracting.

Here, I am using the Google Maps API to draw the property polygon and put a marker at the location from the iPhone’s GPS. You can see the result on the right, taken while eating lunch at the office. If you’ve got a GPS-enabled smart device and you’re in the Fairbanks North Star Borough, the link is http://swingleydev.com/gis/loc.html.

The code that drives the map is at the bottom of this post. The complicated query in the middle of it gets the outermost ring from the polygon, and dumps each vertex as a separate row. This section is in the middle of the Google Maps JavaScript that builds the overlay polygon. Note that this doesn’t handle the few properties in the database that have multiple polygons in their geometry, and it ignores the holes that might be present (if a small chunk out of the middle of a property was sold, for example). I expect these cases are pretty infrequent.

One enhancement would be to include a circular buffer around the point marker so you could get a sense of where the GPS accuracy places you on the map. This is complicated with Google because of the custom projection they’re using. I think it’s called “Google Mercator,” but spatialreference.org lists several projections that look like they might be compatible. I’ll have to give those projections a try to see if I can make a projected circular buffer that looks like a circle. It would also be nice to set the map extent to match the extent of the parcel you’re inside. I don’t know if that’s part of the Google Maps API or not.

Here’s the code:

<script type="text/javascript">
function initialize() {
  if (GBrowserIsCompatible()) {
      var map = new GMap2(document.getElementById("map_canvas"));
      var mapControl = new GMapTypeControl();
      map.addControl(mapControl);
      map.setCenter(new GLatLng(<?php echo $lat; ?>, <?php echo $lon; ?>), 14);
      var polygon = new GPolygon([
        <?php
            $query = "
SELECT
    ST_X(ST_PointN(ST_ExteriorRing(ST_GeometryN(wkb_geometry, 1)),
        generate_series(1, ST_NPoints(ST_ExteriorRing(ST_GeometryN(wkb_geometry, 1))))))
        AS x,
    ST_Y(ST_PointN(ST_ExteriorRing(ST_GeometryN(wkb_geometry, 1)),
        generate_series(1, ST_NPoints(ST_ExteriorRing(ST_GeometryN(wkb_geometry, 1))))))
        AS y
FROM tax_parcels
WHERE ST_Intersects(wkb_geometry, ST_SetSRID(ST_MakePoint($lon, $lat), 4326));";
            $result = pg_query($query);
            $num_rows = pg_num_rows($result);
            for ($i = 0; $i < $num_rows - 1; $i++) {
                $row = pg_fetch_row($result);
                list($vlon, $vlat) = $row;
                echo "new GLatLng($vlat, $vlon),";
            }
            $row = pg_fetch_row($result);
            list($vlon, $vlat) = $row;
            echo "new GLatLng($vlat, $vlon)";
            pg_free_result($result);
        ?>
      ], "#f33f00", 2, 1, "#ff0000", 0.2);
    map.addOverlay(polygon);
    var point = new GLatLng(<?php echo $lat; ?>, <?php echo $lon; ?>);
    map.addOverlay(new GMarker(point));
  }
}
</script>

tags: GIS  GPS  iPhone 
thu, 02-jul-2009, 19:45

The company I work for (ABR, Inc.) pays it’s employees to use alternative transportation—bicycle, walk, run, ski, etc.—to get to work. It’s not a lot of money, but human behavior being what it is, even a small incentive really works. I’ve been riding my bicycle to work three or four times a week, carrying a hand-held GPS (Garmin eTrex Vista Cx) along with me and logging the data into spatially aware databases (PostGIS and spatialite). You can see a map of last week’s data on my GIS page.

One problem with my GPS is that I get really inaccurate elevation data, and as anyone who has ridden a bicycle knows, elevation changes are really important to how fast you can ride. I’ve noticed that I ride much faster going home than on my way to work, and had concluded (until examining the data…) that it must be an uphill ride to work.

But how to fix the elevation data? One approach would be to download the new global digital elevation model (DEM) that’s based on data from Japan’s ASTER satellite and use this to find the corrected elevation for GPS trackpoint. I’ll probably try this at some point just to see how well the DEM matches my data. But the approach I wound up using is to generate an “average” track based on all my trips to and from work.

There’s probably a way to do this with a single SQL statement, but I couldn’t figure out how, so I did the point iteration in Python. The first SQL query takes an existing trip and segments the trip into a series of points. I chose a maximum interval of 50 meters because that was approximately how often the GPS reports data at the speeds I’m normally travelling. Here’s the PostGIS SQL:

SELECT ST_AsText(ST_PointN(
  segpoints.points,
  generate_series(1, ST_NPoints(segpoints.points)))
  )
FROM (
  SELECT
    ST_GeometryN(
        ST_Segmentize(track_utm, 50),
        generate_series(1, ST_NumGeometries(ST_Segmentize(track_utm, 50)))
    )
  AS points FROM tracks WHERE tid=67
  ) AS segpoints;

This yields a series of points (in WKT format) along the track.

My Python script takes each of these points and finds the average X, Y, and Z (elevation) coordinates of all points within 50 meters of the point. Here’s the query:

SELECT count(*), avg(lat), avg(lon), avg(ele)*3.2808399 AS avg_ft
FROM points
WHERE ST_Within(
  ST_SetSRID(ST_GeomFromText(POINT_WKT), 32606),
  ST_Buffer(point_utm, 50)
);

And the results:

Elevation and speed, Jul 1 ride to ABR

The red squares show the elevations along my route, and the green markers show the speed I was traveling at that point. In this case, I’m riding to work, so home is on the left and ABR is on the right. The big hill on the left is Miller Hill Road. In general, you can see that a ride to work involves going over Miller Hill, then a gradual climb along Sheep Creek Road to the intersection with Ester Dome Road (the little hitch in this section is the hairpin curve before Anne’s Greenhouses where so many people go off the road in winter…), followed by a steeper descent to ABR. My speed tracks the elevation changes fairly closely. I’ll be interested in seeing how these plots change over time as my legs and cardiovascular system strengthens. Will the green points rise uniformly, or will I be able to see improvements in individual aspects of my performance such as hill climbing?

One final point: the elevation at home and ABR are essentially the same. My conclusion after all this is that elevation can’t account for why my rides home are so much faster (1.28 mph, on average). Wind? Cold mornings? Excitement to get home?

For reference, here's my ride home on the same day:

Elevation and speed, Jul 1 ride from ABR

tags: ABR  bicycle  cycling  elevation  GIS  GPS  speed 
sat, 29-nov-2008, 10:20

GPS track

GPS track, Goldstream Creek

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.

tags: GIS  Goldstream Creek  GPS  Nika  Piper 
Meta Photolog Archives