Fairbanks had some very hot weather earlier this week, including breaking a high temperature record at the airport on Wednesday afternoon. We got up to 94°F here on the Creek, but what was worse was that the low temperature on Wednesday night was 60°F, too warm to cool the house much. We were tempted to sleep out in the back cabin because the house was so warm.

It’s been cooler in the last couple days. But how much cooler?

The new version of PostgreSQL (8.4) has support for window functions, which make it easier to answer questions like this. Window functions allow you to calculate aggregates (average, sum, etc.) at one level, while display the data from another level. In this case, I want to compare the hourly average temperatures over the last 24 hours with the overall hourly average temperature over the past week. Without window functions, I’d need to combine a query that yields the hourly average temperature over the last 24 hours with a query that calculates overall seven-day hourly average temperatures. And if I want the difference between the two, the first two queries become a subquery of a third query.

Here’s the query:

SELECT dt, t_avg,
    seven_day_avg::numeric(4,1),
    (t_avg - seven_day_avg)::numeric(4,1) AS anomaly
FROM (
    SELECT dt, t_avg::numeric(4,1),
        avg(t_avg::numeric(4,1)) OVER (PARTITION BY extract(hour from dt)) AS seven_day_avg
    FROM hourly WHERE dt > current_timestamp - interval ’7 days’
    ) AS sevenday
WHERE dt > current_timestamp - interval ’24 hours’
ORDER BY dt;

And the result for the last 24 hours of temperature data:

         dt       | t_avg | seven_day_avg | anomaly
------------------+-------+---------------+---------
 2009-07-10 11:00 |  67.1 |          72.3 |    -5.2
 2009-07-10 12:00 |  70.8 |          75.8 |    -5.0
 2009-07-10 13:00 |  72.9 |          77.4 |    -4.5
 2009-07-10 14:00 |  74.1 |          78.5 |    -4.4
 2009-07-10 15:00 |  74.6 |          80.2 |    -5.6
 2009-07-10 16:00 |  75.9 |          80.4 |    -4.5
 2009-07-10 17:00 |  76.1 |          81.0 |    -4.9
 2009-07-10 18:00 |  76.9 |          80.4 |    -3.5
 2009-07-10 19:00 |  76.5 |          79.3 |    -2.8
 2009-07-10 20:00 |  73.1 |          77.0 |    -3.9
 2009-07-10 21:00 |  69.1 |          73.5 |    -4.4
 2009-07-10 22:00 |  63.7 |          68.2 |    -4.5
 2009-07-10 23:00 |  57.6 |          62.3 |    -4.7
 2009-07-11 00:00 |  52.1 |          56.5 |    -4.4
 2009-07-11 01:00 |  48.5 |          52.6 |    -4.1
 2009-07-11 02:00 |  45.5 |          49.3 |    -3.8
 2009-07-11 03:00 |  43.4 |          47.9 |    -4.5
 2009-07-11 04:00 |  42.2 |          47.1 |    -4.9
 2009-07-11 05:00 |  44.8 |          47.6 |    -2.8
 2009-07-11 06:00 |  47.5 |          49.7 |    -2.2
 2009-07-11 07:00 |  51.2 |          53.8 |    -2.6
 2009-07-11 08:00 |  55.3 |          59.2 |    -3.9
 2009-07-11 09:00 |  60.4 |          64.0 |    -3.6
 2009-07-11 10:00 |  65.5 |          68.3 |    -2.8

Conclusion? It’s been several degrees cooler in the last 24 hours compared with the last seven days.

And window functions are groovy.

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:

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:

The Arduino weather station is coming along. Today I integrated a barometric pressure sensor and a calibrated light sensor into the hardware and software. The pressure sensor is a SPC1000-D01, which is sensitive to pressure changes as small as the difference within a 9 cm column of air. It can be used in several different configurations depending on how often you want to get a reading, and how much precision it reports. At the best frequency and precision, the sensor was drawing enough current that I was having trouble adding other sensors to the Arduino, so I’m running it in it’s lowest power configuration (where the chip is in a low power standby mode until you ask it for a reading). The light sensor is a TSL230R, which is also very accurate. It has three different sensitivity levels that can be changed at any time, which means that when the light intensity gets too high or low, the Arduino changes the sensitivity of the sensor to keep the results within range. The only down side to these chips are they require a lot of digital pins (four for the pressure sensor, five for the light sensor).

Here’s a circuit diagram of the current setup. If you click the image, you can view a PDF version. There’s also an Arduino xcircuit diagram. This is the first circuit I’ve ever tried to draw, so it probably isn’t as well laid-out at it should be.

The code is also available. It’s not pretty, but it works. If you do download it, the code include a simple analog light sensor (light0), which isn’t shown on the wiring diagram. I was using it before I got the new sensor set up, and haven’t removed it from the hardware yet.

My Arduino-based weather station has improved in the last week to the point where it’s delivering useful data. I’ve got five DS18B20 temperature sensors hooked up and reporting their temperatures to my Goldstream Creek Weather Pages. In the image on the top of the post, the Arduino is on the left, the indoor sensors are in the middle, and the outdoor cluster of temperature sensors is on the right (under a yogurt container). At the moment, I’m just averaging the data from the two sensors that are inside and the three outside, but eventually I’d like to place each sensor in a different location. I’m not sure where the outdoor sensors will go, but one of the indoor sensors will move up to the second floor of the house. Twisted-pair telephone cables carry the data and power to the sensor clusters.

I’ve also connected a cheap photoresistor to the sensor cluster in the house. I’m converting the resistance it reports to a value from 0 (dark) to 100 (light). It’s in a place that rarely gets direct sunlight, so the values should give some idea of when the sun is up, and whether it’s sunny or cloudy. I have a more sophisticated light sensor (TSL203R) and a barometric pressure sensor (SCP1000), but haven’t tried them yet. I’ve run out of breadboards, and now that my Arduino is actively delivering data, I’m a bit less inclined to experiment.

I think it’ll be interesting to compare the outdoor temperatures from behind the house with the temperature coming from the Rainwise weather station atop the dog yard gate. Also, there ought to be an obvious relationship between outdoor temperature, light, and indoor temperature, since it seems like the house heats up much more quickly when the sun is beating on the south facing side. If I was really ambitious, the windows and window-shades could be connected to the system: it’d open the windows when it detected cooler temperatures outside than inside, and close the shades in whichever window is getting direct sunlight.

Once the design and software has stabilized, I’ll be sure to post the schematics and code.

Yesterday, my Arduino board showed up in the mail. It’s pictured in the photo alongside a Canadian dollar from when I drove up to Alaska. I ordered it with a prototyping shield and Getting Started with Arduino, a book written by one of the developers of the Arduino platform. The book is a great introduction to the hardware and the software you use to control the gadget. To fully appreciate the book, you’ll want to get a few extra extra components so you can try the experiments in the book. These include a breadboard, solid core wire for the breadboard, a button, light sensor, LEDs, and a variety pack of resistors.

What is it? It’s a very simple computer connected to fourteen digital input / output pins, six analog input / output pins, and a USB communication system so you can read data and put programs on the system. In other words, you can read data from the environment, control things based on that data, and send data back to a computer all on the same small board. I’m interested in using the platform to build my own weather station to complement the professional station we have. But more generically what’s cool about it is that it provides an inexpensive (my board was $35) platform for “programming” the world. Instead of buying a gadget off the shelf that someone else has built to perform a task they think you’ll want, you can build something to do exactly what you want.

In the real world.

All I’ve done with it so far is to control the flashing of the pair of LEDs that came with the prototyping shield. I know that doesn’t sound very exciting, but if you can control LEDs and make them go on and off, or vary their intensity, that means you can control anything that takes an on / off, or variable input. For example, a set of fans in your house whose speed and direction is controlled by the relationship between the temperature in the house, in the attic, and outside. Or controlling a sprinkler system that senses how wet the ground is, reads the weather forecast from the Weather Service and only turns on the sprinklers if no rain is expected. Or a system that operates a dog door, sensing which dog has approached by reading an RFID tag on their collar and then opening or locking the door depending on whether they are allowed in or out.

You get the idea. Combining any of the wide variety of input sensors available with the ability to do things based on logic that you control is a really powerful tool. And the low cost of the board makes it reasonable to experiment and try things out until you get a system working the way you want.

My initial project will include a series of temperature sensors that I’ll put in different places in the house and outside (maybe even in a dog house?), a light sensor and a barometric pressure sensor. Of course, you can buy small weather stations that have all of these things in them, but most can’t read temperatures below -40°F, and most only have one sensor. Best of all, I’ll plug mine into a “real” computer via USB and record the data as it comes in.