I love the community at PS9 and understand from parents that have kids in both programs that academically the two are similar. So is giving up the community for middle school worth it?

IMO - in elementary school kids need the community. Once they hit middle school - then it's ok. I also think by the time middle school comes around, options in NYC will have improved, but that is the optimist in me speaking. Also have to consider sibling preference