Is is great? No.
But in a weird way, this might be a good thing for you and DP. You're obviously no longer sexually (/romantically?) attracted to one another, but you've been sticking with the status quo because it's easy.
It's really common for people to stay in stagnant relationships because they don't feel there's 'enough' wrong to leave.
This incident has reminded you what it can feel like when you really fancy someone, and maybe reminded you that you want that from a relationship?
It sounds as though DP would not be too heartbroken for things as they are to end, and nor would you - while it would be sad and difficult to disentangle your lives slightly, it would free you both up to find a relationship that makes you truly happy and fulfilled romantically.
How honest are you with one another about the current state of your relationship - could you open up a conversation about starting to officially 'break up' and move on in a way that's healthy and happy and doesn't cause any drama?
It can be done, especially if you're both on the same page - for example, a 12-month plan to introduce your children to the idea that you're not 'together' romantically any more, but still love them lots, for one of you to start saving for/finding somewhere else to live, getting them used to the idea. If you're already effectively 'just' co-parenting, but doing so in a healthy way, then a move to doing that from two homes isn't going to be too devastating.
I think the biggest issue here will be if you and DP aren't on the same page, or that one of you is happy for the status quo to continue and the other isn't. If you both are, you could have an honest conversation about staying in the same home for now but seeing other people in a way that's not disruptive (not at home, obviously, nothing long term, etc) and boundaries for that in future when you're both ready to move on.
What it does seem, though, is that you've reached the end of being happy to just accept things exactly as they are. And out of that will come growth and good things - you just need to know what you want, and communicate.