-- Create direction lookup table
DROP TABLE IF EXISTS direction_lookup;
CREATE TABLE direction_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(3));
INSERT INTO direction_lookup (name, abbrev) VALUES('WEST', 'W');
INSERT INTO direction_lookup (name, abbrev) VALUES('W', 'W');
INSERT INTO direction_lookup (name, abbrev) VALUES('SW', 'SW');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH-WEST', 'SW');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTHWEST', 'SW');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH-EAST', 'SE');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTHEAST', 'SE');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH_WEST', 'SW');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH_EAST', 'SE');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH', 'S');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH WEST', 'SW');
INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH EAST', 'SE');
INSERT INTO direction_lookup (name, abbrev) VALUES('SE', 'SE');
INSERT INTO direction_lookup (name, abbrev) VALUES('S', 'S');
INSERT INTO direction_lookup (name, abbrev) VALUES('NW', 'NW');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH-WEST', 'NW');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTHWEST', 'NW');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH-EAST', 'NE');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTHEAST', 'NE');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH_WEST', 'NW');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH_EAST', 'NE');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH', 'N');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH WEST', 'NW');
INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH EAST', 'NE');
INSERT INTO direction_lookup (name, abbrev) VALUES('NE', 'NE');
INSERT INTO direction_lookup (name, abbrev) VALUES('N', 'N');
INSERT INTO direction_lookup (name, abbrev) VALUES('EAST', 'E');
INSERT INTO direction_lookup (name, abbrev) VALUES('E', 'E');
CREATE INDEX direction_lookup_abbrev_idx ON direction_lookup (abbrev);



-- Create secondary unit lookup table
DROP TABLE IF EXISTS secondary_unit_lookup;
CREATE TABLE secondary_unit_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(5));
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('APARTMENT', 'APT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('APT', 'APT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BASEMENT', 'BSMT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BSMT', 'BSMT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BUILDING', 'BLDG');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BLDG', 'BLDG');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('DEPARTMENT', 'DEPT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('DEPT', 'DEPT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FLOOR', 'FL');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FL', 'FL');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FRONT', 'FRNT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FRNT', 'FRNT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('HANGAR', 'HNGR');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('HNGR', 'HNGR');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOBBY', 'LBBY');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LBBY', 'LBBY');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOT', 'LOT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOWER', 'LOWR');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOWR', 'LOWR');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('OFFICE', 'OFC');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('OFC', 'OFC');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PENTHOUSE', 'PH');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PH', 'PH');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PIER', 'PIER');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('REAR', 'REAR');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('ROOM', 'RM');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('RM', 'RM');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SIDE', 'SIDE');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SLIP', 'SLIP');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SPACE', 'SPC');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SPC', 'SPC');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('STOP', 'STOP');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SUITE', 'STE');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('STE', 'STE');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('TRAILER', 'TRLR');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('TRLR', 'TRLR');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UNIT', 'UNIT');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UPPER', 'UPPR');
INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UPPR', 'UPPR');
CREATE INDEX secondary_unit_lookup_abbrev_idx ON secondary_unit_lookup (abbrev);



-- Create state lookup table
DROP TABLE IF EXISTS state_lookup;
CREATE TABLE state_lookup (st_code INTEGER PRIMARY KEY, name VARCHAR(40) UNIQUE, abbrev VARCHAR(3) UNIQUE);
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Alabama', 'AL', '01');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Alaska', 'AK', '02');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('American Samoa', 'AS', -1);
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Arizona', 'AZ', '04');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Arkansas', 'AR', '05');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('California', 'CA', '06');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Colorado', 'CO', '08');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Connecticut', 'CT', '09');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Delaware', 'DE', '10');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('District of Columbia', 'DC', '11');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Federated States of Micronesia', 'FM', -2);
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Florida', 'FL', '12');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Georgia', 'GA', '13');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Guam', 'GU', -7);
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Hawaii', 'HI', '15');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Idaho', 'ID', '16');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Illinois', 'IL', '17');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Indiana', 'IN', '18');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Iowa', 'IA', '19');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Kansas', 'KS', '20');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Kentucky', 'KY', '21');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Louisiana', 'LA', '22');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Maine', 'ME', '23');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Marshall Islands', 'MH', -3);
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Maryland', 'MD', '24');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Massachusetts', 'MA', '25');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Michigan', 'MI', '26');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Minnesota', 'MN', '27');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Mississippi', 'MS', '28');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Missouri', 'MO', '29');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Montana', 'MT', '30');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Nebraska', 'NE', '31');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Nevada', 'NV', '32');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Hampshire', 'NH', '33');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Jersey', 'NJ', '34');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Mexico', 'NM', '35');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New York', 'NY', '36');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('North Carolina', 'NC', '37');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('North Dakota', 'ND', '38');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Northern Mariana Islands', 'MP', -4);
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Ohio', 'OH', '39');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Oklahoma', 'OK', '40');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Oregon', 'OR', '41');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Palau', 'PW', -5);
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Pennsylvania', 'PA', '42');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Puerto Rico', 'PR', '72');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Rhode Island', 'RI', '44');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('South Carolina', 'SC', '45');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('South Dakota', 'SD', '46');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Tennessee', 'TN', '47');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Texas', 'TX', '48');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Utah', 'UT', '49');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Vermont', 'VT', '50');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Virgin Islands', 'VI', -6);
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Virginia', 'VA', '51');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Washington', 'WA', '53');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('West Virginia', 'WV', '54');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Wisconsin', 'WI', '55');
INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Wyoming', 'WY', '56');


-- Create street type lookup table
DROP TABLE IF EXISTS street_type_lookup;
CREATE TABLE street_type_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(4));
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLEE', 'Aly');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLEY', 'Aly');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLY', 'Aly');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALY', 'Aly');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANEX', 'Anx');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANNEX', 'Anx');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANNX', 'Anx');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANX', 'Anx');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ARC', 'Arc');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ARCADE', 'Arc');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('AV', 'Ave');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVE', 'Ave');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVEN', 'Ave');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVENU', 'Ave');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVENUE', 'Ave');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVN', 'Ave');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVNUE', 'Ave');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BAYOO', 'Byu');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BAYOU', 'Byu');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BCH', 'Bch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BEACH', 'Bch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BEND', 'Bnd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BND', 'Bnd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLF', 'Blf');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUF', 'Blf');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUFF', 'Blf');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUFFS', 'Blfs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOT', 'Btm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOTTM', 'Btm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOTTOM', 'Btm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BTM', 'Btm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLVD', 'Blvd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOUL', 'Blvd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOULEVARD', 'Blvd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOULV', 'Blvd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BR', 'Br');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRANCH', 'Br');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRNCH', 'Br');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRDGE', 'Brg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRG', 'Brg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRIDGE', 'Brg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRK', 'Brk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BROOK', 'Brk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BROOKS', 'Brks');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BURG', 'Bg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BURGS', 'Bgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYP', 'Byp');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPA', 'Byp');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPAS', 'Byp');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPASS', 'ByP');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPS', 'Byp');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAMP', 'Cp');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CMP', 'Cp');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CP', 'Cp');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CANYN', 'Cyn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CANYON', 'Cyn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNYN', 'Cyn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CYN', 'Cyn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAPE', 'Cpe');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CPE', 'Cpe');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAUSEWAY', 'Cswy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAUSWAY', 'Cswy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CSWY', 'Cswy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CEN', 'Ctr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENT', 'Ctr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTER', 'Ctr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTR', 'Ctr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTRE', 'Ctr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNTER', 'Ctr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNTR', 'Ctr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTR', 'Ctr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTERS', 'Ctrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIR', 'Cir');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRC', 'Cir');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCL', 'Cir');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCLE', 'Cir');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRCL', 'Cir');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRCLE', 'Cir');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCLES', 'Cirs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLF', 'Clf');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLIFF', 'Clf');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLFS', 'Clfs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLIFFS', 'Clfs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLB', 'Clb');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLUB', 'Clb');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('COMMON', 'Cmn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('COR', 'Cor');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORNER', 'Cor');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORNERS', 'Cors');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORS', 'Cors');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURSE', 'Crse');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSE', 'Crse');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURT', 'Ct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRT', 'Ct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CT', 'Ct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURTS', 'Cts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('COVE', 'Cv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CV', 'Cv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('COVES', 'Cvs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CK', 'Crk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CR', 'Crk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CREEK', 'Crk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRK', 'Crk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRECENT', 'Cres');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRES', 'Cres');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRESCENT', 'Cres');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRESENT', 'Cres');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSCNT', 'Cres');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSENT', 'Cres');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSNT', 'Cres');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CREST', 'Crst');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CROSSING', 'Xing');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSSING', 'Xing');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSSNG', 'Xing');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('XING', 'Xing');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CROSSROAD', 'Xrd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CURVE', 'Curv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DALE', 'Dl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DL', 'Dl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DAM', 'Dm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DM', 'Dm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DIV', 'Dv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DIVIDE', 'Dv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DV', 'Dv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DVD', 'Dv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DR', 'Dr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIV', 'Dr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIVE', 'Dr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRV', 'Dr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIVES', 'Drs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EST', 'Est');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTATE', 'Est');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTATES', 'Ests');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTS', 'Ests');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXP', 'Expy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPR', 'Expy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPRESS', 'Expy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPRESSWAY', 'Expy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPW', 'Expy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPY', 'Expy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXT', 'Ext');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTENSION', 'Ext');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTN', 'Ext');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTNSN', 'Ext');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTENSIONS', 'Exts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTS', 'Exts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FALL', 'Fall');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FALLS', 'Fls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLS', 'Fls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FERRY', 'Fry');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRRY', 'Fry');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRY', 'Fry');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FIELD', 'Fld');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLD', 'Fld');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FIELDS', 'Flds');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLDS', 'Flds');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLAT', 'Flt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLT', 'Flt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLATS', 'Flts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLTS', 'Flts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORD', 'Frd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRD', 'Frd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORDS', 'Frds');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FOREST', 'Frst');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORESTS', 'Frst');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRST', 'Frst');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORG', 'Frg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORGE', 'Frg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRG', 'Frg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORGES', 'Frgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORK', 'Frk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRK', 'Frk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORKS', 'Frks');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRKS', 'Frks');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORT', 'Ft');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRT', 'Ft');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FT', 'Ft');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FREEWAY', 'Fwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FREEWY', 'Fwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRWAY', 'Fwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRWY', 'Fwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FWY', 'Fwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDEN', 'Gdn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDN', 'Gdn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GDN', 'Gdn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDEN', 'Gdn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDN', 'Gdn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDENS', 'Gdns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GDNS', 'Gdns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDNS', 'Gdns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATEWAY', 'Gtwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATEWY', 'Gtwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATWAY', 'Gtwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GTWAY', 'Gtwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GTWY', 'Gtwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLEN', 'Gln');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLN', 'Gln');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLENS', 'Glns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GREEN', 'Grn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRN', 'Grn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GREENS', 'Grns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROV', 'Grv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROVE', 'Grv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRV', 'Grv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROVES', 'Grvs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARB', 'Hbr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBOR', 'Hbr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBR', 'Hbr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HBR', 'Hbr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HRBOR', 'Hbr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBORS', 'Hbrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HAVEN', 'Hvn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HAVN', 'Hvn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HVN', 'Hvn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HEIGHT', 'Hts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HEIGHTS', 'Hts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HGTS', 'Hts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HT', 'Hts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HTS', 'Hts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIGHWAY', 'Hwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIGHWY', 'Hwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIWAY', 'Hwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HIWY', 'Hwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HWAY', 'Hwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HWY', 'Hwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HILL', 'Hl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HL', 'Hl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HILLS', 'Hls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HLS', 'Hls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HLLW', 'Holw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLLOW', 'Holw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLLOWS', 'Holw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLW', 'Holw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLWS', 'Holw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('INLET', 'Inlt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('INLT', 'Inlt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('IS', 'Is');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLAND', 'Is');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLND', 'Is');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLANDS', 'Iss');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLNDS', 'Iss');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISS', 'Iss');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLE', 'Isle');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLES', 'Isle');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCT', 'Jct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTION', 'Jct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTN', 'Jct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTION', 'Jct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTN', 'Jct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTON', 'Jct');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTNS', 'Jcts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTS', 'Jcts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTIONS', 'Jcts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KEY', 'Ky');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KY', 'Ky');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KEYS', 'Kys');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KYS', 'Kys');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNL', 'Knl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOL', 'Knl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOLL', 'Knl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNLS', 'Knls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOLLS', 'Knls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAKE', 'Lk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LK', 'Lk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAKES', 'Lks');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LKS', 'Lks');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAND', 'Land');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANDING', 'Lndg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LNDG', 'Lndg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LNDNG', 'Lndg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LA', 'Ln');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANE', 'Ln');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANES', 'Ln');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LN', 'Ln');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LGT', 'Lgt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LIGHT', 'Lgt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LIGHTS', 'Lgts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LF', 'Lf');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOAF', 'Lf');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LCK', 'Lck');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOCK', 'Lck');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LCKS', 'Lcks');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOCKS', 'Lcks');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LDG', 'Ldg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LDGE', 'Ldg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LODG', 'Ldg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LODGE', 'Ldg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOOP', 'Loop');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOOPS', 'Loop');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MALL', 'Mall');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MANOR', 'Mnr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNR', 'Mnr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MANORS', 'Mnrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNRS', 'Mnrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MDW', 'Mdw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEADOW', 'Mdw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MDWS', 'Mdws');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEADOWS', 'Mdws');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEDOWS', 'Mdws');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEWS', 'Mews');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MILL', 'Ml');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ML', 'Ml');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MILLS', 'Mls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MLS', 'Mls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MISSION', 'Msn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MISSN', 'Msn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MSN', 'Msn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MSSN', 'Msn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOTORWAY', 'Mtwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNT', 'Mt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNT', 'Mt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MT', 'Mt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTAIN', 'Mtn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTN', 'Mtn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTAIN', 'Mtn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTIN', 'Mtn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTIN', 'Mtn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTN', 'Mtn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTNS', 'Mtns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTAINS', 'Mtns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('NCK', 'Nck');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('NECK', 'Nck');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCH', 'Orch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCHARD', 'Orch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCHRD', 'Orch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVAL', 'Oval');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVL', 'Oval');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVERPASS', 'Opas');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARK', 'Park');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PK', 'Park');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRK', 'Park');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKS', 'Park');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWAY', 'Pkwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWY', 'Pkwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWAY', 'Pkwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWY', 'Pkwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKY', 'Pkwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWAYS', 'Pkwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWYS', 'Pkwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PASS', 'Pass');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PASSAGE', 'Psge');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PATH', 'Path');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PATHS', 'Path');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PIKE', 'Pike');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PIKES', 'Pike');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PINE', 'Pne');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PINES', 'Pnes');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PNES', 'Pnes');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PL', 'Pl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLACE', 'Pl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAIN', 'Pln');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLN', 'Pln');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAINES', 'Plns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAINS', 'Plns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLNS', 'Plns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAZA', 'Plz');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLZ', 'Plz');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLZA', 'Plz');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('POINT', 'Pt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PT', 'Pt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('POINTS', 'Pts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PTS', 'Pts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PORT', 'Prt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRT', 'Prt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PORTS', 'Prts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRTS', 'Prts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PR', 'Pr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRAIRIE', 'Pr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRARIE', 'Pr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRR', 'Pr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAD', 'Radl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADIAL', 'Radl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADIEL', 'Radl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADL', 'Radl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAMP', 'Ramp');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RANCH', 'Rnch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RANCHES', 'Rnch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RNCH', 'Rnch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RNCHS', 'Rnch');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAPID', 'Rpd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RPD', 'Rpd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAPIDS', 'Rpds');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RPDS', 'Rpds');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('REST', 'Rst');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RST', 'Rst');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDG', 'Rdg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDGE', 'Rdg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIDGE', 'Rdg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDGS', 'Rdgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIDGES', 'Rdgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIV', 'Riv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIVER', 'Riv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIVR', 'Riv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RVR', 'Riv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RD', 'Rd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROAD', 'Rd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDS', 'Rds');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROADS', 'Rds');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROUTE', 'Rte');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROW', 'Row');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RUE', 'Rue');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RUN', 'Run');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHL', 'Shl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOAL', 'Shl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHLS', 'Shls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOALS', 'Shls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOAR', 'Shr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHORE', 'Shr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHR', 'Shr');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOARS', 'Shrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHORES', 'Shrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHRS', 'Shrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SKYWAY', 'Skwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPG', 'Spg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPNG', 'Spg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRING', 'Spg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRNG', 'Spg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPGS', 'Spgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPNGS', 'Spgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRINGS', 'Spgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRNGS', 'Spgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPUR', 'Spur');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPURS', 'Spur');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQ', 'Sq');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQR', 'Sq');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQRE', 'Sq');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQU', 'Sq');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQUARE', 'Sq');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQRS', 'Sqs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQUARES', 'Sqs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STA', 'Sta');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STATION', 'Sta');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STATN', 'Sta');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STN', 'Sta');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRA', 'Stra');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAV', 'Stra');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVE', 'Stra');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVEN', 'Stra');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVENUE', 'Stra');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVN', 'Stra');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRVN', 'Stra');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRVNUE', 'Stra');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREAM', 'Strm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREME', 'Strm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRM', 'Strm');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('ST', 'St');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STR', 'St');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREET', 'St');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRT', 'St');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREETS', 'Sts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SMT', 'Smt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMIT', 'Smt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMITT', 'Smt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMMIT', 'Smt');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TER', 'Ter');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TERR', 'Ter');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TERRACE', 'Ter');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('THROUGHWAY', 'Trwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACE', 'Trce');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACES', 'Trce');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRCE', 'Trce');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACK', 'Trak');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACKS', 'Trak');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAK', 'Trak');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRK', 'Trak');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRKS', 'Trak');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAFFICWAY', 'Trfy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRFY', 'Trfy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TR', 'Trl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAIL', 'Trl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAILS', 'Trl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRL', 'Trl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRLS', 'Trl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNEL', 'Tunl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNL', 'Tunl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNLS', 'Tunl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNEL', 'Tunl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNELS', 'Tunl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNL', 'Tunl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TPK', 'Tpke');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TPKE', 'Tpke');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRNPK', 'Tpke');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRPK', 'Tpke');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TURNPIKE', 'Tpke');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TURNPK', 'Tpke');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNDERPASS', 'Upas');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('UN', 'Un');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNION', 'Un');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNIONS', 'Uns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLEY', 'Vly');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLY', 'Vly');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLLY', 'Vly');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLY', 'Vly');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLEYS', 'Vlys');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLYS', 'Vlys');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VDCT', 'Via');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIA', 'Via');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIADCT', 'Via');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIADUCT', 'Via');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIEW', 'Vw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VW', 'Vw');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIEWS', 'Vws');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VWS', 'Vws');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILL', 'Vlg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAG', 'Vlg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAGE', 'Vlg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLG', 'Vlg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLIAGE', 'Vlg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLG', 'Vlg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAGES', 'Vlgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLGS', 'Vlgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLE', 'Vl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VL', 'Vl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIS', 'Vis');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIST', 'Vis');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VISTA', 'Vis');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VST', 'Vis');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('VSTA', 'Vis');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALK', 'Walk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALKS', 'Walk');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALL', 'Wall');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WAY', 'Way');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WY', 'Way');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WAYS', 'Ways');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WELL', 'Wl');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WELLS', 'Wls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WLS', 'Wls');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYU', 'Byu');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLFS', 'Blfs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRKS', 'Brks');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BG', 'Bg');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('BGS', 'Bgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTRS', 'Ctrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRS', 'Cirs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CMN', 'Cmn');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTS', 'Cts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CVS', 'Cvs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRST', 'Crst');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('XRD', 'Xrd');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('CURV', 'Curv');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRS', 'Drs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRDS', 'Frds');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRGS', 'Frgs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLNS', 'Glns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRNS', 'Grns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRVS', 'Grvs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('HBRS', 'Hbrs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('LGTS', 'Lgts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTWY', 'Mtwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTNS', 'Mtns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('OPAS', 'Opas');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PSGE', 'Psge');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('PNE', 'Pne');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('RTE', 'Rte');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SKWY', 'Skwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQS', 'Sqs');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('STS', 'Sts');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRWY', 'Trwy');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('UPAS', 'Upas');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNS', 'Uns');
INSERT INTO street_type_lookup (name, abbrev) VALUES ('WL', 'Wl');
CREATE INDEX street_type_lookup_abbrev_idx ON street_type_lookup (abbrev);

-- Create place and countysub lookup tables
DROP TABLE IF EXISTS place_lookup;
CREATE TABLE place_lookup (
    st_code INTEGER,
    state   VARCHAR(2),
    pl_code INTEGER,
    name    VARCHAR(90),
    PRIMARY KEY (st_code,pl_code)
);

INSERT INTO place_lookup
  SELECT
    pl.state::integer   as st_code,
    sl.abbrev           as state,
    pl.placefp::integer as pl_code,
    pl.name             as name
  FROM
    pl99_d00 pl
    JOIN state_lookup sl ON (pl.state = lpad(sl.st_code,2,'0'))
  GROUP BY pl.state, sl.abbrev, pl.placefp, pl.name;

CREATE INDEX place_lookup_name_idx ON place_lookup (soundex(name));
CREATE INDEX place_lookup_state_idx ON place_lookup (state);

DROP TABLE IF EXISTS county_lookup;
CREATE TABLE county_lookup (
    st_code INTEGER,
    state   VARCHAR(2),
    co_code INTEGER,
    name    VARCHAR(90),
    PRIMARY KEY (st_code, co_code)
);

INSERT INTO county_lookup
  SELECT
    co.state::integer    as st_code,
    sl.abbrev            as state,
    co.county::integer   as co_code,
    co.name              as name
  FROM
    co99_d00 co
    JOIN state_lookup sl ON (co.state = lpad(sl.st_code,2,'0'))
  GROUP BY co.state, sl.abbrev, co.county, co.name;

CREATE INDEX county_lookup_name_idx ON county_lookup (soundex(name));
CREATE INDEX county_lookup_state_idx ON county_lookup (state);

DROP TABLE IF EXISTS countysub_lookup;
CREATE TABLE countysub_lookup (
    st_code INTEGER,
    state   VARCHAR(2),
    co_code INTEGER,
    county  VARCHAR(90),
    cs_code INTEGER,
    name    VARCHAR(90),
    PRIMARY KEY (st_code, co_code, cs_code)
);

INSERT INTO countysub_lookup
  SELECT
    cs.state::integer    as st_code,
    sl.abbrev            as state,
    cs.county::integer   as co_code,
    cl.name              as county,
    cs.cousubfp::integer as cs_code,
    cs.name              as name
  FROM
    cs99_d00 cs
    JOIN state_lookup sl ON (cs.state = lpad(sl.st_code,2,'0'))
    JOIN county_lookup cl ON (cs.state = lpad(cl.st_code,2,'0') AND cs.county = cl.co_code)
  GROUP BY cs.state, sl.abbrev, cs.county, cl.name, cs.cousubfp, cs.name;

CREATE INDEX countysub_lookup_name_idx ON countysub_lookup (soundex(name));
CREATE INDEX countysub_lookup_state_idx ON countysub_lookup (state);

DROP TABLE IF EXISTS zip_lookup_all;
CREATE TABLE zip_lookup_all (
    zip     INTEGER,
    st_code INTEGER,
    state   VARCHAR(2),
    co_code INTEGER,
    county  VARCHAR(90),
    cs_code INTEGER,
    cousub  VARCHAR(90),
    pl_code INTEGER,
    place   VARCHAR(90),
    cnt     INTEGER
);

SET work_mem = '2GB';

INSERT INTO zip_lookup_all
  SELECT *,count(*) as cnt FROM
  (SELECT
    zipl                 as zip,
    rl.statel            as st_code,
    sl.abbrev            as state,
    rl.countyl           as co_code,
    cl.name              as county,
    rl.cousubl           as cs_code,
    cs.name              as countysub,
    rl.placel            as pl_code,
    pl.name              as place
  FROM
    roads_local rl
    JOIN state_lookup sl ON (rl.statel = lpad(sl.st_code,2,'0'))
    LEFT JOIN county_lookup cl ON (rl.statel = lpad(cl.st_code,2,'0') AND rl.countyl = cl.co_code)
    LEFT JOIN countysub_lookup cs ON (rl.statel = lpad(cs.st_code,2,'0') AND rl.countyl = cs.co_code AND rl.cousubl = cs.cs_code)
    LEFT JOIN place_lookup pl ON (rl.statel = lpad(pl.st_code,2,'0') AND rl.placel = pl.pl_code)
  WHERE zipl IS NOT NULL
  UNION ALL
  SELECT
    zipr                 as zip,
    rl.stater            as st_code,
    sl.abbrev            as state,
    rl.countyr           as co_code,
    cl.name              as county,
    rl.cousubr           as cs_code,
    cs.name              as countysub,
    rl.placer            as pl_code,
    pl.name              as place
  FROM
    roads_local rl
    JOIN state_lookup sl ON (rl.stater = lpad(sl.st_code,2,'0'))
    LEFT JOIN county_lookup cl ON (rl.stater = lpad(cl.st_code,2,'0') AND rl.countyr = cl.co_code)
    LEFT JOIN countysub_lookup cs ON (rl.stater = lpad(cs.st_code,2,'0') AND rl.countyr = cs.co_code AND rl.cousubr = cs.cs_code)
    LEFT JOIN place_lookup pl ON (rl.stater = lpad(pl.st_code,2,'0') AND rl.placer = pl.pl_code)
  WHERE zipr IS NOT NULL
  ) as subquery
  GROUP BY zip, st_code, state, co_code, county, cs_code, countysub, pl_code, place;

DROP TABLE IF EXISTS zip_lookup_base;
CREATE TABLE zip_lookup_base (
    zip     INTEGER,
    state   VARCHAR(40),
    county  VARCHAR(90),
    city    VARCHAR(90),
    PRIMARY KEY (zip)
);

-- INSERT INTO zip_lookup_base
-- Populate through magic
-- If anyone knows of a good, public, free, place to pull this information from, that'd be awesome to have...

DROP TABLE IF EXISTS zip_lookup;
CREATE TABLE zip_lookup (
    zip     INTEGER,
    st_code INTEGER,
    state   VARCHAR(2),
    co_code INTEGER,
    county  VARCHAR(90),
    cs_code INTEGER,
    cousub  VARCHAR(90),
    pl_code INTEGER,
    place   VARCHAR(90),
    cnt     INTEGER,
    PRIMARY KEY (zip)
);

INSERT INTO zip_lookup
  SELECT
    DISTINCT ON (zip)
    zip,
    st_code,
    state,
    co_code,
    county,
    cs_code,
    cousub,
    pl_code,
    place,
    cnt
  FROM zip_lookup_all
  ORDER BY zip,cnt desc;