Yup, this cost vs. gentrification dilemma is definitely a paradox that stadium projects have to deal with, although local governments typically resolve this tension by being totally uninterested in the concerns of neighborhood residents worried about gentrification.
One of the benefits of the NCCIW site (and to a lesser extent the Cargill) site is that you would have a lot fewer concerns about gentrification because there is not currently any housing on those sites. The NCCIW site is big enough that you could put a stadium in the middle and still surround it with a pretty extensive “buffer” of new housing, and so the existing housing closest to the prison would not be as intensely affected by the stadium. Plus, local residents would probably be happy to see the prison knocked down. And, just speaking hypothetically, that’s an area where a stadium project could potentially be the winning bidder.
As for the other examples, it’s not that the AT&T Park site wasn’t expensive or desirable, because all land in around San Francisco is extremely expensive and desirable. It’s just that that was almost certainly the most reasonably priced option the Giants could have gotten. And, again, I know that PNC was put on the North Shore very specifically because land was cheaper there. (Both PNC and AT&T do demonstrate, though, that the most expensive land is not necessarily the best land for a baseball stadium, or vice versa. Notwithstanding their more reasonable cost, those were both awesome, awesome sites for a stadium.) Cincinnati, yeah, is probably the closest example to what you’re describing, but that too was built in the parking lot of the old stadium, which I gather wasn’t such a hot area at the time Riverfront was built.
The highly unusual fact that there is no freeway running through the heart of downtown is one of the many reasons why I love Raleigh. But, yes, there are many facets of the city that would complicate any efforts to build a downtown stadium. For one thing, Raleigh needs to make massive, massive investments in public transportation. Until that happens, any sports stadium is going to be car-centric, like PNC Arena is. I would very much like Raleigh to make such a massive investment, so if building an MLB stadium could be an inducement to get the city to do something it desperately needs to do anyway, that would be the proverbial two birds with one stone from my view.
I totally agree that putting on my baseball fan hat (figuratively or literally), I love downtown ballparks, and DBAP in particular.