Yeah, everything in Chapel Hill looks like that. You'd have to go to downtown Durham to find anything that looks both urban and architecturally/aesthetically pleasing, and it'd be way outside a student's price range. (Think, brain surgeons live here -- not grad students live here.)
The Westwood Dr. area is definitely not rural, though if you keep going down S. Columbia toward where it crosses over 15-501 (you can view this on Google Maps) it definitely starts to look that way. In actuality, Westwood Drive/Old Pittsboro/that area is right across from Old Mason Farm Rd, which is on hospital campus. There are also two bus stops in that area for convenient transportation to/from main campus.
As for Carrboro, that's the artsy/hipster sort of area. Expect a lot of cyclists and ironic lenseless glasses. However, it's definitely got the coolest coffeeshop/diner scene of the RDU area which is awesome for studying if you like to get out of your duplex/house every now and then. It's a mix between upperclassman undergrads, grad students, and liberal arts-oriented professors here (obviously I'm grossly overgeneralizing, but I'd say the standard holds in most cases). There's an organic food co-op in downtown Carrboro, and another version of the same co-op further down S. Columbia Street toward that "rural area" over in Southern Village (which is more typical Southern suburbia: houses, duplexes, apartments, little shopping center, etc., and is mostly adults/families but a few grad-age students).
The greatest concentration of undergrads is going to be in the apartment complexes (obviously) as well as any neighborhoods really close to Franklin Street. (For example, Greek life is housed on or just off Franklin, as is an "off-campus" campus housing option for undergrads and several student-oriented apartment complexes.) There're also a lot of undergrads down Martin Luther King Jr Pkwy way. Avoid that if you don't enjoy loud parties.
If you want large concentrations of grad and med students, your best bet is Carrboro -- the side furthest from Chapel Hill (15-20 minute bus commute, ish) or the Westwood Drive/Old Pittsboro area (walking distance, 5 minute bus). McCauley is hit or miss for undergrad/grad ratio, depending on where exactly in that neighborhood you live. There are also a lot of grad students in the apartment complexes off 15-501 but that area is pretty far from aesthetically-pleasing or historic.
If you need any specific help, PM me and I can tell you about the specific area I live in. Plus there's a good chance I'm moving to Cambridge/Boston next year, so perhaps you could help me out in return.
Also, if you don't mind commuting/driving from Durham and have a price range, I can find some historic areas in dtown Durham near Duke that you might be interested in. (Converted loft sorts of deals, etc.) Like I said, generally more expensive, but now that I think about it, I consider $500/month rent to be moderately expensive if not including utilities. These places would be like 900-2000/month which may/may not be more like what you'd find in Boston/Cambridge.