CREATE DATABASE IF NOT EXISTS US_LOCATIONS;
USE US_LOCATIONS;
--
-- Table structure for table `us_states`
--
DROP TABLE IF EXISTS US_STATES;
CREATE TABLE US_STATES (
ID int(11) NOT NULL AUTO_INCREMENT,
STATE_CODE char(2) NOT NULL,
STATE_NAME varchar(50) NOT NULL,
PRIMARY KEY (ID)
);
INSERT INTO US_STATES VALUES
(1,'AL','Alabama'),
(2,'AK','Alaska'),
(3,'AZ','Arizona'),
(4,'AR','Arkansas'),
(5,'CA','California'),
(6,'CO','Colorado'),
(7,'CT','Connecticut'),
(8,'DE','Delaware'),
(9,'DC','District of Columbia'),
(10,'FL','Florida'),
(11,'GA','Georgia'),
(12,'HI','Hawaii'),
(13,'ID','Idaho'),
(14,'IL','Illinois'),
(15,'IN','Indiana'),
(16,'IA','Iowa'),
(17,'KS','Kansas'),
(18,'KY','Kentucky'),
(19,'LA','Louisiana'),
(20,'ME','Maine'),
(21,'MD','Maryland'),
(22,'MA','Massachusetts'),
(23,'MI','Michigan'),
(24,'MN','Minnesota'),
(25,'MS','Mississippi'),
(26,'MO','Missouri'),
(27,'MT','Montana'),
(28,'NE','Nebraska'),
(29,'NV','Nevada'),
(30,'NH','New Hampshire'),
(31,'NJ','New Jersey'),
(32,'NM','New Mexico'),
(33,'NY','New York'),
(34,'NC','North Carolina'),
(35,'ND','North Dakota'),
(36,'OH','Ohio'),
(37,'OK','Oklahoma'),
(38,'OR','Oregon'),
(39,'PA','Pennsylvania'),
(40,'PR','Puerto Rico'),
(41,'RI','Rhode Island'),
(42,'SC','South Carolina'),
(43,'SD','South Dakota'),
(44,'TN','Tennessee'),
(45,'TX','Texas'),
(46,'UT','Utah'),
(47,'VT','Vermont'),
(48,'VA','Virginia'),
(49,'WA','Washington'),
(50,'WV','West Virginia'),
(51,'WI','Wisconsin'),
(52,'WY','Wyoming');
--
-- Table structure for table `us_cities`
--
DROP TABLE IF EXISTS US_CITIES;
CREATE TABLE US_CITIES (
ID int(11) NOT NULL AUTO_INCREMENT,
ID_STATE int(11) NOT NULL,
CITY varchar(50) NOT NULL,
COUNTY varchar(50) NOT NULL,
LATITUDE double NOT NULL,
LONGITUDE double NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (ID_STATE) REFERENCES US_STATES(ID)
);
INSERT INTO US_CITIES VALUES
(1,2,'Adak','Aleutians West',55.999722,-161.207778),
(2,2,'Akiachak','Bethel',60.891854,-161.39233),
(3,2,'Akiak','Bethel',60.890632,-161.199325),
(4,2,'Akutan','Aleutians East',54.143012,-165.785368),
(5,2,'Alakanuk','Wade Hampton',62.746967,-164.60228),
(6,2,'Aleknagik','Dillingham',59.269688,-158.619882),
(7,2,'Allakaket','Yukon Koyukuk',66.543197,-152.712155),
(8,2,'Ambler','Northwest Arctic',67.46951,-156.455652),
(9,2,'Anaktuvuk Pass','North Slope',68.11878,-151.679005),
(10,2,'Anchor Point','Kenai Peninsula',59.788818,-151.732933),
(11,2,'Anchorage','Anchorage',61.211571,-149.876077),
(12,2,'Anderson','Denali',64.300693,-149.1718),
(13,2,'Angoon','Skagway Hoonah Angoon',57.569832,-134.371052),
(14,2,'Aniak','Bethel',61.691648,-157.758502),
(15,2,'Anvik','Yukon Koyukuk',62.830913,-160.130441),
(16,2,'Arctic Village','Yukon Koyukuk',68.077395,-145.423115),
(17,2,'Atka','Aleutians West',52.199167,-174.213333),
(18,2,'Atqasuk','North Slope',71.290556,-156.788611),
(19,2,'Auke Bay','Juneau',58.3833,-134.6597),
(20,2,'Barrow','North Slope',71.234637,-156.817409),
(21,2,'Beaver','Yukon Koyukuk',66.33883,-147.279803),
(22,2,'Bethel','Bethel',60.832389,-161.824053),
(23,2,'Bettles Field','Yukon Koyukuk',67.100495,-151.062414),
(24,2,'Big Lake','Matanuska Susitna',61.5842,-149.4401),
(25,2,'Brevig Mission','Nome',65.334187,-166.478578),
(26,2,'Buckland','Northwest Arctic',65.981052,-161.131676),
(27,2,'Cantwell','Denali',63.395458,-148.89735),
(28,2,'Central','Yukon Koyukuk',65.468058,-144.74886),
(29,2,'Chalkyitsik','Yukon Koyukuk',66.719,-143.638121),
(30,2,'Chefornak','Bethel',60.153746,-164.210294),
(31,2,'Chevak','Wade Hampton',61.583982,-164.776457),
(32,2,'Chicken','Southeast Fairbanks',64.075,-141.933333),
(33,2,'Chignik','Lake And Peninsula',56.301639,-158.415696),
(34,2,'Chignik Lagoon','Lake And Peninsula',56.251277,-158.673528),
(35,2,'Chignik Lake','Lake And Peninsula',56.255556,-158.7625),
(36,2,'Chitina','Valdez Cordova',61.516667,-144.436667),
(37,2,'Chugiak','Anchorage',61.409802,-149.453736),
(38,2,'Circle','Yukon Koyukuk',65.824542,-144.08262),
(39,2,'Clam Gulch','Kenai Peninsula',60.201603,-151.422628),
(40,2,'Clarks Point','Dillingham',58.84921,-158.451241),
(41,2,'Clear','Denali',64.418121,-149.139885),
(42,2,'Coffman Cove','Prince Wales Ketchikan',55.342222,-131.646111),
(43,2,'Cold Bay','Aleutians East',55.610952,-161.942941),
(44,2,'Cooper Landing','Kenai Peninsula',60.476692,-149.823514),
(45,2,'Copper Center','Valdez Cordova',61.91581,-144.97793),
(46,2,'Cordova','Valdez Cordova',60.102558,-147.943316),
(47,2,'Craig','Prince Wales Ketchikan',55.47317,-133.117081),
(48,2,'Crooked Creek','Bethel',61.818072,-158.002483),
(49,2,'Deering','Northwest Arctic',66.062265,-162.711951),
(50,2,'Delta Junction','Southeast Fairbanks',64.005426,-145.613611),
(51,2,'Denali National Park','Denali',63.516075,-149.539532),
(52,2,'Dillingham','Dillingham',59.059279,-158.973533),
(53,2,'Douglas','Juneau',58.275597,-134.395041),
(54,2,'Dutch Harbor','Aleutians West',53.362757,-167.510656),
(55,2,'Eagle','Southeast Fairbanks',64.783333,-141.2),
(56,2,'Eagle River','Anchorage',61.311357,-149.508515),
(57,2,'Eek','Bethel',60.215058,-162.032341),
(58,2,'Egegik','Lake And Peninsula',58.206174,-157.342202),
(59,2,'Eielson Afb','Fairbanks North Star',64.67352,-147.08051),
(60,2,'Ekwok','Dillingham',59.362792,-157.478211),
(61,2,'Elfin Cove','Skagway Hoonah Angoon',58.195,-136.358333),
(62,2,'Elim','Nome',64.621662,-162.260371),
(63,2,'Elmendorf Afb','Anchorage',61.251531,-149.812667),
(64,2,'Emmonak','Wade Hampton',62.827404,-164.131298),
(65,2,'Ester','Fairbanks North Star',64.846667,-148.016667),
(66,2,'Fairbanks','Fairbanks North Star',64.840238,-147.710431),
(67,2,'False Pass','Aleutians East',54.841028,-163.436845),
(68,2,'Fort Greely','Southeast Fairbanks',64.036667,-145.733333),
(69,2,'Fort Richardson','Anchorage',61.275256,-149.675454),
(70,2,'Fort Wainwright','Fairbanks North Star',64.828303,-147.655673),
(71,2,'Fort Yukon','Yukon Koyukuk',66.520744,-145.306439),
(72,2,'Gakona','Valdez Cordova',62.654744,-143.568393),
(73,2,'Galena','Yukon Koyukuk',64.760784,-156.797701),
(74,2,'Gambell','Nome',63.776555,-171.701685),
(75,2,'Girdwood','Anchorage',60.9425,-149.166389),
(76,2,'Glennallen','Valdez Cordova',62.103895,-145.661684),
(77,2,'Goodnews Bay','Bethel',59.085008,-161.587146),
(78,2,'Grayling','Yukon Koyukuk',63.372013,-159.404907),
(79,2,'Gustavus','Skagway Hoonah Angoon',58.42835,-135.761542),
(80,2,'Haines','Haines',59.251886,-135.542032),
(81,2,'Healy','Denali',63.917123,-149.011128),
(82,2,'Holy Cross','Yukon Koyukuk',62.192584,-159.825092),
(83,2,'Homer','Kenai Peninsula',59.665495,-151.462644),
(84,2,'Hoonah','Skagway Hoonah Angoon',58.032237,-135.558435),
(85,2,'Hooper Bay','Wade Hampton',61.537157,-165.891045),
(86,2,'Hope','Kenai Peninsula',60.92,-149.641667),
(87,2,'Houston','Matanuska Susitna',61.633611,-149.841111),
(88,2,'Hughes','Yukon Koyukuk',66.038246,-154.26443),
(89,2,'Huslia','Yukon Koyukuk',65.689918,-156.291976),
(90,2,'Hydaburg','Prince Wales Ketchikan',55.137406,-132.633175),
(91,2,'Hyder','Prince Wales Ketchikan',55.925867,-130.124915),
(92,2,'Iliamna','Lake And Peninsula',59.564836,-155.462556),
(93,2,'Indian','Anchorage',60.9804,-149.4917),
(94,2,'Juneau','Juneau',58.362767,-134.529429),
(95,2,'Kake','Wrangell Petersburg',56.975833,-133.947222),
(96,2,'Kaktovik','North Slope',70.042889,-143.631329),
(97,2,'Kalskag','Bethel',61.541006,-160.3261),
(98,2,'Kaltag','Yukon Koyukuk',64.330452,-158.724251),
(99,2,'Karluk','Kodiak Island',57.57,-154.458333),
(100,2,'Kasigluk','Bethel',60.895556,-162.520556),
(101,2,'Kasilof','Kenai Peninsula',60.316365,-151.28958),
(102,2,'Kenai','Kenai Peninsula',60.614467,-151.254556),
(103,2,'Ketchikan','Ketchikan Gateway',55.372028,-131.683175),
(104,2,'Kiana','Northwest Arctic',67.18026,-158.152204),
(105,2,'King Cove','Aleutians East',55.062848,-162.305561),
(106,2,'King Salmon','Bristol Bay',58.724264,-156.641603),
(107,2,'Kipnuk','Bethel',59.923204,-164.101013),
(108,2,'Kivalina','Northwest Arctic',67.665859,-163.733617),
(109,2,'Klawock'
