As you probably know, on Wednesday morning a 908-foot cargo ship, the Cosco Busan, ran into one of the San Francisco Bay Bridge towers, creating the worst oil spill in more than a decade in San Francisco Bay.

There’s all sorts of questions being raised about the speed of response. In time, I suspect we’ll learn a lot. For now, speculation on just how quickly the response was mustered, whether it could have been done more quickly, and what caused any delays is just that—speculation.

Even more puzzling is how this could happen to begin with. There’s a huge opening between the bridge towers—this is not a tight fit, even for a huge cargo ship.

It just so happens that one of my sites, BoatingSF.com, tracks ship movements on the bay. Anyone who looked at my real-time ship tracking page within an hour of the accident could have seen an instant replay of the ship’s track. And it should have been reasonably simple for me to access the historical data.

My perfect storm of server data ugliness

This data comes from two AIS receivers I operate, which receive VHF signals that all commercial ships are required to send that encode their position, speed, destination, name, and dimensions. These reports, which arrive at the rate of a few per minute, are streamed up to my server, where I decode them with some custom PHP code (this predates my involvement with Ruby and Rails) and stuff them into a database. Every five minutes, a cron job extracts a summary from the database and generates an XML file. The web page has a Flash movie that reads this XML file to control the animation.

Unfortunately, for performance reasons, I don’t keep decoded position information that’s more than one hour old. I’m going to rearchitect the solution to make this possible, but when I designed this almost two years ago, it was all I could do to get it working, and other projects got in the way of further optimization.

I do archive the raw AIS data stream, so I can go back and process it later to get at historical data. Several private and government agencies have used this data for various kinds of analysis projects. Until recently, I stored this raw stream in the database.

A couple weeks ago, something went wrong, and my simplistic scheme began to torment me. My simple database configuration on an old VPS account didn’t deal well with tens of millions of records.

I had the system set up to send me an email when a database failure occurred—and I started getting 100,000 such emails a day!

There’s another article to be written here, but suffice it to say that you shouldn’t do this (and I don’t any more)—write the errors to a log file, use logrotate or somesuch to keep the files from getting too big, and then use something like logwatch to warn you when the logs have errors in them.

The fix creates a new problem

In my hurry to stop the mail deluge, I changed the code to put the raw AIS stream into a log file instead of into the database. And in the rush, I forgot that the raw AIS data lacks a built-in time stamp. So when I went to dig out the data that would show the Cosco Busan accident, I found that I had no timestamps for any of the position reports! This meant I couldn’t just look for 8:30 am Wednesday but had to analyze ship movements to find the accident.

I also had to write new code to pull the reports from the log file, decode them, and stuff them into the database for further analysis. And I wanted a different zoom region for the display, which took additional work.

About 12 hours later, I had an animation of the accident completed. It doesn’t show actual time, since I didn’t have any timestamps to work with, but it animates on the assumption that the pace of ship reports is roughly constant (which it should be).

I had expected that the ship would have been heading for the space between the towers, and veered a bit off course. The reality is that the ship was going nearly parallel to the bridge, until it turned sharply and headed straight for the center tower! And there was a tug following closely behind. Unless there was a catastrophic steering failure, which seems unlikely given that the ship continued on under apparently good control, there’s some people with a lot of explaining to do.

Next up: a new architecture

This debacle (my server’s, not the ship’s) has spurred me to begin thinking about a new architecture for the system. I want to be able to pull any past window of time, and zoom in on any region, without custom programming.

Perhaps I’ll move it over to Ruby while I’m at it. One of the reasons it too me so long to create the Cosco Busan animation is that it had been almost a year since I had touched the PHP code, and it is an ugly thing! It is hard to remember to put semicolons at the end of every line, and empty parentheses after function calls that take no arguments, and so forth, now that I’m accustomed to a language that doesn’t have such requirements.

Making it even more complex is the convoluted Flash code that creates the animation, which requires dealing in yet another language and the vagaries of the Flash timeline interface. I’m not sure I see a way out of having the Flash code, but at least I can get rid of the PHP.