I always thought leggings were worn under skirts/dresses/tunics and were no different to tights really. They can be rather ... erm, figure hugging. Though I guess they're a bit like breeches, and I never stopped to think about their figure hugging ways while riding.
Leggings are quite comfortable in the gym, but given that it's a mixed-gender gym I feel slightly uncomfortable wearing them. Not because I think I'm hot stuff and will cause all the men to lust after me, but because I do not feel comfortable feeling that exposed, I guess. It's like how I think those tank tops are really comfortable in the gym, but because it's mixed-gender I opt for baggy t-shirts as I feel far too exposed.
Personally leggings are an extra layer of warmth under a skirt/dress/trousers, or for those dresses I don't feel comfortable wearing tights under but would look silly with actual trousers.
Plus I'm shallow, leggings only really look nice on a certain body shape. I am not that shape. I've seen enough women of my shape wearing leggings as if they were trousers to know that it really isn't a look I have any desire to recreate.
================================
Modesty is a difficult area, largely as it's defined by society/culture. It is, at heart, a very personal thing.