Huh, within just a few replies, two of my picks already came up, so let me throw my meaningless voice behind those.
1) Alpha Centauri - My favorite and I think easily the most interesting premise of all the Civ games. I still play it obsessively.
2) Homeworld: Cataclysm - All of the homeowrld games were amazing, but cataclysm, I think, had the most compelling story of the three. Commanding a mining ship with very little offensive power and facing a galactic threat gave such a feeling of hopelessness and eventual triumph. The mission where you watch as your hard-earned fleet gets ripped to shreds, your crews willingly sacrificing themselves as you try and try to convince the Bentuzi not to give up on the galaxy had some of the greatest emotional weight of any scene I've ever played in a game.
3) I'm going to be that guy among the 'that guys' and say FFVI. I know VII is the one everyone goes to and all, and I understand why VII is so beloved, but if there exists a FF that grabs your feelings and twists them up it's VI. I'm not down on VII, I just feel it's main plot is a little juvenile. Don't crucify me for this opinion please, but I find Cloud's story to be less compelling than Barret's and Cid's. When you can't even be more interesting than the side plots in your own game, I think something is amiss. I also blame cloud for starting the trend of "Angst turned up to 11" in several of the following games. This is one guy's opinion on a touchy subject, so no need to make a federal case out of it. All of that said I would be wonderfully happy with a FFVII update, as I do love the game despite my criticisms!
I would also have said Freelancer, but we're going to get our update of that in the form of Star Citizen. So I guess honorable mention goes to another possibly controversial pick in the form of The Adventure of Link. This is not because I think it's better than other Legend of Zelda games that could also use a remake, but because it would likely see the greatest improvement from it.