What it lets you do is wirelessly transfer pictures from a digital camera directly to a computer (both Mac and Windows) and then on to the photo sharing service of your choice. This will negate the use of USB or other wires to connect your camera to your computer, and with 802.11g, upload speeds should be pretty snappy. This is a surefire solution for cameras that do not come with Wi-Fi already on board. It draws power from the camera, and requires only a simple software install on the PC side of the equation to get up and running.
For professional shutterbugs who need to take pictures out in the field, such as insurance appraisers or Realtors, this could be a useful solution. Anything that helps speed or simplify the process of uploading photos is a step in the right direction.
Of course, the Eye-Fi is probably another nightmare to add to the list of things that are considered "dangerous" in most IT admins' minds.