List/Database of U.S cities and states [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

Closed 7 years ago.

Improve this question

I want a .csv lis开发者_C百科t, 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.

---

You can get city/state information in tab-separated value format from GeoNames.org. The data is free, comprehensive and well structured. For US data, grab the US.txt file at the free postal code data page. The readme.txt file on that page describes the format.

---

I spent a while looking for such a file, and ended up doing one myself, you can get it from here:

https://github.com/grammakov/us_cities_and_states/tree/master

---

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.

---

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/

---

I use the us city and county database for this purpose and I just checked that it got updated in August. They claim to include 198,703 populated places (a GNIS term for a city or village). I see you need full state names and these names are included in a free database called us state list.

Both of them are in CSV files and they provide very detailed instructions about how to import them to both local and remote MySQL servers. You can join them in a select statement to pull records with full state names for your needs.

---

You can find csv or sql format or html format at below website. They have cities+states for some countries including usa.

http://www.countrystatecity.com/. They keep updating the site and doing good job. hope this will help to other developers also.

for usa you can check below link.

http://www.countrystatecity.com/USAStatesCities.php

---

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.

---

This helped me a great deal: http://www.farinspace.com/us-cities-and-state-sql-dump/

It has a .zip file with 3 .sql files which you can just run in ssms.

You may need to replace some of the weirdly encoded single quotes with double quotes.