I will certainly understand if iPhone/iPad multitasking arrives with some fairly severe limitations placed on the developers of background apps. Right now there's zero ability in that area so anything, even if severely limited, will still be an infinite improvement (depending on how you like to imagine your divide by zero operations playing out. ;)
So now the user has two things to keep in mind as far as silencing: which times it is programmed to do so automatically (all of which the user must be a bit suspicious of already - as you said, to be extra-sure the user would want to manually override even at those locations during times that it's really important for the ringer to be off) and which times they still have to manually override it. So we've got extra mental burden and anxiety for the user, partially or wholly overriding the savings of not having to worry about silencing in some circumstances. We're only trusting the system for those times where we'd really like it to work but it doesn't have to be guaranteed, so there's some lurking uneasiness during those times.
And the user has to program it. And I'd think it's very likely to be a significant battery drain since it's needing to turn on the GPS quite often to be useful. Etc etc.
Anyway, it's a super-cool sounding feature when just considered as a bullet-point, but it just strikes me as a "techno cool" feature (kind of like speech recognition and "Minority Report" interfaces) that I doubt I'll ever see widely used. I'm sure there are a few specific circumstances where it's really handy and I'm happy for those who enjoy it, but I have nearly no interest in it compared to a trusty manual switch and I think I'm speaking for the vast majority of users there. Could certainly be wrong.