DS9 shows both sides of the equation, with the white hats attacking fortified black hat positions and vice versa. We see many levels of conflict there: how a single freighter fares against a single small commerce hunter ("Return to Grace"), a protected convoy wards off harassers ("Rules of Engagement" and "Sons and Daughters"), an isolated space fortress fights off starships in small ("Emissary"), medium ("Way of the Warrior") and large ("Call to Arms") formations, individual ships and small formations skirmish (numerous episodes), large fleets clash in empty space ("Sacrifice of Angels"), and invasion forces penetrate into a defended star system ("Tears of the Prophets") and then fight a quagmire battle there ("Siege of SR-558"). All of these form a rather consistent and smooth whole; there are special characteristics to each type of engagement, but nothing much that would make one engagement look silly in face of evidence from another.
...With the single exception of the lack of standoff mass destruction. We know the Cardassians go for it, using very large and heavily defended penetrators for it ("Dreadnought"). We know that raining extinction-level death from orbiting starships is easily achieved in a matter of hours or even minutes if the target is undefended ("The Die is Cast"). But what stops at least a few planetbusting potshots from getting through in the heat of a battle above a defended planet? Say, Earth, or Chin'toka?
With Chin'toka, we can argue that the invaders didn't want destruction but conquest. With the Breen raid, destruction would have been a smart move, though. We could well argue that San Francisco was hunkering under powerful shields, or at least that its individual city blocks and buildings were; perhaps what we saw was the level of destruction one achieves when citykiller warheads detonate against such shields, and the seams leak a little bit? However, if the Breen really meant it, they wouldn't have fired citykillers. They would have fired something that slagged half of California, so that the continent would melt away from beneath the defenders - the sort of destruction we saw in "The Die is Cast" or, say, "Skin of Evil". Aiming at nearby unshielded desert would then destroy the shielded defender.
Shields aren't the full answer, then. But perhaps Earth also has point defense systems that shoot down missile-type weapons, preventing the biggest bangs from penetrating the ground and from causing geology-rearranging effects; the shields may handle airbursts well enough to keep San Francisco safe, even if most of Death Valley gets even deader in collateral damage.
But even point defense interception doesn't explain what keeps heavy phaser blasts from reaching the ground. Here we probably have to argue that phasers don't melt continents except when allowed to dwell for several minutes or hours, which would be impossible with a space battle raging above the defended surface. In "Encounter at Farpoint", Riker likens the fairly mild bombardment of the Space Jellyfish to a phaser attack, suggesting they might not get much worse than that.
How does this all relate to STXI? Nero didn't have bombardment ordnance as far as we know: his flechette missiles worked against starships, but wouldn't have made much of a hole in San Francisco. His drill was a clumsy tool, and probably couldn't even be aimed well without long preparation. He had no other beam weapons in evidence. And it's not a bad bet that his red matter would only have been a practical weapon if deployed with some precision; we see that when it reacts in empty space, the results are far from destructive (to anything besides the fabric of the universe).
The question here really isn't how Earth's defenses did or did not keep Nero from inflicting damage to San Francisco Bay. The question is why said defenses were so completely silenced that Nero's vulnerable drill was not counterattacked. Most of the weapons and improvised weapons on Earth that were capable of harming the drill would apparently have been outside central control, as Trek has always portrayed the hardware as such, so using command codes to confuse, destroy or lock up central control would not have protected the drill (even if it shut down all the orbital or surface cannon that could shoot down the Narada, confused the launching orders for the primary interceptor craft, and prevented the raising of all the shields that could oppose the drilling beam).
Timo Saloniemi