Hey there thebiggedbig!
Ahhh, yes - the graphics card dilemma. Having been through this one with my very upgraded dual 1 GHz Quicksilver - I feel I can offer you some information.
Your 2003 MDD PowerMac G4 has a x4 AGP slot. So as stated above, the Radeon 9600 or 9800 are going to be your best bet. Personally, I would go for the 9800 Pro - it's got a little more grunt. From what I have been told the Radeon 9800 Pro is a powerful card that will not over saturate the AGP bus. Whereas even more powerful cards such as the ATI Radeon X800 XT are going to be pushing it... (and considered overkill for a x4 AGP G4). My understanding is that the 9600 is only going to be a little bit better than your current 9000 Pro, but with core image support. So... recommendation = go for the Radeon 9800 Pro. At least you will get a decent "feel" in the upgrade.
I upgraded my Radeon 8500 with a 9800 Pro. Core Image isn't really a big thing at the moment with Tiger (only gives you the dashboard ripple - that's it!!!), but it will become more important (like you said) with the next OS X release. Someone mentioned that they noticed a huge difference in Photoshop due to the Core Image support - but I'm not so sure... The Radeon 9800 Pro provided a significant performance increase over the Radeon 8500 (which is very similar in performance to a 9000 Pro). I'm very happy with it. Halo is MUCH better, and most graphics applications just fly... Plus the 128MB RAM really helps with my dual monitor setup.
Due to cost, there was no way in hell I was going to buy an overpriced Radeon 9800 Pro from Apple. It's daylight robbery that Mac graphic cards cost significantly more than their PC versions - especially when you consider that they are made in the same factory (same quality) and the only difference is a slightly larger EEPROM. You should check OWC (otherworldcomputing.com) for the best price on a genuine Mac graphics card. They have both the 9800 and 9600 for the G4 available. If you need a card with a ADC connector - you will need to get a geunine Mac Edition card.
However if all you need is a DVI or VGA equiped card - I would highly recommend getting a "modified" PC card. Yes, there is a huge concern with flashed cards that use the small 64K EEPROM chip - don't get one of those. They use a reduced ROM (so it can fit on the 64K chip / when the original Mac ROM is 128K in size.) This can cause all sorts of problems. However, many PC modified cards for the Mac have replaced the small 64K EEPROM with a larger one (128K +) - and given that this is the only difference between a PC and Mac card - the modified cards work flawlessly (and can be upgraded with any new Mac ATI firmware update).
There are two advantages for getting a modified Radeon 9800 Pro.
1. It is significantly cheaper. I ended up getting a brand new modified 9800 with the 128K EEPROM chip for about $160 off Ebay - 6 months ago (whereas Apple wanted to sell me the same card for $399!).
2. The 9800 Pro PC version supports x8, x4, x2 AGP compared to the Mac Edition 9800 Pro that only supports x4 and x2 AGP. What this means is that if you ever get a G5 (that has x8 AGP) you can take the 9800 with you. The mac edition 9800 will only work with the G4 tower...
So in conclusion (and extra considerations):
I would recommend getting the Radeon 9800 Pro.
If you need a ADC connector on the card you will have to get a genuine Mac Edition graphic card.
If you get a PC modified card - make sure it has the 128K EEPROM.
Most of these cards really disperse a lot of heat. This is a big problem with the MDD G4 towers where heat collects up the top (where the PCI slots are) and has nowhere to go. Best solution is to get a PCI slot cooler to exhaust that hot air.