This discussion will quickly turn into a budget conversation, and here's my two cents.
I use a Canon 20D. For macros, I use the 100mm f/2.8 USM Macro. It's a great lens, especially for the price. Bang for buck, it can't be beat. However, Canon also has a 180mm macro, and it has the "L" distinction, which is Canon's top of the line. These "L" lenses are flat out phenomenal, and arguably the best glass money can buy. But you're going to pay for it. While the 100mm is $430, the 180 will run you $1200.
For full tank shots, I don't use macro lens. My daily duty lens is the 28-135mm IS USM. I use it for many of my tank shots, but it's not a macro lens, so it's not going to get those super-up-close shots. This lens is $420. I dream of owning one of the daily duty "L" lenses someday. I'm quite interested in the 24-70 f/2.8L, but it's $1100. Again... those L lenses are phenomenal, but you pay for them.
So budget: If you can spare a couple thousand, start looking into those L lenses. You really are getting what you pay for. If you aren't ready for that kind of investment (me either), I suggest the 100mm macro. It's a deal that can't be beat. And I've been extremely happy with the 28-135 as my daily use lens, but for a DSLR, it's not wide enough, so you may consider one of Canon's EF-S (DLSR only) lenses, like the 17-85 IS for around $500.