I'm not sure how many of you out there were proximal to the NYC steam pipe explosion, but I from what I hear it was pretty traumatizing. The damage it effected of course drew morbid parallels to other, premeditated explosions, but since I grew up hearing engine room horror stories, I wasn't surprised to learn the cause attributed directly to a phenomenon I'm sure many of you have heard attacking your household pipes: water hammer. Sprinklers and toilets notoriously suffer from it, but any closed system with a valve is vulnerable, as wisegeek explains:
Water hammer is a very loud banging, knocking or hammering noise in the pipes that occurs when the flow is suddenly turned off. It is caused by a pressure or shock wave that travels faster than the speed of sound through the pipes, brought on by a sudden stop in the velocity of the water, or a change in the direction. It's also been described as a rumbling, shaking vibration in the pipes.
If you're concerned about some complicated and/or aged pipes in your vicinity, LMNO Engineering has a calculator that allows you to determine the piezometric pressure (representing the marriage of pressure & weight) in each pipe. I'm not sure what the stats were for the pipe in question, but I don't envy Con Ed, especially now that the law suits are surfacing.