Zipcode-table-update

From VPOIDS Documentation

Updating the zipcode table

CREATE TABLE zipcode_import
(City varchar(28),
State char(2),
ZIP char(5),
Area char(3),
FIPS varchar(5),
County varchar(25),
Preferred char(1),
Time varchar(5),
DST char(1),
Latitude varchar(8),
Longitude varchar(9),
MSA varchar(4),
PMSA varchar(4),
Abbreviation varchar(13),
MA varchar(3),
TYPE char(1),
CBSA varchar(5),
Division varchar(5),
Population varchar(10))
load data local infile 'C:/web/AFIDS/Zipcode data/2013 Update/z5max.txt' into table zipcode_import fields terminated by ','
enclosed by '"'
LINES TERMINATED BY '\r\n'
ignore 2 LINES
(City, State, ZIP, Area, FIPS, County, Preferred, Time, DST, Latitude, Longitude, MSA, PMSA, Abbreviation, MA, `Type`, CBSA, Division, Population);
ALTER TABLE zipcode
add msa int, add ma, add pmsa int, add cbsa int, add division int, add population int;
/* convert over to zipcode */
INSERT INTO zipcode
SELECT distinct null as id,
city,
state,
zip as zipcode,
area as area_code,
fips as fips_code,
county as county_name,
preferred as preferred,
time as time_zone,
dst as dst_flag,
case when type = '' then null else type end as zip_type,
null as gmt_offset,
null as dst_offset,
null as afa_org_id,
latitude,
longitude,
null as wing_id,
ma,
msa,
pmsa,
cbsa,
division,
population
FROM zipcode_import;
/* update the gmt and dst offset */
update zipcode
set gmt_offset = (case when time_zone = 'EST' then	-5
when time_zone = 'EST+1' then	-4
when time_zone = 'GMT+1' then	1
when time_zone = 'CST' then	-6
when time_zone = 'MST' then	-7
when time_zone = 'PST' then	-8
when time_zone = 'PST-1' then	-9
when time_zone = 'PST-2' then	-10
when time_zone = 'PST-3' then	-11
when time_zone = 'PST-4' then	-12
when time_zone = 'PST-5' then	-13
when time_zone = 'PST-6' then	-14
when time_zone = 'PST-7' then	-15
end);
update zipcode
set dst_offset = (case when dst_flag = 'Y' then	1
when dst_flag = 'N' then 0
end);

Updating the zipcoe and county tables

/* afa org mapping */
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'AK';
	UPDATE zipcode Set afa_org_id = 103 WHERE state = 'AL';
	UPDATE zipcode Set afa_org_id = 105 WHERE state = 'AR';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'AZ';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'CA';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'CO';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'CT';
	UPDATE zipcode Set afa_org_id = 104 WHERE state = 'DC';
	UPDATE zipcode Set afa_org_id = 104 WHERE state = 'DE';
	UPDATE zipcode Set afa_org_id = 103 WHERE state = 'FL';
	UPDATE zipcode Set afa_org_id = 103 WHERE state = 'GA';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'HI';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'IA';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'ID';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'IL';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'IN';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'KS';
	UPDATE zipcode Set afa_org_id = 104 WHERE state = 'KY';
	UPDATE zipcode Set afa_org_id = 105 WHERE state = 'LA';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'MA';
	UPDATE zipcode Set afa_org_id = 104 WHERE state = 'MD';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'ME';	
	UPDATE zipcode Set afa_org_id = 104 WHERE state = 'MI';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'MN';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'MO';
	UPDATE zipcode Set afa_org_id = 110 WHERE state = 'MS';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'MT';
	UPDATE zipcode Set afa_org_id = 109 WHERE state = 'NC';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'ND';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'NE';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'NH';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'NJ';
	UPDATE zipcode Set afa_org_id = 105 WHERE state = 'NM';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'NV';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'NY';
	UPDATE zipcode Set afa_org_id = 104 WHERE state = 'OH';
	UPDATE zipcode Set afa_org_id = 105 WHERE state = 'OK';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'OR';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'PA';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'RI';
	UPDATE zipcode Set afa_org_id = 103 WHERE state = 'SC';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'SD';
	UPDATE zipcode Set afa_org_id = 109 WHERE state = 'TN';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'TX';
	UPDATE zipcode Set afa_org_id = 104 WHERE state = 'VA';
	UPDATE zipcode Set afa_org_id = 106 WHERE state = 'VT';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'WA';
	UPDATE zipcode Set afa_org_id = 101 WHERE state = 'WI';
	UPDATE zipcode Set afa_org_id = 104 WHERE state = 'WV';
	UPDATE zipcode Set afa_org_id = 102 WHERE state = 'WY';
	
/* update the wing id */
	UPDATE zipcode Set wing_id = 13 WHERE state = 'AK';
	UPDATE zipcode Set wing_id = 1 WHERE state = 'AZ';
	UPDATE zipcode Set wing_id = 3 WHERE state = 'CA';
	UPDATE zipcode Set wing_id = 4 WHERE state = 'CO';
	UPDATE zipcode Set wing_id = 14 WHERE state = 'HI';	
	UPDATE zipcode Set wing_id = 5 WHERE state = 'ID';
	UPDATE zipcode Set wing_id = 6 WHERE state = 'MT';
	UPDATE zipcode Set wing_id = 7 WHERE state = 'NM';
	UPDATE zipcode Set wing_id = 8 WHERE state = 'NV';
	UPDATE zipcode Set wing_id = 9 WHERE state = 'OR';
	UPDATE zipcode Set wing_id = 22 WHERE state = 'TX';
	UPDATE zipcode Set wing_id = 10 WHERE state = 'UT';
	UPDATE zipcode Set wing_id = 11 WHERE state = 'WA';
	UPDATE zipcode Set wing_id = 12 WHERE state = 'WY';
truncate table county;

insert into county
select distinct fips_code, county_name
from zipcode
where fips_code != 0;