We've integrated Flickr into Web sites, both custom integration working with their API and using prebuilt WordPress plug-ins (
http://speckyboy.com/2008/10/14/10-a...ns-for-flickr/).
If you're a reasonably good programmer (or know someone who is), Flickr does a reasonably good job making its gallery format available for second-party integration. Here's a link to the classes we used as the base for a custom CMS integration -
http://phpflickr.com/ - we didn't use them as-is, but there was enough good stuff in there to get us going.
As far as off-the-shelf apps go, Coppermine gets my vote. It's extremely customizable, once you've learned how the theme.php page is put together (until you do, customizing is very limited and can be painful).