One of the best analyses I've seen is from Strong Towns - a blog devoted to building livable and financially solvent towns and cities. In their deep-dive analysis of Lafayette, LA, they find that the only part of the region generating enough revenue to sustain the maintenance costs was the dense old-town city center. [1] Most of the costs examined come from infrastructure (roads, power, gas, water), not social services.
I imagine the calculus is different when considering federal government expenditures (as you point out), as the majority of federal expenditures are towards social security, medicare, medicaid, and the military. However, there is significant federal subsidies to farmers as you point out, and huge sums of cash for highways, power, and other infrastructure.
[1] https://www.strongtowns.org/journal/2017/1/9/the-real-reason...