My folks have lived in St. Pete for about the a decade now and I go to a couple Ray's games a year when visiting, so I have some thoughts on this and some stuff I've picked up from them. They're big baseball people and season ticket holders, so they have some strong opinions.
A new stadium probably won't fix anything in isolation, regardless as to where they put it. The bigger problem is the lack of public transportation. Yes, there are more people in Tampa than St. Pete, but there are already about a million people just in Pinellas county. If the team can't draw from that plus the other 2 million+ in the metro area, I don't think moving in the area is going to help. The geography just doesn't work without solid public transportation. Even if they moved across the Bay, then you're in a similar situation where it's still going to be a pain in the ass for half of the metro area to get to a game. If there was something like a light rail that could get you from downtown Tampa to the Trop, I think you'd see an increase in attendance.
The Trop is a weird place, but is a really fun stadium to see a game. Building a newer, shinier stadium won't change anything in terms of attendance (see Miami).
The transplant culture makes things difficult and I think this is a phenomenon that's compounded with baseball. I think baseball fandom, more than any other major US pro sport, is more likely to be inherited depending on how you grew up. A lot of folks are bandwagoning the Rays down there now, but a lot of them will always be Yankees/Red Sox/Jays/Braves/Cubs/etc. fans first. The same thing would happen in pretty much any other southern city with an expansion baseball team. If Charlotte got an expansion team, I think they would have the same problem. The Bolts are another good example of this. They're playing at a really high level now, but if they start stinking it'll be back to home games being dominated by Rangers/Wings/Leafs/Bruins fans when they're in town. They sell seats, but even when theyre good this is still kind of the case.