I was doing some searching online for geolocation data to try and help a person out on the MSDN Forums today and happened on a really neat project on Codeplex. This project has scripts to create a repository for US Census Data for SQL Server 2008 with spatial information. The script files are very large, up to 41.5MB (it is a 380MB TSQL Script uncompressed) compressed in RAR format for the ZipCode table creation script alone, but it contains the GeoLocation data using the Geography data type in SQL Server 2008.
The scripts load the data and include spatial indexing into SQL Server 2008. It’s quite comprehensive for being free. To install the scripts you need to use sqlcmd from the command line due to the size of the script files, they don’t load in SSMS without causing performance problems. If you check it out, make sure you click the Downloads button on the toolbar, you only get the State data from the Download Now button, and additional granularities of data are available under all of the Downloads.