Which Heroscape Expansions Provide the best Terrain?

Honestly any of it, especially the 3 core sets. Rise of the Valkyrie (RoTV), Swarm of the Marro (SoTM), and Dungeons and Dragons (DnD)

1 of any of those is enough to build some pretty solid maps. ROTV has the most overall hexes worth of terrain and includes the Ruins pieces as line of sight blockers. SoTM has the swamp water tiles but significantly smaller amount of them compared to RoTV, and also includes the marro hive as a LoS blocker. D&D is hardest to find and is also on the smaller side of overall tiles but IMO they look the best, especially when using the Innistrad cardboard maps as a base level. It includes the rock structures as LOS blockers.

Once you have at least 1 Core set worth of terrain I'd consider 1-2 of the supplementary sets as you see fit.  Road to the Forgotten Forest (RTTFF) includes lots of road hexes and 5 trees to use as LoS blockers which are very nice to have.  The Castle set adds a cool aesthetic and gives you lots of tiles that can double as Road Hexes.  The Lava, Ice, and Jungle sets are more about aesthetics than gameplay in my experience so I'd put those towards the bottom of your list

I pretty much agree with kajoq . There were some maps made for the Herosape and Magic:AotP Cryptolith Map Contest that include various sets.  Even though the terrain is limited and the price online is double-triple MSRP, I recommend the D&D or Marvel sets just for aesthetics mixing with Shadows over Innostrad mats.  RoTV (amster set 1) and Road to The forgotten Forrest are probably the next best options.