We're all advising from our own perspective and experiences, so here's a list of thoughts and you can either consider them OP or strike them off your list. They just might help crystalize your thoughts.
You buy a house for the person who's paying and to suit the person or people who are there for the majority of the time. That said, if the two closest people in my life 'hated' my choice, I would look for a different option.
If I was going to be living there on my own, possibly, within say five years, I wouldn't want a house that was too big for one, or too time consuming and expensive to maintain. Stamp duty, legal and all moving costs are very expensive and so I would want to be sure that I was making the best possible choice and compromises were minor.
I wouldn't touch a house near land that flooded. I've been there before and so it's now a deal breaker.
Personally I do like life to be quiet and peaceful. I enjoy views and nature and don't mind being somewhat isolated or having to drive everywhere.
That said the downside of countryside life is poor services and amenities and can be dreadful broadband connections. It's very easy to see a rural idyll but the reality of poor transport links and so on, can give a very different experience.
New houses can rise well in value in a good market, but on the whole where there is a lot of building taking place they can suffer from the fact that there can be a lot of competition for sales at any one time in the future and it therefore becomes a buyers' market.
I have grown children who come back home to stay and I think the fact that they both have a comfortable and fairly equal sized bedroom and separate bathroom makes it more attractive to them. If it was possible, which obviously it isn't always, I wouldn't go for a two bed with a box room type house where one is in a double room and one in a small single.
There's the probability that it won't be long before they want to have a girlfriend staying over too, so I'd factor that in if you want them to always feel welcome to stay or to come and spend time with you going forward.
Mine also come more often because it's very easy to get back. As I said at the start, you'll be buying a home for you but you love your DSs and want them to be around, so you probably do need to think about the fact that the second house isn't appealing to them at all and their reasons seem fair enough.
There really should be somewhere with less compromises. I think you're feeling pressured because houses don't come up often in these locations.