I've just touched the edges of Flickr (created my own account and uploaded a few photos to it), but it does look like a valuable (and fun) tool. I really need to play around with it a lot more to truly appreciate what it offers.
Of the mashups and 3rd party sites, I explored the trading card and mosaic ones. Here is a trading card that I made with my daughter's baby picture on it (below). It is pretty basic since I didn't know what I was doing, but I can see how fun it would be to create a whole deck of these card for your family and friends!
As for using Flickr in the library world, I can see using some of 3rd party sites (like the trading card or mosaic maker) to create fun visuals for your library. Perhaps a "Favorite Book" trading card series (with covers of books as the visual) and then comments from staff and patrons in the text section. Copyright issues may prohibit this, but it is an idea.
An interesting mashup is the map one that allows people to post photographs that they've taken around the world in juxtaposition to a world map, showing where the photo was taken. This might be a fun thing to do with patrons--have a library site where people can show scenes from their vacations.