在 .NET 中获取州和省 [英] Getting States and Provinces in .NET

问题描述

以下网络服务将为我提供所有国家/地区.我想获得所有州和省.我该怎么做?

The following web service will give me all countries. I want to get all states and provinces. How can I do this?

http://ws.geonames.org/countryInfo

似乎唯一的方法是在此处注册 API 用户名 http://www.geonames.org/export/web-services.html 然后尝试调用类似 http://api.geonames.org/postalCodeSearch?country=COUNTRY_CODE&username=demo 其中 COUNTRY_CODE 是一个变量，我必须通过遍历每个国家来填充.

It seems the only way is to register for an API username here http://www.geonames.org/export/web-services.html and then try to make a call to something like http://api.geonames.org/postalCodeSearch?country=COUNTRY_CODE&username=demo where COUNTRY_CODE is a variable I would have to populate by looping through each country.

是否有更简单/更好的方法来获取所有州/省信息?像 http://ws.geonames.org/stateInfo 这样的东西会很棒！

Is there an easier/ better way to get all the state/province info? Something like http://ws.geonames.org/stateInfo would be fantastic!

推荐答案

我发现最好的是:

CREATE TABLE `countries` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `name` varchar(40) NOT NULL,
  `abbreviation` varchar(4) NOT NULL,  
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE `states` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `country_id` smallint(5) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `abbreviation` varchar(3) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO countries (name, abbreviation) VALUES ('Canada', 'CAN');
INSERT INTO countries (name, abbreviation) VALUES ('United States of America', 'USA');

INSERT INTO states (country_id, name, abbreviation) VALUES ('1', 'Alberta', 'AB');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1', 'British Columbia', 'BC');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Manitoba', 'MB');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'New Brunswick', 'NB');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Newfoundland and Labrador', 'NL');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Northwest Territories', 'NT');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Nova Scotia', 'NS');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1', 'Nunavut', 'NU');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Ontario', 'ON');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Prince Edward Island', 'PE');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Québec', 'QC');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Saskatchewan', 'SK');
INSERT INTO states (country_id, name, abbreviation) VALUES ('1',  'Yukon Territory', 'YT');


INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Alaska', 'AK');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Alabama', 'AL');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Arizona', 'AZ');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Arkansas', 'AR');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'California', 'CA');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Colorado', 'CO');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Connecticut', 'CT');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Delaware', 'DE');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'District of Columbia', 'DC');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Florida', 'FL');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Georgia', 'GA');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Hawaii', 'HI');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Idaho', 'ID');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Illinois', 'IL');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Indiana', 'IN');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Iowa', 'IA');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Kansas', 'KS');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Kentucky', 'KY');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Louisiana', 'LA');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Maine', 'ME');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Maryland', 'MD');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Massachusetts', 'MA');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Michigan', 'MI');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Minnesota', 'MN');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Mississippi', 'MS');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Missouri', 'MO');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Montana', 'MT');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Nebraska', 'NE');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Nevada', 'NV');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'New Hampshire', 'NH');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'New Jersey', 'NJ');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'New Mexico', 'NM');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'New York', 'NY');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'North Carolina', 'NC');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'North Dakota', 'ND');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Ohio', 'OH');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Oklahoma', 'OK');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Oregon', 'OR');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Pennsylvania', 'PA');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Puerto Rico', 'PR');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Rhode Island', 'RI');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'South Carolina', 'SC');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'South Dakota', 'SD');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Tennessee', 'TN');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Texas', 'TX');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Utah', 'UT');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Vermont', 'VT');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Virginia', 'VA');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Washington', 'WA');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'West Virginia', 'WV');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Wisconsin', 'WI');
INSERT INTO states (country_id, name, abbreviation) VALUES ('2', 'Wyoming', 'WY');

我从这里得到的 http://www.prioninteractive.com/2011/02/mysql-sql-for-generating-canada-and-usa-provincestate-tables/

这是司空见惯的事情；真不敢相信这么难找...

This is such as common thing; I can't believe it was so hard to find...

希望这对其他人有所帮助.

Hope this will help someone else.

这篇关于在 .NET 中获取州和省的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！