database 美国城市和州列表/数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2177831/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List/Database of U.S cities and states
提问by Click Upvote
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:
我想要一个 .csv 列表、mysql 数据库或所有美国州和城市的任何其他列表，包括哪些城市在哪个州。从这个列表中，我将生成一个包含以下字段的 mysql 数据库：
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.
提前致谢。
采纳答案by Yada
Check out the MySQL world sample database. This db is used by mysql documentation as a sample db to test query on.
查看MySQL world 示例数据库。该数据库被 mysql 文档用作示例数据库来测试查询。
It already have a 'cities' table you are looking for.
它已经有一个你正在寻找的“城市”表。
回答by Joshua Frank
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.txtfile at the free postal code datapage. The readme.txtfile on that page describes the format.
您可以从GeoNames.org获取制表符分隔值格式的城市/州信息。数据免费、全面且结构良好。对于美国数据，请US.txt在免费的邮政编码数据页面获取文件。readme.txt该页面上的文件描述了格式。
回答by The Whiz of Oz
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
https://github.com/grammakov/us_cities_and_states/tree/master
回答by bkm
Are you ready to pay for this content? If YES, then you can find it at uscities.trumpetmarketing.net
您准备好为此内容付费了吗？如果是，那么您可以在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:
我也在一些编程书籍中看到了这些信息，尤其是那些处理 .NET 数据库编程的书籍。让我参考我的图书馆并就此回复您：
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.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
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/
http://mhinze.com/archive/list-of-us-cities-all-city-names-and-states-regex-groups/
回答by John Kwiatecki
I use the us city and county databasefor 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.
为此，我使用了美国市县数据库，我刚刚检查了它是否在 8 月更新。他们声称包括 198,703 个人口稠密的地方（城市或村庄的 GNIS 术语）。我看到您需要完整的州名，这些名称包含在名为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.
它们都在 CSV 文件中，并且提供了有关如何将它们导入本地和远程 MySQL 服务器的非常详细的说明。您可以在 select 语句中加入它们，以根据您的需要提取具有完整状态名称的记录。
回答by dammika
You can find csv or sql format or html format at below website. They have cities+states for some countries including usa.
您可以在以下网站找到 csv 或 sql 格式或 html 格式。他们有包括美国在内的一些国家的城市+州。
http://www.countrystatecity.com/. They keep updating the site and doing good job. hope this will help to other developers also.
http://www.countrystatecity.com/。他们不断更新网站并做得很好。希望这也会对其他开发人员有所帮助。
for usa you can check below link.
对于美国，您可以查看以下链接。
回答by Micro
This helped me a great deal: http://www.farinspace.com/us-cities-and-state-sql-dump/
这对我帮助很大：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.
它有一个包含 3 个 .sql 文件的 .zip 文件，您可以在 ssms 中运行这些文件。
You may need to replace some of the weirdly encoded single quotes with double quotes.
您可能需要用双引号替换一些奇怪编码的单引号。
回答by BobMcGee
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.
这是一项艰巨的任务。考虑通过从此页面上删除链接来创建一个： WP：美国城市、城镇和村庄列表。如果您抓取 wiki 标记代码而不是原始 HTML，它会简单得多。
Will require some skill at regexes or at least parsers, but should be do-able.
需要一些正则表达式或至少解析器方面的技能，但应该是可行的。
