Cities In Countreys

[RizlaUK]

dose anyone know of a predefind list of countrys and the citys within them that i can build a database from

im building an application that will store addresses an well as various other information, the client wants the countrys and citys in combo boxes, select a country and below city combo gets filled with cities in that country.

plz save me hours/days even weeks of research, there must be a list published somewhere

[Dermot]

Use this to create a table of countries. I have the list of cites, just need to save it in a format to share.

CREATE TABLE countrycodes (code text,country text);
insert into countrycodes (code,country) values ('AF','Afghanistan');
insert into countrycodes (code,country) values ('AL','Albania');
insert into countrycodes (code,country) values ('DZ','Algeria');
insert into countrycodes (code,country) values ('AS','American Samoa');
insert into countrycodes (code,country) values ('AD','Andorra');
insert into countrycodes (code,country) values ('AO','Angola');
insert into countrycodes (code,country) values ('AI','Anguilla');
insert into countrycodes (code,country) values ('AQ','Antarctica');
insert into countrycodes (code,country) values ('AG','Antigua and Barbuda');
insert into countrycodes (code,country) values ('AR','Argentina');
insert into countrycodes (code,country) values ('AM','Armenia');
insert into countrycodes (code,country) values ('AW','Aruba');
insert into countrycodes (code,country) values ('AU','Australia');
insert into countrycodes (code,country) values ('AT','Austria');
insert into countrycodes (code,country) values ('AZ','Azerbaidjan');
insert into countrycodes (code,country) values ('BS','Bahamas');
insert into countrycodes (code,country) values ('BH','Bahrain');
insert into countrycodes (code,country) values ('BD','Banglades');
insert into countrycodes (code,country) values ('BB','Barbados');
insert into countrycodes (code,country) values ('BY','Belarus');
insert into countrycodes (code,country) values ('BE','Belgium');
insert into countrycodes (code,country) values ('BZ','Belize');
insert into countrycodes (code,country) values ('BJ','Benin');
insert into countrycodes (code,country) values ('BM','Bermuda');
insert into countrycodes (code,country) values ('BO','Bolivia');
insert into countrycodes (code,country) values ('BA','Bosnia-Herzegovina');
insert into countrycodes (code,country) values ('BW','Botswana');
insert into countrycodes (code,country) values ('BV','Bouvet Island');
insert into countrycodes (code,country) values ('BR','Brazil');
insert into countrycodes (code,country) values ('IO','British Indian O. Terr.');
insert into countrycodes (code,country) values ('BN','Brunei Darussalam');
insert into countrycodes (code,country) values ('BG','Bulgaria');
insert into countrycodes (code,country) values ('BF','Burkina Faso');
insert into countrycodes (code,country) values ('BI','Burundi');
insert into countrycodes (code,country) values ('BT','Buthan');
insert into countrycodes (code,country) values ('KH','Cambodia');
insert into countrycodes (code,country) values ('CM','Cameroon');
insert into countrycodes (code,country) values ('CA','Canada');
insert into countrycodes (code,country) values ('CV','Cape Verde');
insert into countrycodes (code,country) values ('KY','Cayman Islands');
insert into countrycodes (code,country) values ('CF','Central African Rep.');
insert into countrycodes (code,country) values ('TD','Chad');
insert into countrycodes (code,country) values ('CL','Chile');
insert into countrycodes (code,country) values ('CN','China');
insert into countrycodes (code,country) values ('CX','Christmas Island');
insert into countrycodes (code,country) values ('CC','Cocos (Keeling) Isl.');
insert into countrycodes (code,country) values ('CO','Colombia');
insert into countrycodes (code,country) values ('KM','Comoros');
insert into countrycodes (code,country) values ('CG','Congo');
insert into countrycodes (code,country) values ('CK','Cook Islands');
insert into countrycodes (code,country) values ('CR','Costa Rica');
insert into countrycodes (code,country) values ('HR','Croatia');
insert into countrycodes (code,country) values ('CU','Cuba');
insert into countrycodes (code,country) values ('CY','Cyprus');
insert into countrycodes (code,country) values ('CZ','Czech Republic');
insert into countrycodes (code,country) values ('CS','Czechoslovakia');
insert into countrycodes (code,country) values ('DK','Denmark');
insert into countrycodes (code,country) values ('DJ','Djibouti');
insert into countrycodes (code,country) values ('DM','Dominica');
insert into countrycodes (code,country) values ('DO','Dominican Republic');
insert into countrycodes (code,country) values ('TP','East Timor');
insert into countrycodes (code,country) values ('EC','Ecuador');
insert into countrycodes (code,country) values ('EG','Egypt');
insert into countrycodes (code,country) values ('SV','El Salvador');
insert into countrycodes (code,country) values ('GQ','Equatorial Guinea');
insert into countrycodes (code,country) values ('EE','Estonia');
insert into countrycodes (code,country) values ('ET','Ethiopia');
insert into countrycodes (code,country) values ('FK','Falkland Isl.(UK)');
insert into countrycodes (code,country) values ('FO','Faroe Islands');
insert into countrycodes (code,country) values ('FJ','Fiji');
insert into countrycodes (code,country) values ('FI','Finland');
insert into countrycodes (code,country) values ('FR','France');
insert into countrycodes (code,country) values ('FX','France (European Ter.)');
insert into countrycodes (code,country) values ('TF','French Southern Terr.');
insert into countrycodes (code,country) values ('GA','Gabon');
insert into countrycodes (code,country) values ('GM','Gambia');
insert into countrycodes (code,country) values ('GE','Georgia');
insert into countrycodes (code,country) values ('DE','Germany');
insert into countrycodes (code,country) values ('GH','Ghana');
insert into countrycodes (code,country) values ('GI','Gibraltar');
insert into countrycodes (code,country) values ('GB','Great Britain (UK)');
insert into countrycodes (code,country) values ('GR','Greece');
insert into countrycodes (code,country) values ('GL','Greenland');
insert into countrycodes (code,country) values ('GD','Grenada');
insert into countrycodes (code,country) values ('GP','Guadeloupe (Fr.)');
insert into countrycodes (code,country) values ('GU','Guam (US)');
insert into countrycodes (code,country) values ('GT','Guatemala');
insert into countrycodes (code,country) values ('GN','Guinea');
insert into countrycodes (code,country) values ('GW','Guinea Bissau');
insert into countrycodes (code,country) values ('GY','Guyana');
insert into countrycodes (code,country) values ('GF','Guyana (Fr.)');
insert into countrycodes (code,country) values ('HT','Haiti');
insert into countrycodes (code,country) values ('HM','Heard & McDonald Isl.');
insert into countrycodes (code,country) values ('HN','Honduras');
insert into countrycodes (code,country) values ('HK','Hong Kong');
insert into countrycodes (code,country) values ('HU','Hungary');
insert into countrycodes (code,country) values ('IS','Iceland');
insert into countrycodes (code,country) values ('IN','India');
insert into countrycodes (code,country) values ('ID','Indonesia');
insert into countrycodes (code,country) values ('IR','Iran');
insert into countrycodes (code,country) values ('IQ','Iraq');
insert into countrycodes (code,country) values ('IE','Ireland');
insert into countrycodes (code,country) values ('IL','Israel');
insert into countrycodes (code,country) values ('IT','Italy');
insert into countrycodes (code,country) values ('CI','Ivory Coast');
insert into countrycodes (code,country) values ('JM','Jamaica');
insert into countrycodes (code,country) values ('JP','Japan');
insert into countrycodes (code,country) values ('JO','Jordan');
insert into countrycodes (code,country) values ('KZ','Kazachstan');
insert into countrycodes (code,country) values ('KE','Kenya');
insert into countrycodes (code,country) values ('KG','Kirgistan');
insert into countrycodes (code,country) values ('KI','Kiribati');
insert into countrycodes (code,country) values ('KP','Korea (North)');
insert into countrycodes (code,country) values ('KR','Korea (South)');
insert into countrycodes (code,country) values ('KW','Kuwait');
insert into countrycodes (code,country) values ('LA','Laos');
insert into countrycodes (code,country) values ('LV','Latvia');
insert into countrycodes (code,country) values ('LB','Lebanon');
insert into countrycodes (code,country) values ('LS','Lesotho');
insert into countrycodes (code,country) values ('LR','Liberia');
insert into countrycodes (code,country) values ('LY','Libya');
insert into countrycodes (code,country) values ('LI','Liechtenstein');
insert into countrycodes (code,country) values ('LT','Lithuania');
insert into countrycodes (code,country) values ('LU','Luxembourg');
insert into countrycodes (code,country) values ('MO','Macau');
insert into countrycodes (code,country) values ('MG','Madagascar');
insert into countrycodes (code,country) values ('MW','Malawi');
insert into countrycodes (code,country) values ('MY','Malaysia');
insert into countrycodes (code,country) values ('MV','Maldives');
insert into countrycodes (code,country) values ('ML','Mali');
insert into countrycodes (code,country) values ('MT','Malta');
insert into countrycodes (code,country) values ('MH','Marshall Islands');
insert into countrycodes (code,country) values ('MQ','Martinique (Fr.)');
insert into countrycodes (code,country) values ('MR','Mauritania');
insert into countrycodes (code,country) values ('MU','Mauritius');
insert into countrycodes (code,country) values ('MX','Mexico');
insert into countrycodes (code,country) values ('FM','Micronesia');
insert into countrycodes (code,country) values ('MD','Moldavia');
insert into countrycodes (code,country) values ('MC','Monaco');
insert into countrycodes (code,country) values ('MN','Mongolia');
insert into countrycodes (code,country) values ('MS','Montserrat');
insert into countrycodes (code,country) values ('MA','Morocco');
insert into countrycodes (code,country) values ('MZ','Mozambique');
insert into countrycodes (code,country) values ('MM','Myanmar');
insert into countrycodes (code,country) values ('NA','Namibia');
insert into countrycodes (code,country) values ('NR','Nauru');
insert into countrycodes (code,country) values ('NP','Nepal');
insert into countrycodes (code,country) values ('AN','Netherland Antilles');
insert into countrycodes (code,country) values ('NL','Netherlands');
insert into countrycodes (code,country) values ('NT','Neutral Zone');
insert into countrycodes (code,country) values ('NC','New Caledonia (Fr.)');
insert into countrycodes (code,country) values ('NZ','New Zealand');
insert into countrycodes (code,country) values ('NI','Nicaragua');
insert into countrycodes (code,country) values ('NE','Niger');
insert into countrycodes (code,country) values ('NG','Nigeria');
insert into countrycodes (code,country) values ('NU','Niue');
insert into countrycodes (code,country) values ('NF','Norfolk Island');
insert into countrycodes (code,country) values ('MP','Northern Mariana Isl.');
insert into countrycodes (code,country) values ('NO','Norway');
insert into countrycodes (code,country) values ('OM','Oman');
insert into countrycodes (code,country) values ('PK','Pakistan');
insert into countrycodes (code,country) values ('PW','Palau');
insert into countrycodes (code,country) values ('PA','Panama');
insert into countrycodes (code,country) values ('PG','Papua New');
insert into countrycodes (code,country) values ('PY','Paraguay');
insert into countrycodes (code,country) values ('PE','Peru');
insert into countrycodes (code,country) values ('PH','Philippines');
insert into countrycodes (code,country) values ('PN','Pitcairn');
insert into countrycodes (code,country) values ('PL','Poland');
insert into countrycodes (code,country) values ('PF','Polynesia (Fr.)');
insert into countrycodes (code,country) values ('PT','Portugal');
insert into countrycodes (code,country) values ('PR','Puerto Rico (US)');
insert into countrycodes (code,country) values ('QA','Qatar');
insert into countrycodes (code,country) values ('RE','Reunion (Fr.)');
insert into countrycodes (code,country) values ('RO','Romania');
insert into countrycodes (code,country) values ('RU','Russian Federation');
insert into countrycodes (code,country) values ('RW','Rwanda');
insert into countrycodes (code,country) values ('LC','Saint Lucia');
insert into countrycodes (code,country) values ('WS','Samoa');
insert into countrycodes (code,country) values ('SM','San Marino');
insert into countrycodes (code,country) values ('SA','Saudi Arabia');
insert into countrycodes (code,country) values ('SN','Senegal');
insert into countrycodes (code,country) values ('SC','Seychelles');
insert into countrycodes (code,country) values ('SL','Sierra Leone');
insert into countrycodes (code,country) values ('SG','Singapore');
insert into countrycodes (code,country) values ('SK','Slovak Republic');
insert into countrycodes (code,country) values ('SI','Slovenia');
insert into countrycodes (code,country) values ('SB','Solomon Islands');
insert into countrycodes (code,country) values ('SO','Somalia');
insert into countrycodes (code,country) values ('ZA','South Africa');
insert into countrycodes (code,country) values ('SU','Soviet Union');
insert into countrycodes (code,country) values ('ES','Spain');
insert into countrycodes (code,country) values ('LK','Sri Lanka');
insert into countrycodes (code,country) values ('SH','St. Helena');
insert into countrycodes (code,country) values ('PM','St. Pierre & Miquelon');
insert into countrycodes (code,country) values ('ST','St. Tome and Principe');
insert into countrycodes (code,country) values ('KN','St.Kitts Nevis Anguilla');
insert into countrycodes (code,country) values ('VC','St.Vincent & Grenadines');
insert into countrycodes (code,country) values ('SD','Sudan');
insert into countrycodes (code,country) values ('SR','Suriname');
insert into countrycodes (code,country) values ('SJ','Svalbard & Jan Mayen Is');
insert into countrycodes (code,country) values ('SZ','Swaziland');
insert into countrycodes (code,country) values ('SE','Sweden');
insert into countrycodes (code,country) values ('CH','Switzerland');
insert into countrycodes (code,country) values ('SY','Syria');
insert into countrycodes (code,country) values ('TJ','Tadjikistan');
insert into countrycodes (code,country) values ('TW','Taiwan');
insert into countrycodes (code,country) values ('TZ','Tanzania');
insert into countrycodes (code,country) values ('TH','Thailand');
insert into countrycodes (code,country) values ('TG','Togo');
insert into countrycodes (code,country) values ('TK','Tokelau');
insert into countrycodes (code,country) values ('TO','Tonga');
insert into countrycodes (code,country) values ('TT','Trinidad & Tobago');
insert into countrycodes (code,country) values ('TN','Tunisia');
insert into countrycodes (code,country) values ('TR','Turkey');
insert into countrycodes (code,country) values ('TM','Turkmenistan');
insert into countrycodes (code,country) values ('TC','Turks & Caicos Islands');
insert into countrycodes (code,country) values ('TV','Tuvalu');
insert into countrycodes (code,country) values ('UG','Uganda');
insert into countrycodes (code,country) values ('UA','Ukraine');
insert into countrycodes (code,country) values ('AE','United Arab Emirates');
insert into countrycodes (code,country) values ('UK','United Kingdom');
insert into countrycodes (code,country) values ('US','United States');
insert into countrycodes (code,country) values ('UY','Uruguay');
insert into countrycodes (code,country) values ('UM','US Minor outlying Isl.');
insert into countrycodes (code,country) values ('UZ','Uzbekistan');
insert into countrycodes (code,country) values ('VU','Vanuatu');
insert into countrycodes (code,country) values ('VA','Vatican City State');
insert into countrycodes (code,country) values ('VE','Venezuela');
insert into countrycodes (code,country) values ('VN','Vietnam');
insert into countrycodes (code,country) values ('VG','Virgin Islands (British)');
insert into countrycodes (code,country) values ('VI','Virgin Islands (US)');
insert into countrycodes (code,country) values ('WF','Wallis & Futuna Islands');
insert into countrycodes (code,country) values ('EH','Western Sahara');
insert into countrycodes (code,country) values ('YE','Yemen');
insert into countrycodes (code,country) values ('YU','Yugoslavia');
insert into countrycodes (code,country) values ('ZR','Zaire');
insert into countrycodes (code,country) values ('ZM','Zambia');
insert into countrycodes (code,country) values ('ZW','Zimbabwe');

Then create an index on the code field to make lookups fater.

CREATE INDEX countrycodes_code ON countrycodes (code)

[RizlaUK]

ah wow Dermot thanks, even in syntax, with citys to that saves me so much work

Thanks again

[bule]

AFAIK, Yugoslavia no longer exists:
insert into countrycodes (code,country) values ('YU','Yugoslavia');

Instead, there are Serbia, Kosovo and Monte Negro (apart from other few countries formed earlier in a fight for freedom bloodshed, only to be enslaved again, this time by the European banking empire... but you have those in that list Dermot). I don't know the shortcuts, though.

[Dermot]

AFAIK, Yugoslavia no longer exists:
insert into countrycodes (code,country) values ('YU','Yugoslavia');

Instead, there are Serbia, Kosovo and Monte Negro (apart from other few countries formed earlier in a fight for freedom bloodshed, only to be enslaved again, this time by the European banking empire... but you have those in that list Dermot). I don't know the shortcuts, though.

You are correct. I have had that list for ages.

RS - Serbia
ME - Montenegro

From what I can tell, there is no ISO code for Kosovo yet so just make one up.

[Dermot]

OK my list of cities is huge. 2,254,793 records so it is probably way to big for what you need. You can download it here. It is a zipped text file. It has two columns, Country and City. The Country column just has the country code. It's over 8mb zipped.

[Intrigued]

Thanks Dermot!

[RizlaUK]

Thanks Dermot , Thats a "huge" help.