There are two separate questions here: should add-on software you install go in /usr or /usr/local (short answer: it should go in /usr/local for the reasons Dan Benjamin gives), and should you install add-on software from a provided installer (disk image) or by building it yourself (short answer: whatever works best for you, but either way it should go in /usr/local).
I'll take Python as an example. OS X v10.6.6 includes python v2.6.1 in /usr/bin, with its frameworks in /System/Library/Frameworks. If you download an installer for a newer version (currently installers for v2.7.1 and v3.1.3 are available), it'll put the new version in /usr/local/bin, and its frameworks in /Library/Frameworks (/Library has roughly the same relation to /System/Library that /usr/local has to /usr), which is exactly what you want. If your paths are set properly, you'll use the newer version automatically. OTOH system scripts that may not be compatible with, say, Python v3 should start with #!/usr/bin/python, and keep using the old (standard) version. Furthermore, when OS X v10.6.7 ships and includes a relinked version of Python v2.6.1, it won't step on the update you installed.
If you were to build a newer version of Python yourself, you should do it just like the installer does: put the binary in /usr/local/bin and the frameworks in /Library/Frameworks, for exactly the same reasons. If you aren't sure how to do this, you should probably stick to the installer -- at least for major projects like Python and Git, there are likely to be smart people involved who know how to get this sort of thing right.