Countries, States, Provinces, and Oceans MySQL Dump

Over the years, I've collected quite a collection of states and countries data for various projects. There's the U.S. States MySQL table that I started with (and later added latitudes and longitudes to), the subsequent Canadian provinces table, and finally, the countries table. They were handy, but a little all over the place. So, I went through this weekend and cleaned up all the data, organized it into neat and clean tables, and am now sharing those tables with you. There's also a newly shared table of the world's five major oceans. Hopefully, this data dump saves you a bit of time in your own coding journeys.

Without further ado, here are the tables:

1.   CREATE TABLE `geo_countries` (
2.     `defunct` char(1) DEFAULT NULL,
3.     `name` varchar(100) NOT NULL,
4.     `abv` char(2) NOT NULL COMMENT 'ISO 3661-1 alpha-2',
5.     `abv3` char(3) NOT NULL COMMENT 'ISO 3661-1 alpha-3',
6.     `code` char(3) NOT NULL COMMENT 'ISO 3661-1 numeric',
7.     `slug` varchar(100) NOT NULL,
8.     PRIMARY KEY (`abv`),
9.     UNIQUE KEY `slug` (`slug`)
10.  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
11.  
12.  INSERT INTO `geo_countries` (`defunct`,`name`,`abv`,`abv3`,`code`,`slug`) VALUES 
13.  ('','Afghanistan','AF','AFG',4,'afghanistan'),
14.  ('','Aland Islands','AX','ALA',248,'aland-islands'),
15.  ('','Albania','AL','ALB',8,'albania'),
16.  ('','Algeria','DZ','DZA',12,'algeria'),
17.  ('','American Samoa','AS','ASM',16,'american-samoa'),
18.  ('','Andorra','AD','AND',20,'andorra'),
19.  ('','Angola','AO','AGO',24,'angola'),
20.  ('','Anguilla','AI','AIA',660,'anguilla'),
21.  ('','Antigua and Barbuda','AG','ATG',28,'antigua-and-barbuda'),
22.  ('','Argentina','AR','ARG',32,'argentina'),
23.  ('','Armenia','AM','ARM',51,'armenia'),
24.  ('','Aruba','AW','ABW',533,'aruba'),
25.  ('','Australia','AU','AUS',36,'australia'),
26.  ('','Austria','AT','AUT',40,'austria'),
27.  ('','Azerbaijan','AZ','AZE',31,'azerbaijan'),
28.  ('','Bahamas','BS','BHS',44,'bahamas'),
29.  ('','Bahrain','BH','BHR',48,'bahrain'),
30.  ('','Bangladesh','BD','BGD',50,'bangladesh'),
31.  ('','Barbados','BB','BRB',52,'barbados'),
32.  ('','Belarus','BY','BLR',112,'belarus'),
33.  ('','Belgium','BE','BEL',56,'belgium'),
34.  ('','Belize','BZ','BLZ',84,'belize'),
35.  ('','Benin','BJ','BEN',204,'benin'),
36.  ('','Bermuda','BM','BMU',60,'bermuda'),
37.  ('','Bhutan','BT','BTN',64,'bhutan'),
38.  ('','Bolivia','BO','BOL',68,'bolivia'),
39.  ('','Bosnia and Herzegovina','BA','BIH',70,'bosnia-and-herzegovina'),
40.  ('','Botswana','BW','BWA',72,'botswana'),
41.  ('','Brazil','BR','BRA',76,'brazil'),
42.  ('','British Virgin Islands','VG','VGB',92,'british-virgin-islands'),
43.  ('','Brunei Darussalam','BN','BRN',96,'brunei-darussalam'),
44.  ('','Bulgaria','BG','BGR',100,'bulgaria'),
45.  ('','Burkina Faso','BF','BFA',854,'burkina-faso'),
46.  ('','Burundi','BI','BDI',108,'burundi'),
47.  ('','Cambodia','KH','KHM',116,'cambodia'),
48.  ('','Cameroon','CM','CMR',120,'cameroon'),
49.  ('','Canada','CA','CAN',124,'canada'),
50.  ('','Cape Verde','CV','CPV',132,'cape-verde'),
51.  ('','Cayman Islands','KY','CYM',136,'cayman-islands'),
52.  ('','Central African Republic','CF','CAF',140,'central-african-republic'),
53.  ('','Chad','TD','TCD',148,'chad'),
54.  ('','Chile','CL','CHL',152,'chile'),
55.  ('','China','CN','CHN',156,'china'),
56.  ('','Hong Kong','HK','HKG',344,'hong-kong'),
57.  ('','Macao','MO','MAC',446,'macao'),
58.  ('','Colombia','CO','COL',170,'colombia'),
59.  ('','Comoros','KM','COM',174,'comoros'),
60.  ('','Congo','CG','COG',178,'congo'),
61.  ('','Democratic Republic of the Congo','CD','COD',180,'democratic-republic-of-congo'),
62.  ('','Cook Islands','CK','COK',184,'cook-islands'),
63.  ('','Costa Rica','CR','CRI',188,'costa-rica'),
64.  ('','Cote d\'Ivoire','CI','CIV',384,'cote-divoire'),
65.  ('','Croatia','HR','HRV',191,'croatia'),
66.  ('','Cuba','CU','CUB',192,'cuba'),
67.  ('','Cyprus','CY','CYP',196,'cyprus'),
68.  ('','Czech Republic','CZ','CZE',203,'czech-republic'),
69.  ('','North Korea','KP','PRK',408,'north-korea'),
70.  ('','Denmark','DK','DNK',208,'denmark'),
71.  ('','Djibouti','DJ','DJI',262,'djibouti'),
72.  ('','Dominica','DM','DMA',212,'dominica'),
73.  ('','Dominican Republic','DO','DOM',214,'dominican-republic'),
74.  ('','Ecuador','EC','ECU',218,'ecuador'),
75.  ('','Egypt','EG','EGY',818,'egypt'),
76.  ('','El Salvador','SV','SLV',222,'el-salvador'),
77.  ('','Equatorial Guinea','GQ','GNQ',226,'equatorial-guinea'),
78.  ('','Eritrea','ER','ERI',232,'eritrea'),
79.  ('','Estonia','EE','EST',233,'estonia'),
80.  ('','Ethiopia','ET','ETH',231,'ethiopia'),
81.  ('','Faeroe Islands','FO','FRO',234,'faeroe-islands'),
82.  ('','Falkland Islands','FK','FLK',238,'falkland-islands'),
83.  ('','Fiji','FJ','FJI',242,'fiji'),
84.  ('','Finland','FI','FIN',246,'finland'),
85.  ('','France','FR','FRA',250,'france'),
86.  ('','French Guiana','GF','GUF',254,'french-guiana'),
87.  ('','French Polynesia','PF','PYF',258,'french-polynesia'),
88.  ('','Gabon','GA','GAB',266,'gabon'),
89.  ('','Gambia','GM','GMB',270,'gambia'),
90.  ('','Georgia','GE','GEO',268,'georgia'),
91.  ('','Germany','DE','DEU',276,'germany'),
92.  ('','Ghana','GH','GHA',288,'ghana'),
93.  ('','Gibraltar','GI','GIB',292,'gibraltar'),
94.  ('','Greece','GR','GRC',300,'greece'),
95.  ('','Greenland','GL','GRL',304,'greenland'),
96.  ('','Grenada','GD','GRD',308,'grenada'),
97.  ('','Guadeloupe','GP','GLP',312,'guadeloupe'),
98.  ('','Guam','GU','GUM',316,'guam'),
99.  ('','Guatemala','GT','GTM',320,'guatemala'),
100. ('','Guernsey','GG','GGY',831,'guernsey'),
101. ('','Guinea','GN','GIN',324,'guinea'),
102. ('','Guinea-Bissau','GW','GNB',624,'guinea-bissau'),
103. ('','Guyana','GY','GUY',328,'guyana'),
104. ('','Haiti','HT','HTI',332,'haiti'),
105. ('','Holy See','VA','VAT',336,'holy-see'),
106. ('','Honduras','HN','HND',340,'honduras'),
107. ('','Hungary','HU','HUN',348,'hungary'),
108. ('','Iceland','IS','ISL',352,'iceland'),
109. ('','India','IN','IND',356,'india'),
110. ('','Indonesia','ID','IDN',360,'indonesia'),
111. ('','Iran','IR','IRN',364,'iran'),
112. ('','Iraq','IQ','IRQ',368,'iraq'),
113. ('','Ireland','IE','IRL',372,'ireland'),
114. ('','Isle of Man','IM','IMN',833,'isle-of-man'),
115. ('','Israel','IL','ISR',376,'israel'),
116. ('','Italy','IT','ITA',380,'italy'),
117. ('','Jamaica','JM','JAM',388,'jamaica'),
118. ('','Japan','JP','JPN',392,'japan'),
119. ('','Jersey','JE','JEY',832,'jersey'),
120. ('','Jordan','JO','JOR',400,'jordan'),
121. ('','Kazakhstan','KZ','KAZ',398,'kazakhstan'),
122. ('','Kenya','KE','KEN',404,'kenya'),
123. ('','Kiribati','KI','KIR',296,'kiribati'),
124. ('','Kuwait','KW','KWT',414,'kuwait'),
125. ('','Kyrgyzstan','KG','KGZ',417,'kyrgyzstan'),
126. ('','Laos','LA','LAO',418,'laos'),
127. ('','Latvia','LV','LVA',428,'latvia'),
128. ('','Lebanon','LB','LBN',422,'lebanon'),
129. ('','Lesotho','LS','LSO',426,'lesotho'),
130. ('','Liberia','LR','LBR',430,'liberia'),
131. ('','Libyan Arab Jamahiriya','LY','LBY',434,'libyan-arab-jamahiriya'),
132. ('','Liechtenstein','LI','LIE',438,'liechtenstein'),
133. ('','Lithuania','LT','LTU',440,'lithuania'),
134. ('','Luxembourg','LU','LUX',442,'luxembourg'),
135. ('','Madagascar','MG','MDG',450,'madagascar'),
136. ('','Malawi','MW','MWI',454,'malawi'),
137. ('','Malaysia','MY','MYS',458,'malaysia'),
138. ('','Maldives','MV','MDV',462,'maldives'),
139. ('','Mali','ML','MLI',466,'mali'),
140. ('','Malta','MT','MLT',470,'malta'),
141. ('','Marshall Islands','MH','MHL',584,'marshall-islands'),
142. ('','Martinique','MQ','MTQ',474,'martinique'),
143. ('','Mauritania','MR','MRT',478,'mauritania'),
144. ('','Mauritius','MU','MUS',480,'mauritius'),
145. ('','Mayotte','YT','MYT',175,'mayotte'),
146. ('','Mexico','MX','MEX',484,'mexico'),
147. ('','Micronesia','FM','FSM',583,'micronesia'),
148. ('','Monaco','MC','MCO',492,'monaco'),
149. ('','Mongolia','MN','MNG',496,'mongolia'),
150. ('','Montenegro','ME','MNE',499,'montenegro'),
151. ('','Montserrat','MS','MSR',500,'montserrat'),
152. ('','Morocco','MA','MAR',504,'morocco'),
153. ('','Mozambique','MZ','MOZ',508,'mozambique'),
154. ('','Myanmar','MM','MMR',104,'myanmar'),
155. ('','Namibia','NA','NAM',516,'namibia'),
156. ('','Nauru','NR','NRU',520,'nauru'),
157. ('','Nepal','NP','NPL',524,'nepal'),
158. ('','Netherlands','NL','NLD',528,'netherlands'),
159. ('','Netherlands Antilles','AN','ANT',530,'netherlands-antilles'),
160. ('','New Caledonia','NC','NCL',540,'new-caledonia'),
161. ('','New Zealand','NZ','NZL',554,'new-zealand'),
162. ('','Nicaragua','NI','NIC',558,'nicaragua'),
163. ('','Niger','NE','NER',562,'niger'),
164. ('','Nigeria','NG','NGA',566,'nigeria'),
165. ('','Niue','NU','NIU',570,'niue'),
166. ('','Norfolk Island','NF','NFK',574,'norfolk-island'),
167. ('','Northern Mariana Islands','MP','MNP',580,'northern-mariana-islands'),
168. ('','Norway','NO','NOR',578,'norway'),
169. ('','Palestine','PS','PSE',275,'palestine'),
170. ('','Oman','OM','OMN',512,'oman'),
171. ('','Pakistan','PK','PAK',586,'pakistan'),
172. ('','Palau','PW','PLW',585,'palau'),
173. ('','Panama','PA','PAN',591,'panama'),
174. ('','Papua New Guinea','PG','PNG',598,'papua-new-guinea'),
175. ('','Paraguay','PY','PRY',600,'paraguay'),
176. ('','Peru','PE','PER',604,'peru'),
177. ('','Philippines','PH','PHL',608,'philippines'),
178. ('','Pitcairn','PN','PCN',612,'pitcairn'),
179. ('','Poland','PL','POL',616,'poland'),
180. ('','Portugal','PT','PRT',620,'portugal'),
181. ('','Puerto Rico','PR','PRI',630,'puerto-rico'),
182. ('','Qatar','QA','QAT',634,'qatar'),
183. ('','South Korea','KR','KOR',410,'south-korea'),
184. ('','Moldova','MD','MDA',498,'moldova'),
185. ('','Reunion','RE','REU',638,'reunion'),
186. ('','Romania','RO','ROU',642,'romania'),
187. ('','Russian Federation','RU','RUS',643,'russian-federation'),
188. ('','Rwanda','RW','RWA',646,'rwanda'),
189. ('','Saint-Barthelemy','BL','BLM',652,'saint-barthelemy'),
190. ('','Saint Helena','SH','SHN',654,'saint-helena'),
191. ('','Saint Kitts and Nevis','KN','KNA',659,'saint-kitts-and-nevis'),
192. ('','Saint Lucia','LC','LCA',662,'saint-lucia'),
193. ('','Saint-Martin','MF','MAF',663,'saint-martin'),
194. ('','Saint Pierre and Miquelon','PM','SPM',666,'saint-pierre-and-miquelon'),
195. ('','Saint Vincent and the Grenadines','VC','VCT',670,'saint-vincent-and-grenadines'),
196. ('','Samoa','WS','WSM',882,'samoa'),
197. ('','San Marino','SM','SMR',674,'san-marino'),
198. ('','Sao Tome and Principe','ST','STP',678,'sao-tome-and-principe'),
199. ('','Saudi Arabia','SA','SAU',682,'saudi-arabia'),
200. ('','Senegal','SN','SEN',686,'senegal'),
201. ('','Serbia','RS','SRB',688,'serbia'),
202. ('','Seychelles','SC','SYC',690,'seychelles'),
203. ('','Sierra Leone','SL','SLE',694,'sierra-leone'),
204. ('','Singapore','SG','SGP',702,'singapore'),
205. ('','Slovakia','SK','SVK',703,'slovakia'),
206. ('','Slovenia','SI','SVN',705,'slovenia'),
207. ('','Solomon Islands','SB','SLB',90,'solomon-islands'),
208. ('','Somalia','SO','SOM',706,'somalia'),
209. ('','South Africa','ZA','ZAF',710,'south-africa'),
210. ('','Spain','ES','ESP',724,'spain'),
211. ('','Sri Lanka','LK','LKA',144,'sri-lanka'),
212. ('','Sudan','SD','SDN',729,'sudan'),
213. ('','Suriname','SR','SUR',740,'suriname'),
214. ('','Svalbard and Jan Mayen Islands','SJ','SJM',744,'svalbard-and-jan-mayen-islands'),
215. ('','Swaziland','SZ','SWZ',748,'swaziland'),
216. ('','Sweden','SE','SWE',752,'sweden'),
217. ('','Switzerland','CH','CHE',756,'switzerland'),
218. ('','Syrian Arab Republic','SY','SYR',760,'syrian-arab-republic'),
219. ('','Tajikistan','TJ','TJK',762,'tajikistan'),
220. ('','Thailand','TH','THA',764,'thailand'),
221. ('','Macedonia','MK','MKD',807,'macedonia'),
222. ('','Timor-Leste','TP','TLS',626,'timor-leste'),
223. ('','Togo','TG','TGO',768,'togo'),
224. ('','Tokelau','TK','TKL',772,'tokelau'),
225. ('','Tonga','TO','TON',776,'tonga'),
226. ('','Trinidad and Tobago','TT','TTO',780,'trinidad-and-tobago'),
227. ('','Tunisia','TN','TUN',788,'tunisia'),
228. ('','Turkey','TR','TUR',792,'turkey'),
229. ('','Turkmenistan','TM','TKM',795,'turkmenistan'),
230. ('','Turks and Caicos Islands','TC','TCA',796,'turks-and-caicos-islands'),
231. ('','Tuvalu','TV','TUV',798,'tuvalu'),
232. ('','Uganda','UG','UGA',800,'uganda'),
233. ('','Ukraine','UA','UKR',804,'ukraine'),
234. ('','United Arab Emirates','AE','ARE',784,'united-arab-emirates'),
235. ('','United Kingdom','UK','GBR',826,'united-kingdom'),
236. ('','Tanzania','TZ','TZA',834,'tanzania'),
237. ('','United States','US','USA',840,'united-states'),
238. ('','U.S. Virgin Islands','VI','VIR',850,'us-virgin-islands'),
239. ('','Uruguay','UY','URY',858,'uruguay'),
240. ('','Uzbekistan','UZ','UZB',860,'uzbekistan'),
241. ('','Vanuatu','VU','VUT',548,'vanuatu'),
242. ('','Venezuela','VE','VEN',862,'venezuela'),
243. ('','Viet Nam','VN','VNM',704,'viet-nam'),
244. ('','Wallis and Futuna Islands','WF','WLF',876,'wallis-and-futuna-islands'),
245. ('','Western Sahara','EH','ESH',732,'western-sahara'),
246. ('','Yemen','YE','YEM',887,'yemen'),
247. ('','Zambia','ZM','ZMB',894,'zambia'),
248. ('','Zimbabwe','ZW','ZWE',716,'zimbabwe'),
249. ('','South Sudan','SS','SSD',728,'south-sudan');
250. 
251. CREATE TABLE `geo_states` (
252.   `name` varchar(50) NOT NULL DEFAULT '',
253.   `abv` char(2) NOT NULL DEFAULT '',
254.   `country` char(2) NOT NULL,
255.   `is_state` char(1) DEFAULT NULL,
256.   `is_lower48` char(1) DEFAULT NULL,
257.   `slug` varchar(50) NOT NULL,
258.   `latitude` float(9,6) DEFAULT NULL,
259.   `longitude` float(9,6) DEFAULT NULL,
260.   `population` bigint(20) unsigned DEFAULT NULL,
261.   `area` float(8,2) unsigned DEFAULT NULL,
262.   PRIMARY KEY (`abv`,`country`)
263. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
264. 
265. INSERT INTO `geo_states` (`name`,`abv`,`country`,`is_state`,`is_lower48`,`slug`,`latitude`,`longitude`,`population`,`area`) VALUES 
266. ('Alabama','AL','US','y','y','alabama',32.806671,-86.791130,4779736,50744.00),
267. ('Alaska','AK','US','y','n','alaska',61.370716,-152.404419,710231,571951.25),
268. ('Arizona','AZ','US','y','y','arizona',33.729759,-111.431221,6392017,113634.57),
269. ('Arkansas','AR','US','y','y','arkansas',34.969704,-92.373123,2915918,52068.17),
270. ('California','CA','US','y','y','california',36.116203,-119.681564,37253956,155939.52),
271. ('Colorado','CO','US','y','y','colorado',39.059811,-105.311104,5029196,103717.53),
272. ('Connecticut','CT','US','y','y','connecticut',41.597782,-72.755371,3574097,4844.80),
273. ('Delaware','DE','US','y','y','delaware',39.318523,-75.507141,897934,1953.56),
274. ('District of Columbia','DC','US','n','n','district-of-columbia',38.897438,-77.026817,601723,68.34),
275. ('Florida','FL','US','y','y','florida',27.766279,-81.686783,18801310,53926.82),
276. ('Georgia','GA','US','y','y','georgia',33.040619,-83.643074,9687653,57906.14),
277. ('Hawaii','HI','US','y','n','hawaii',21.094318,-157.498337,1360301,6422.62),
278. ('Idaho','ID','US','y','y','idaho',44.240459,-114.478828,1567582,82747.21),
279. ('Illinois','IL','US','y','y','illinois',40.349457,-88.986137,12830632,55583.58),
280. ('Indiana','IN','US','y','y','indiana',39.849426,-86.258278,6483802,35866.90),
281. ('Iowa','IA','US','y','y','iowa',42.011539,-93.210526,3046355,55869.36),
282. ('Kansas','KS','US','y','y','kansas',38.526600,-96.726486,2853118,81814.88),
283. ('Kentucky','KY','US','y','y','kentucky',37.668140,-84.670067,4339367,39728.18),
284. ('Louisiana','LA','US','y','y','louisiana',31.169546,-91.867805,4533372,43561.85),
285. ('Maine','ME','US','y','y','maine',44.693947,-69.381927,1328361,30861.55),
286. ('Maryland','MD','US','y','y','maryland',39.063946,-76.802101,5773552,9773.82),
287. ('Massachusetts','MA','US','y','y','massachusetts',42.230171,-71.530106,6547629,7840.02),
288. ('Michigan','MI','US','y','y','michigan',43.326618,-84.536095,9883640,56803.82),
289. ('Minnesota','MN','US','y','y','minnesota',45.694454,-93.900192,5303925,79610.08),
290. ('Mississippi','MS','US','y','y','mississippi',32.741646,-89.678696,2967297,46906.96),
291. ('Missouri','MO','US','y','y','missouri',38.456085,-92.288368,5988927,68885.93),
292. ('Montana','MT','US','y','y','montana',46.921925,-110.454353,989415,145552.44),
293. ('Nebraska','NE','US','y','y','nebraska',41.125370,-98.268082,1826341,76872.41),
294. ('Nevada','NV','US','y','y','nevada',38.313515,-117.055374,2700551,109825.99),
295. ('New Hampshire','NH','US','y','y','new-hampshire',43.452492,-71.563896,1316470,8968.10),
296. ('New Jersey','NJ','US','y','y','new-jersey',40.298904,-74.521011,8791894,7417.34),
297. ('New Mexico','NM','US','y','y','new-mexico',34.840515,-106.248482,2059179,121355.53),
298. ('New York','NY','US','y','y','new-york',42.165726,-74.948051,19378102,47213.79),
299. ('North Carolina','NC','US','y','y','north-carolina',35.630066,-79.806419,9535483,48710.88),
300. ('North Dakota','ND','US','y','y','north-dakota',47.528912,-99.784012,672591,68975.93),
301. ('Ohio','OH','US','y','y','ohio',40.388783,-82.764915,11536504,40948.38),
302. ('Oklahoma','OK','US','y','y','oklahoma',35.565342,-96.928917,3751351,68667.06),
303. ('Oregon','OR','US','y','y','oregon',44.572021,-122.070938,3831074,95996.79),
304. ('Pennsylvania','PA','US','y','y','pennsylvania',40.590752,-77.209755,12702379,44816.61),
305. ('Rhode Island','RI','US','y','y','rhode-island',41.680893,-71.511780,1052567,1044.93),
306. ('South Carolina','SC','US','y','y','south-carolina',33.856892,-80.945007,4625364,30109.47),
307. ('South Dakota','SD','US','y','y','south-dakota',44.299782,-99.438828,814180,75884.64),
308. ('Tennessee','TN','US','y','y','tennessee',35.747845,-86.692345,6346105,41217.12),
309. ('Texas','TX','US','y','y','texas',31.054487,-97.563461,25145561,261797.12),
310. ('Utah','UT','US','y','y','utah',40.150032,-111.862434,2763885,82143.65),
311. ('Vermont','VT','US','y','y','vermont',44.045876,-72.710686,625741,9249.56),
312. ('Virginia','VA','US','y','y','virginia',37.769337,-78.169968,8001024,39594.07),
313. ('Washington','WA','US','y','y','washington',47.400902,-121.490494,6724540,66544.06),
314. ('West Virginia','WV','US','y','y','west-virginia',38.491226,-80.954453,1852994,24077.73),
315. ('Wisconsin','WI','US','y','y','wisconsin',44.268543,-89.616508,5686986,54310.10),
316. ('Wyoming','WY','US','y','y','wyoming',42.755966,-107.302490,563626,97100.40),
317. ('Alberta','AB','CA',NULL,NULL,'alberta',NULL,NULL,NULL,NULL),
318. ('British Columbia','BC','CA',NULL,NULL,'british-columbia',NULL,NULL,NULL,NULL),
319. ('Manitoba','MB','CA',NULL,NULL,'manitoba',NULL,NULL,NULL,NULL),
320. ('New Brunswick','NB','CA',NULL,NULL,'new-brunswick',NULL,NULL,NULL,NULL),
321. ('Newfoundland and Labrador','NL','CA',NULL,NULL,'newfoundland-and-labrador',NULL,NULL,NULL,NULL),
322. ('Northwest Territories','NT','CA',NULL,NULL,'northwest-territories',NULL,NULL,NULL,NULL),
323. ('Nova Scotia','NS','CA',NULL,NULL,'nova-scotia',NULL,NULL,NULL,NULL),
324. ('Nunavut','NU','CA',NULL,NULL,'nunavut',NULL,NULL,NULL,NULL),
325. ('Ontario','ON','CA',NULL,NULL,'ontario',NULL,NULL,NULL,NULL),
326. ('Prince Edward Island','PE','CA',NULL,NULL,'prince-edward-island',NULL,NULL,NULL,NULL),
327. ('Quebec','QC','CA',NULL,NULL,'quebec',NULL,NULL,NULL,NULL),
328. ('Saskatchewan','SK','CA',NULL,NULL,'saskatchewan',NULL,NULL,NULL,NULL),
329. ('Yukon','YT','CA',NULL,NULL,'yukon',NULL,NULL,NULL,NULL),
330. ('Aguascalientes','AG','MX',NULL,NULL,'aguascalientes',NULL,NULL,NULL,NULL),
331. ('Baja California','BC','MX',NULL,NULL,'baja-california',NULL,NULL,NULL,NULL),
332. ('Baja California Sur','BS','MX',NULL,NULL,'baja-california-sur',NULL,NULL,NULL,NULL),
333. ('Campeche','CM','MX',NULL,NULL,'campeche',NULL,NULL,NULL,NULL),
334. ('Chiapas','CS','MX',NULL,NULL,'chiapas',NULL,NULL,NULL,NULL),
335. ('Chihuahua','CH','MX',NULL,NULL,'chihuahua',NULL,NULL,NULL,NULL),
336. ('Coahuila','CO','MX',NULL,NULL,'coahuila',NULL,NULL,NULL,NULL),
337. ('Colima','CL','MX',NULL,NULL,'colima',NULL,NULL,NULL,NULL),
338. ('Federal District','DF','MX',NULL,NULL,'federal-district',NULL,NULL,NULL,NULL),
339. ('Durango','DG','MX',NULL,NULL,'durango',NULL,NULL,NULL,NULL),
340. ('Guanajuato','GT','MX',NULL,NULL,'guanajuato',NULL,NULL,NULL,NULL),
341. ('Guerrero','GR','MX',NULL,NULL,'guerrero',NULL,NULL,NULL,NULL),
342. ('Hidalgo','HG','MX',NULL,NULL,'hidalgo',NULL,NULL,NULL,NULL),
343. ('Jalisco','JA','MX',NULL,NULL,'jalisco',NULL,NULL,NULL,NULL),
344. ('Mexico State','ME','MX',NULL,NULL,'mexico-state',NULL,NULL,NULL,NULL),
345. ('Michoacán','MI','MX',NULL,NULL,'michoacan',NULL,NULL,NULL,NULL),
346. ('Morelos','MO','MX',NULL,NULL,'morelos',NULL,NULL,NULL,NULL),
347. ('Nayarit','NA','MX',NULL,NULL,'nayarit',NULL,NULL,NULL,NULL),
348. ('Nuevo León','NL','MX',NULL,NULL,'nuevo-leon',NULL,NULL,NULL,NULL),
349. ('Oaxaca','OA','MX',NULL,NULL,'oaxaca',NULL,NULL,NULL,NULL),
350. ('Puebla','PB','MX',NULL,NULL,'puebla',NULL,NULL,NULL,NULL),
351. ('Querétaro','QE','MX',NULL,NULL,'queretaro',NULL,NULL,NULL,NULL),
352. ('Quintana Roo','QR','MX',NULL,NULL,'quintana-roo',NULL,NULL,NULL,NULL),
353. ('San Luis Potosí','SL','MX',NULL,NULL,'san-luis-potosi',NULL,NULL,NULL,NULL),
354. ('Sinaloa','SI','MX',NULL,NULL,'sinaloa',NULL,NULL,NULL,NULL),
355. ('Sonora','SO','MX',NULL,NULL,'sonora',NULL,NULL,NULL,NULL),
356. ('Tabasco','TB','MX',NULL,NULL,'tabasco',NULL,NULL,NULL,NULL),
357. ('Tamaulipas','TM','MX',NULL,NULL,'tamaulipas',NULL,NULL,NULL,NULL),
358. ('Tlaxcala','TL','MX',NULL,NULL,'tlaxcala',NULL,NULL,NULL,NULL),
359. ('Veracruz','VE','MX',NULL,NULL,'veracruz',NULL,NULL,NULL,NULL),
360. ('Yucatán','YU','MX',NULL,NULL,'yucatan',NULL,NULL,NULL,NULL),
361. ('Zacatecas','ZA','MX',NULL,NULL,'zacatecas',NULL,NULL,NULL,NULL);
362. 
363. CREATE TABLE `geo_oceans` (
364.   `abv` char(2) COLLATE utf8_bin NOT NULL,
365.   `name` varchar(100) COLLATE utf8_bin NOT NULL,
366.   `slug` varchar(100) COLLATE utf8_bin NOT NULL,
367.   PRIMARY KEY (`abv`)
368. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
369. 
370. INSERT INTO `geo_oceans` (`abv`,`name`,`slug`) VALUES 
371. ('AR','Arctic Ocean','arctic-ocean'),
372. ('AT','Atlantic Ocean','atlantic-ocean'),
373. ('IN','Indian Ocean','indian-ocean'),
374. ('PA','Pacific Ocean','pacific-ocean'),
375. ('SO','Southern Ocean','southern-ocean');

For an easy way to convert these abbrevations to names, check out our State and Country Abbreviations to Names function.


Comments

Loading…

This post was published on November 1st, 2014 by Robert James Reese in the following categories: Data Dumps and MySQL. Before using any of the code or other content in this post, you must read and agree to our terms of use.