List/Database of U.S cities and states

Question

I want a .csv list, mysql database, or any other list of all U.S states and cities, including which cities are in which state. From this list I will generate a mysql database with the following fields:

states:
 - id (int, auto_increment, primary)
 - name (varchar 255)

cities:
 - id (int, auto_increment, primary)
 - stateId (id of the state from states table to which this city belongs)
 - name (varchar 255)

Thanks in advance.

Answer 1

That's a tall order. Consider creating one by scraping the links off this page: WP: List of cities, towns, and villages in the US. It is much simpler if you scrape the wiki markup code rather than the raw HTML.

Will require some skill at regexes or at least parsers, but should be do-able.

Answer 2

Are you ready to pay for this content? If YES, then you can find it at uscities.trumpetmarketing.net

I have also seen this information provided along with some programming books especially ones dealing with .NET database programming. Let me refer to my library and ge back to you on this:

You can also refer the following:

http://www.world-gazetteer.com/wg.php?x=1129163518&men=stdl&lng=en&gln=xx&dat=32&srt=npan&col=aohdq

http://www.geobytes.com/FreeServices.htm

Please dont bother voting for this answer. There is no information here that cannot be obtained via a simple google search!

Someone has posted a list here:

http://mhinze.com/archive/list-of-us-cities-all-city-names-and-states-regex-groups/

Answer 3

Check out the MySQL world sample database. This db is used by mysql documentation as a sample db to test query on.

It already have a 'cities' table you are looking for.

http://dev.mysql.com/doc/world-setup/en/world-setup.html