I know it's cheating, but if you are driving, there are excellent viewing spots of CP's Kicking Horse Pass route (west of Lake Louise).
It's particularly impressive watching eastbound freights navigate the Yoho loops and spirals up the final slopes to the summit.
Time in Jasper (CN) is also very rewarding, with the Canadian coming through during the day as well as all the freight.
The route through the Fraser Canyon is spectacular from the road (Highway 1) and you get bird's eye views of trains on both banks for long distances.
CP and CN tracks run on opposite banks and swap half way, with trains of both companies sharing each direction.
Some of the freight traffic is epic. I was particularly impressed by the trains of raw sulphur (a by-product of the oil sands industry).
The sulphur is shipped to Vancouver and on to China to be turned into fireworks and other explosives.
http://www.cpr.ca/en/our-markets/sulphur
I would also recommend Mike Walker's Railroad Atlas of Western Canada for the route detail (SPV).