As of Mac OS X Lion 10.7, Terminal will open a new window if you drag a folder (or a text pathname) onto the application icon. If you drag to the tab bar of an existing window, it will create a new tab in that window.
You can also do this from the command line or a shell script:
open -a Terminal /some/path/
This is the command-line equivalent of dragging a folder onto the Terminal application icon and will open a new terminal window at "/some/path".
Terminal also now supports Services for opening a terminal at a selected folder (e.g., in Finder) or a text pathname using the contextual menu. You can enable them in
System Preferences > Keyboard > Keyboard Shortcuts > Services
Look for New Terminal at Folder and New Terminal Tab at Folder. You can even assign command keys to them if you like.
Finally, if you drag a folder or pathname onto an existing tab (i.e., the tab in the tab bar) and the foreground process is the shell, it will execute a "cd" command in one step. As in previous versions, dragging a folder onto the terminal display will insert the pathname to the folder.