I see most of my riding in the near future to be going back and forth to work with some totally pleasurable weekend riding, but not many cross country or even next state trips (don't ever want to say never about anything).
Before anything else please understand that we can't make up your mind for you and it's ultimately up to you as to what motorcycle you will select. I can tell you that I've owned lots of motorcycles and I've loved every single one of them but that none of them did everything equally well.
I can, of course, share some of my personal opinions on what type of motorcycles work best for different situations, or more specifically, what I'd look for based upon what you expect to use a motorcycle for. Generally speaking for a "commuter" type motorcycle you want a mid-size or smaller motorcycle depending upon your commute.
When I was in college I traveled about 15 miles each way all on surface streets where the speed limit never exceeded 45 mph and I had a Yamaha 70 with a maximum speed of about 45 mph and it worked just fine for that. I also used it for limited country riding and even made a trip of about 60 miles each way one time but it was very limited and couldn't go on a freeway. That's probably much smaller than you would want but it is an example of "matching" the motorcycle to your needs. It was all I needed.
Later on I had a Honda 175 that could do 60 mph and I could take it on the freeways but it was very small for that purpose so eventually I upgraded to a 600cc Suzuki and it was excellent for most riding purposes. It was light and nimble enough for local commuting and was also excellent for canyon riding. It would do freeways very well but was not a touring motorcycle (although I believe you can tour on literally any motorcycle including a 50cc if you really want to - people actually tour on bicycles so any motorcycle can be used).
When I rode the Street 500 and Street 750 they reminded me of my Suzuki 600. Obviously they're much more modern motorcycles but the "feel" was about the same.
Since my Suzuki in the 1970's I've owned larger motorcycles but eventually I settled on my current 2007 50th Anniversary Sportster as the perfect ride for me as a primary motorcycle.
My wife doesn't ride due to a disability that makes riding for distances painful so I don't need a motorcycle for 2-up riding. I do a lot of distance touring on winding backroads and highway demanding a compromise in size and agility that the Sportster provides. It's not really an "around town" type of motorcycle so that's a downside but it works for it. It's primary purpose is 1-up touring and everything else was secondary to that use.
There's no fundamental difference between the 883cc and my 1200cc Sportster from a "functional use" standpoint. A little difference in power but both have more than adequate power. The same is true for the Street 500 and Street 750. There is no difference from a "functional use" standpoint but there is a difference in power but both have more than adequate for the purpose intended.
If my primary purpose wasn't 1-up touring but instead local commuting and weekend rides then I'd probably own a Street or a similar mid-size motorcycle. I wouldn't want the size and weight of the Sportster for local commuting and weekend rides.
I could still use a Street for touring if I wanted to, just like I could use a Honda 50 if I wanted to (but it would take me forever to get anywhere but that's not necessarily a bad thing on a motorcycle) but it would be perhaps different. I'm a bit weird though because I travel very light on me Sportster so storage wouldn't be an issue. I've done 200 miles in a day on the Street without any discomfort but I don't think I'd attempt 750 miles which I've done on my Sportster (but don't typically do).
In the end though any motorcycle is better than no motorcycle and, as you note, it's all about what you use it for. I always argue with myself about "what am I going to use it for" and then I don't buy a motorcycle that's bigger than I really need. Perhaps the first question I ask is myself "Why do I need this motorcycle as opposed to a smaller motorcycle?"