I just found out (after posting, of course) that /u/Nighthawk321 made a video on this exact topic just two months ago:
$1.
That was definitely very helpful, and brought a few things to my attention that I didn't necessarily think of. Like, if they're obviously a resident at a place, they probably know their way around better than I do myself.
It also kind of did confirm for me that yes, there actually can be situations where being offered help can be unwelcome, even if it is with the best of intentions. So, I guess my takeaway is: It's very situational, and always will be. But I shouldn't let my social awkwardness stop me from offering help to people if I feel they might need it.