Man this is going to sound ridiculously fanboyish/nerdy... But 
 
Sephiroth(in northern crater) was holding back the lifestream energy that Aerith summoned with the White Materia (the spell Holy) which was the only thing that could stop Meteor. The ending sequence shows the Holy spell not happening and total despair, until it finally surfaces just in time. But not before Midgar gets messed up pretty badly and eventually overgrows with Vegetation 500 years later.

Well, Sephiroth dies for good and doesn't become a God as he intended (by absorbing the lifestream that would gather and form to close the planet's wound from the Meteor's strike), Meteor is stopped by Holy (which appears to allow it to reach Midgar, or perhaps unable to hold it back) and the lifestream's power (which then stops it from annihilating Midgar), Midgar the city is destroyed but humans presumably survive as Cait Sith had taken them to the slums below (and the city while destroyed doesn't appear to be completely obliterated with its tall towers still standing), and the only real significance of seeing it 500 years later is that what once was a wound of the planet (with the reactors sucking up lifestream and the only flowers growing in Aeris' garden and nowhere else) is now an explosion of life, filled with plants and flowers. Something like that.

Try FF IX next, it's less emo, more charming, more fantasy than sci fi, more SNES style (but with some real nice visuals). Then try FF Tactics for a different type of game, one of my favorite Strategy RPGs and game stories (despite sloppy translation, supposedly fixed on PSP's War of the Lions).

Sephiroth was researching to summon meteor. The Ancients professed the coming of meteor which would destroy the world. Sephiroth was in the process of summoning meteor. Sephiroth was also holding the planet's lifestream (the green stuff) back, to prevent it from healing the planet, basically giving meteor a free shot at the planet.  Lifestream, as explained by Bugenhagen, is the life of the planet. When any living thing dies it returns to lifestream, then can be reborn elsewhere as something else, keeping the entire planet's ecosystem in tact. 'Mako' is lifestream turned into energy, which is basically like killing the planet.
 
Now.. Aeris as an Ancient was able to summon Holy, because she had the white materia. She 'died', but as mentioned she didn't REALLY die, she just returned to lifestream and somehow. So that's why Cloud sort of sees her after defeating Sephiroth. 
 
So by defeating Sephiroth, we now have lifestream back to help defend/heal the damages created by meteor. However, it's too late, meteor is on a crash course to obliterate the planet. As we see in the cinematic lifestream rushes toward the area where meteor will hit, Midgar, and tried to stop it. However it slowly goes through, it seems lifestream isn't strong enough. 
 
But then, what's that white stuff? It's most likely holy. How Aeris was able to summon somehow connected to her being an Ancient and having the white materia. Most likely when she was praying at the end of Disc 1 in Forgotten City, she was actually praying to summon Holy, and Holy knew when to act. 
 
So, 500 years later, the planet is still in tact due to the combined efforts of lifestream and holy. Red XIII has a long life, as explained by Bugenhagen, which is why he is still alive and somehow has children. It is likely that the other characters are now dead, the average human life rarely going over 100 years. Midgar is in ruins because lifestream and holy were not fast enough to completely save Midgar. 
 
Yes other FFs are like this. Most of this is explained/alluded to throughout the in-game dialogue. I hope this explained most of your questions.

I'm a bit confused about how in Advent Children Midgar is returned to full glory (that Denzel short showed that off really nice) and in the "500 years later" it is overgrown by vegetation. 
 
Might be best not to think too much about that stuff though. 
 
@Willy105:
You might want to check out a 30min anime short called Denzel that Square made, it tells the events of the meteor "hitting" Midgar and what its eventual effects were on the population.