*cough*
SimTropolis
Shameless link. It's a site that I frequented and moderated a while back all about SC4. There's mods, custom buildings, and a pretty huge community. The forums are a bit slow, reply-wise, though.
And yes, SC4 needs loads of RAM, because it's got to keep loads of different images (the buildings) handy. The bigger the city, the more different buildings, the slower the game.
IIRC, the game slows down when scrolling as it's got to load the buildings in the area you're going to. If you click on an area of the map yo aint visited in a while, you'll see buildings popping up.
That site's great though, if you're bored of the buildings and scenery. I seem to remember a terrain mod by "CycleDogg", or something along those lines. It replaced the tree models, water textures and land textures, made it look a lot better. You can also download the Building Architect Tool (BAT) from the official site, and make custom buildings, if you can be bothered to learn gMax.
</shameless plug>