This place is a 15 minute bus ride or 30 minute walk from Pacific Central Station, so it's a great choice if you are arriving by train. If you don't mind walking, you can get to many different places on foot from here, however if you want to maximize your time in Vancouver get yourself a Compass pass for the days you plan to do some serious exploring. There is a bus stop just a couple minutes away from here and our experience with the public transit was great.
The place itself is very modern. It's in a cute neighborhood and you have a private entrance on the outside of the house with no entry to the upstairs from inside - so it really feels like your own little apartment. Keith stopped by to make sure we were all set and let us know if we needed anything, but after that we didn't see him much which may be a bonus for you if you prefer privacy.
There are people living upstairs, and like an apartment you will hear them from time to time. In the evenings if you are there you will hear the kids upstairs doing kid things, and in the mornings you may hear them getting ready for school. There was never any noise during our stay that kept us awake or that woke us or our baby up, but if you are a light sleeper and sensitive to noise you may have a hard time.
Overall, I would recommend this location and would book it again.