Canadian Provinces in MySQL Table

I posted a list of U.S. State Abbreviations up here a while ago, then found myself needing the Canadian provinces as well. Here's the MySQL code to easily add them to that table of abbreviations and names. Hope it saves you some time!

1.   CREATE TABLE IF NOT EXISTS `geo_states` (
2.     `name` varchar(50) NOT NULL DEFAULT '',
3.     `abv` char(2) NOT NULL DEFAULT '',
4.     `country` char(2) NOT NULL,
5.     `latitude` decimal(9,6) DEFAULT NULL,
6.     `longitude` decimal(9,6) DEFAULT NULL,
7.     `slug` varchar(50) NOT NULL,
10.  INSERT INTO `geo_states` (`name`, `abv`, `country`, `slug`) VALUES
11.  ('Alberta','AB','CA','alberta');
12.  ('British Columbia','BC','CA','british-columbia');
13.  ('Manitoba','MB','CA','manitoba');
14.  ('New Brunswick','NB','CA','new-brunswick');
15.  ('Newfoundland and Labrador','NL','CA','newfoundland-and-labrador');
16.  ('Northwest Territories','NT','CA','northwest-territories');
17.  ('Nova Scotia','NS','CA','nova-scotia');
18.  ('Nunavut','NU','CA','nunavut');
19.  ('Ontario','ON','CA','ontario');
20.  ('Prince Edward Island','PE','CA','prince-edward-island');
21.  ('Quebec','QC','CA','quebec');
22.  ('Saskatchewan','SK','CA','saskatchewan');
23.  ('Yukon','YT','CA','yukon');
25.  ALTER TABLE `geo_states` ADD PRIMARY KEY (`abv`,`country`);

This data can also be found in our combined countries, states, provinces, and oceans MySQL dump. There is also a collection of Countries MySQL data posted here on the blog.



This post was first published on September 5th, 2009 and last updated 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.