1--SET search_path TO tiger, public; 2SELECT tiger.SetSearchPathForInstall('tiger'); 3-- Create direction lookup table 4DROP TABLE IF EXISTS tiger.direction_lookup; 5CREATE TABLE direction_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(3)); 6INSERT INTO direction_lookup (name, abbrev) VALUES('WEST', 'W'); 7INSERT INTO direction_lookup (name, abbrev) VALUES('W', 'W'); 8INSERT INTO direction_lookup (name, abbrev) VALUES('SW', 'SW'); 9INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH-WEST', 'SW'); 10INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTHWEST', 'SW'); 11INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH-EAST', 'SE'); 12INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTHEAST', 'SE'); 13INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH_WEST', 'SW'); 14INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH_EAST', 'SE'); 15INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH', 'S'); 16INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH WEST', 'SW'); 17INSERT INTO direction_lookup (name, abbrev) VALUES('SOUTH EAST', 'SE'); 18INSERT INTO direction_lookup (name, abbrev) VALUES('SE', 'SE'); 19INSERT INTO direction_lookup (name, abbrev) VALUES('S', 'S'); 20INSERT INTO direction_lookup (name, abbrev) VALUES('NW', 'NW'); 21INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH-WEST', 'NW'); 22INSERT INTO direction_lookup (name, abbrev) VALUES('NORTHWEST', 'NW'); 23INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH-EAST', 'NE'); 24INSERT INTO direction_lookup (name, abbrev) VALUES('NORTHEAST', 'NE'); 25INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH_WEST', 'NW'); 26INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH_EAST', 'NE'); 27INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH', 'N'); 28INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH WEST', 'NW'); 29INSERT INTO direction_lookup (name, abbrev) VALUES('NORTH EAST', 'NE'); 30INSERT INTO direction_lookup (name, abbrev) VALUES('NE', 'NE'); 31INSERT INTO direction_lookup (name, abbrev) VALUES('N', 'N'); 32INSERT INTO direction_lookup (name, abbrev) VALUES('EAST', 'E'); 33INSERT INTO direction_lookup (name, abbrev) VALUES('E', 'E'); 34CREATE INDEX direction_lookup_abbrev_idx ON direction_lookup (abbrev); 35 36-- Create secondary unit lookup table 37DROP TABLE IF EXISTS tiger.secondary_unit_lookup; 38CREATE TABLE secondary_unit_lookup (name VARCHAR(20) PRIMARY KEY, abbrev VARCHAR(5)); 39INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('APARTMENT', 'APT'); 40INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('APT', 'APT'); 41INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BASEMENT', 'BSMT'); 42INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BSMT', 'BSMT'); 43INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BUILDING', 'BLDG'); 44INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('BLDG', 'BLDG'); 45INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('DEPARTMENT', 'DEPT'); 46INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('DEPT', 'DEPT'); 47INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FLOOR', 'FL'); 48INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FL', 'FL'); 49INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FRONT', 'FRNT'); 50INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('FRNT', 'FRNT'); 51INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('HANGAR', 'HNGR'); 52INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('HNGR', 'HNGR'); 53INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOBBY', 'LBBY'); 54INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LBBY', 'LBBY'); 55INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOT', 'LOT'); 56INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOWER', 'LOWR'); 57INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('LOWR', 'LOWR'); 58INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('OFFICE', 'OFC'); 59INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('OFC', 'OFC'); 60INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PENTHOUSE', 'PH'); 61INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PH', 'PH'); 62INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('PIER', 'PIER'); 63INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('REAR', 'REAR'); 64INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('ROOM', 'RM'); 65INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('RM', 'RM'); 66INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SIDE', 'SIDE'); 67INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SLIP', 'SLIP'); 68INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SPACE', 'SPC'); 69INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SPC', 'SPC'); 70INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('STOP', 'STOP'); 71INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('SUITE', 'STE'); 72INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('STE', 'STE'); 73INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('TRAILER', 'TRLR'); 74INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('TRLR', 'TRLR'); 75INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UNIT', 'UNIT'); 76INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UPPER', 'UPPR'); 77INSERT INTO secondary_unit_lookup (name, abbrev) VALUES ('UPPR', 'UPPR'); 78CREATE INDEX secondary_unit_lookup_abbrev_idx ON secondary_unit_lookup (abbrev); 79 80-- Create state lookup table 81DROP TABLE IF EXISTS tiger.state_lookup; 82CREATE TABLE state_lookup (st_code INTEGER PRIMARY KEY, name VARCHAR(40) UNIQUE, abbrev VARCHAR(3) UNIQUE, statefp char(2) UNIQUE); 83INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Alabama', 'AL', '01'); 84INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Alaska', 'AK', '02'); 85INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('American Samoa', 'AS', '60'); 86INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Arizona', 'AZ', '04'); 87INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Arkansas', 'AR', '05'); 88INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('California', 'CA', '06'); 89INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Colorado', 'CO', '08'); 90INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Connecticut', 'CT', '09'); 91INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Delaware', 'DE', '10'); 92INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('District of Columbia', 'DC', '11'); 93INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Federated States of Micronesia', 'FM', '64'); 94INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Florida', 'FL', '12'); 95INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Georgia', 'GA', '13'); 96INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Guam', 'GU', '66'); 97INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Hawaii', 'HI', '15'); 98INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Idaho', 'ID', '16'); 99INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Illinois', 'IL', '17'); 100INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Indiana', 'IN', '18'); 101INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Iowa', 'IA', '19'); 102INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Kansas', 'KS', '20'); 103INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Kentucky', 'KY', '21'); 104INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Louisiana', 'LA', '22'); 105INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Maine', 'ME', '23'); 106INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Marshall Islands', 'MH', '68'); 107INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Maryland', 'MD', '24'); 108INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Massachusetts', 'MA', '25'); 109INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Michigan', 'MI', '26'); 110INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Minnesota', 'MN', '27'); 111INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Mississippi', 'MS', '28'); 112INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Missouri', 'MO', '29'); 113INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Montana', 'MT', '30'); 114INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Nebraska', 'NE', '31'); 115INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Nevada', 'NV', '32'); 116INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Hampshire', 'NH', '33'); 117INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Jersey', 'NJ', '34'); 118INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New Mexico', 'NM', '35'); 119INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('New York', 'NY', '36'); 120INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('North Carolina', 'NC', '37'); 121INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('North Dakota', 'ND', '38'); 122INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Northern Mariana Islands', 'MP', '69'); 123INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Ohio', 'OH', '39'); 124INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Oklahoma', 'OK', '40'); 125INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Oregon', 'OR', '41'); 126INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Palau', 'PW', '70'); 127INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Pennsylvania', 'PA', '42'); 128INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Puerto Rico', 'PR', '72'); 129INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Rhode Island', 'RI', '44'); 130INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('South Carolina', 'SC', '45'); 131INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('South Dakota', 'SD', '46'); 132INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Tennessee', 'TN', '47'); 133INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Texas', 'TX', '48'); 134INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Utah', 'UT', '49'); 135INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Vermont', 'VT', '50'); 136INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Virgin Islands', 'VI', '78'); 137INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Virginia', 'VA', '51'); 138INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Washington', 'WA', '53'); 139INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('West Virginia', 'WV', '54'); 140INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Wisconsin', 'WI', '55'); 141INSERT INTO state_lookup (name, abbrev, st_code) VALUES ('Wyoming', 'WY', '56'); 142-- NOTE: fix later -- this is wrong for those - state code ones 143UPDATE state_lookup SET statefp = lpad(st_code::text,2,'0'); 144 145-- Create street type lookup table 146DROP TABLE IF EXISTS tiger.street_type_lookup; 147CREATE TABLE street_type_lookup (name VARCHAR(50) PRIMARY KEY, abbrev VARCHAR(50), is_hw boolean NOT NULL DEFAULT false); 148INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLEE', 'Aly'); 149INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLEY', 'Aly'); 150INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALLY', 'Aly'); 151INSERT INTO street_type_lookup (name, abbrev) VALUES ('ALY', 'Aly'); 152INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANEX', 'Anx'); 153INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANNEX', 'Anx'); 154INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANNX', 'Anx'); 155INSERT INTO street_type_lookup (name, abbrev) VALUES ('ANX', 'Anx'); 156INSERT INTO street_type_lookup (name, abbrev) VALUES ('ARC', 'Arc'); 157INSERT INTO street_type_lookup (name, abbrev) VALUES ('ARCADE', 'Arc'); 158INSERT INTO street_type_lookup (name, abbrev) VALUES ('AV', 'Ave'); 159INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVE', 'Ave'); 160INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVEN', 'Ave'); 161INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVENU', 'Ave'); 162INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVENUE', 'Ave'); 163INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVN', 'Ave'); 164INSERT INTO street_type_lookup (name, abbrev) VALUES ('AVNUE', 'Ave'); 165INSERT INTO street_type_lookup (name, abbrev) VALUES ('BAYOO', 'Byu'); 166INSERT INTO street_type_lookup (name, abbrev) VALUES ('BAYOU', 'Byu'); 167INSERT INTO street_type_lookup (name, abbrev) VALUES ('BCH', 'Bch'); 168INSERT INTO street_type_lookup (name, abbrev) VALUES ('BEACH', 'Bch'); 169INSERT INTO street_type_lookup (name, abbrev) VALUES ('BEND', 'Bnd'); 170INSERT INTO street_type_lookup (name, abbrev) VALUES ('BND', 'Bnd'); 171INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLF', 'Blf'); 172INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUF', 'Blf'); 173INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUFF', 'Blf'); 174INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLUFFS', 'Blfs'); 175INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOT', 'Btm'); 176INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOTTM', 'Btm'); 177INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOTTOM', 'Btm'); 178INSERT INTO street_type_lookup (name, abbrev) VALUES ('BTM', 'Btm'); 179INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLVD', 'Blvd'); 180INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOUL', 'Blvd'); 181INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOULEVARD', 'Blvd'); 182INSERT INTO street_type_lookup (name, abbrev) VALUES ('BOULV', 'Blvd'); 183INSERT INTO street_type_lookup (name, abbrev) VALUES ('BR', 'Br'); 184INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRANCH', 'Br'); 185INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRNCH', 'Br'); 186INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRDGE', 'Brg'); 187INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRG', 'Brg'); 188INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRIDGE', 'Brg'); 189INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRK', 'Brk'); 190INSERT INTO street_type_lookup (name, abbrev) VALUES ('BROOK', 'Brk'); 191INSERT INTO street_type_lookup (name, abbrev) VALUES ('BROOKS', 'Brks'); 192INSERT INTO street_type_lookup (name, abbrev) VALUES ('BURG', 'Bg'); 193INSERT INTO street_type_lookup (name, abbrev) VALUES ('BURGS', 'Bgs'); 194INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYP', 'Byp'); 195INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPA', 'Byp'); 196INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPAS', 'Byp'); 197INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPASS', 'ByP'); 198INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYPS', 'Byp'); 199INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAMP', 'Cp'); 200INSERT INTO street_type_lookup (name, abbrev) VALUES ('CMP', 'Cp'); 201INSERT INTO street_type_lookup (name, abbrev) VALUES ('CP', 'Cp'); 202INSERT INTO street_type_lookup (name, abbrev) VALUES ('CANYN', 'Cyn'); 203INSERT INTO street_type_lookup (name, abbrev) VALUES ('CANYON', 'Cyn'); 204INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNYN', 'Cyn'); 205INSERT INTO street_type_lookup (name, abbrev) VALUES ('CYN', 'Cyn'); 206INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAPE', 'Cpe'); 207INSERT INTO street_type_lookup (name, abbrev) VALUES ('CPE', 'Cpe'); 208INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAUSEWAY', 'Cswy'); 209INSERT INTO street_type_lookup (name, abbrev) VALUES ('CAUSWAY', 'Cswy'); 210INSERT INTO street_type_lookup (name, abbrev) VALUES ('CSWY', 'Cswy'); 211INSERT INTO street_type_lookup (name, abbrev) VALUES ('CEN', 'Ctr'); 212INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENT', 'Ctr'); 213INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTER', 'Ctr'); 214INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTR', 'Ctr'); 215INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTRE', 'Ctr'); 216INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNTER', 'Ctr'); 217INSERT INTO street_type_lookup (name, abbrev) VALUES ('CNTR', 'Ctr'); 218INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTR', 'Ctr'); 219INSERT INTO street_type_lookup (name, abbrev) VALUES ('CENTERS', 'Ctrs'); 220INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIR', 'Cir'); 221INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRC', 'Cir'); 222INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCL', 'Cir'); 223INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCLE', 'Cir'); 224INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRCL', 'Cir'); 225INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRCLE', 'Cir'); 226INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRCLES', 'Cirs'); 227INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLF', 'Clf'); 228INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLIFF', 'Clf'); 229INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLFS', 'Clfs'); 230INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLIFFS', 'Clfs'); 231INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLB', 'Clb'); 232INSERT INTO street_type_lookup (name, abbrev) VALUES ('CLUB', 'Clb'); 233INSERT INTO street_type_lookup (name, abbrev) VALUES ('COMMON', 'Cmn'); 234INSERT INTO street_type_lookup (name, abbrev) VALUES ('COR', 'Cor'); 235INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORNER', 'Cor'); 236INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORNERS', 'Cors'); 237INSERT INTO street_type_lookup (name, abbrev) VALUES ('CORS', 'Cors'); 238INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURSE', 'Crse'); 239INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSE', 'Crse'); 240INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURT', 'Ct'); 241INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRT', 'Ct'); 242INSERT INTO street_type_lookup (name, abbrev) VALUES ('CT', 'Ct'); 243INSERT INTO street_type_lookup (name, abbrev) VALUES ('COURTS', 'Cts'); 244INSERT INTO street_type_lookup (name, abbrev) VALUES ('COVE', 'Cv'); 245INSERT INTO street_type_lookup (name, abbrev) VALUES ('CV', 'Cv'); 246INSERT INTO street_type_lookup (name, abbrev) VALUES ('COVES', 'Cvs'); 247INSERT INTO street_type_lookup (name, abbrev) VALUES ('CK', 'Crk'); 248INSERT INTO street_type_lookup (name, abbrev) VALUES ('CR', 'Crk'); 249INSERT INTO street_type_lookup (name, abbrev) VALUES ('CREEK', 'Crk'); 250INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRK', 'Crk'); 251INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRECENT', 'Cres'); 252INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRES', 'Cres'); 253INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRESCENT', 'Cres'); 254INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRESENT', 'Cres'); 255INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSCNT', 'Cres'); 256INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSENT', 'Cres'); 257INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSNT', 'Cres'); 258INSERT INTO street_type_lookup (name, abbrev) VALUES ('CREST', 'Crst'); 259INSERT INTO street_type_lookup (name, abbrev) VALUES ('CROSSING', 'Xing'); 260INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSSING', 'Xing'); 261INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRSSNG', 'Xing'); 262INSERT INTO street_type_lookup (name, abbrev) VALUES ('XING', 'Xing'); 263INSERT INTO street_type_lookup (name, abbrev) VALUES ('CROSSROAD', 'Xrd'); 264INSERT INTO street_type_lookup (name, abbrev) VALUES ('CURVE', 'Curv'); 265INSERT INTO street_type_lookup (name, abbrev) VALUES ('DALE', 'Dl'); 266INSERT INTO street_type_lookup (name, abbrev) VALUES ('DL', 'Dl'); 267INSERT INTO street_type_lookup (name, abbrev) VALUES ('DAM', 'Dm'); 268INSERT INTO street_type_lookup (name, abbrev) VALUES ('DM', 'Dm'); 269INSERT INTO street_type_lookup (name, abbrev) VALUES ('DIV', 'Dv'); 270INSERT INTO street_type_lookup (name, abbrev) VALUES ('DIVIDE', 'Dv'); 271INSERT INTO street_type_lookup (name, abbrev) VALUES ('DV', 'Dv'); 272INSERT INTO street_type_lookup (name, abbrev) VALUES ('DVD', 'Dv'); 273INSERT INTO street_type_lookup (name, abbrev) VALUES ('DR', 'Dr'); 274INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIV', 'Dr'); 275INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIVE', 'Dr'); 276INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRV', 'Dr'); 277INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRIVES', 'Drs'); 278INSERT INTO street_type_lookup (name, abbrev) VALUES ('EST', 'Est'); 279INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTATE', 'Est'); 280INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTATES', 'Ests'); 281INSERT INTO street_type_lookup (name, abbrev) VALUES ('ESTS', 'Ests'); 282--INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXP', 'Expy'); 283--INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPR', 'Expy'); 284--INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPRESS', 'Expy'); 285--INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPRESSWAY', 'Expy'); 286--INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPW', 'Expy'); 287--INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXPY', 'Expy'); 288INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXT', 'Ext'); 289INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTENSION', 'Ext'); 290INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTN', 'Ext'); 291INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTNSN', 'Ext'); 292INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTENSIONS', 'Exts'); 293INSERT INTO street_type_lookup (name, abbrev) VALUES ('EXTS', 'Exts'); 294INSERT INTO street_type_lookup (name, abbrev) VALUES ('FALL', 'Fall'); 295INSERT INTO street_type_lookup (name, abbrev) VALUES ('FALLS', 'Fls'); 296INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLS', 'Fls'); 297INSERT INTO street_type_lookup (name, abbrev) VALUES ('FERRY', 'Fry'); 298INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRRY', 'Fry'); 299INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRY', 'Fry'); 300INSERT INTO street_type_lookup (name, abbrev) VALUES ('FIELD', 'Fld'); 301INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLD', 'Fld'); 302INSERT INTO street_type_lookup (name, abbrev) VALUES ('FIELDS', 'Flds'); 303INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLDS', 'Flds'); 304INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLAT', 'Flt'); 305INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLT', 'Flt'); 306INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLATS', 'Flts'); 307INSERT INTO street_type_lookup (name, abbrev) VALUES ('FLTS', 'Flts'); 308INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORD', 'Frd'); 309INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRD', 'Frd'); 310INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORDS', 'Frds'); 311--INSERT INTO street_type_lookup (name, abbrev) VALUES ('FOREST', 'Frst'); 312--INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORESTS', 'Frst'); 313--INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRST', 'Frst'); 314INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORG', 'Frg'); 315INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORGE', 'Frg'); 316INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRG', 'Frg'); 317INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORGES', 'Frgs'); 318INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORK', 'Frk'); 319INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRK', 'Frk'); 320INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORKS', 'Frks'); 321INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRKS', 'Frks'); 322INSERT INTO street_type_lookup (name, abbrev) VALUES ('FORT', 'Ft'); 323INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRT', 'Ft'); 324INSERT INTO street_type_lookup (name, abbrev) VALUES ('FT', 'Ft'); 325INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDEN', 'Gdn'); 326INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDN', 'Gdn'); 327INSERT INTO street_type_lookup (name, abbrev) VALUES ('GDN', 'Gdn'); 328INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDEN', 'Gdn'); 329INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDN', 'Gdn'); 330INSERT INTO street_type_lookup (name, abbrev) VALUES ('GARDENS', 'Gdns'); 331INSERT INTO street_type_lookup (name, abbrev) VALUES ('GDNS', 'Gdns'); 332INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRDNS', 'Gdns'); 333INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATEWAY', 'Gtwy'); 334INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATEWY', 'Gtwy'); 335INSERT INTO street_type_lookup (name, abbrev) VALUES ('GATWAY', 'Gtwy'); 336INSERT INTO street_type_lookup (name, abbrev) VALUES ('GTWAY', 'Gtwy'); 337INSERT INTO street_type_lookup (name, abbrev) VALUES ('GTWY', 'Gtwy'); 338INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLEN', 'Gln'); 339INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLN', 'Gln'); 340INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLENS', 'Glns'); 341INSERT INTO street_type_lookup (name, abbrev) VALUES ('GREEN', 'Grn'); 342INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRN', 'Grn'); 343INSERT INTO street_type_lookup (name, abbrev) VALUES ('GREENS', 'Grns'); 344INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROV', 'Grv'); 345INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROVE', 'Grv'); 346INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRV', 'Grv'); 347INSERT INTO street_type_lookup (name, abbrev) VALUES ('GROVES', 'Grvs'); 348INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARB', 'Hbr'); 349INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBOR', 'Hbr'); 350INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBR', 'Hbr'); 351INSERT INTO street_type_lookup (name, abbrev) VALUES ('HBR', 'Hbr'); 352INSERT INTO street_type_lookup (name, abbrev) VALUES ('HRBOR', 'Hbr'); 353INSERT INTO street_type_lookup (name, abbrev) VALUES ('HARBORS', 'Hbrs'); 354INSERT INTO street_type_lookup (name, abbrev) VALUES ('HAVEN', 'Hvn'); 355INSERT INTO street_type_lookup (name, abbrev) VALUES ('HAVN', 'Hvn'); 356INSERT INTO street_type_lookup (name, abbrev) VALUES ('HVN', 'Hvn'); 357INSERT INTO street_type_lookup (name, abbrev) VALUES ('HEIGHT', 'Hts'); 358INSERT INTO street_type_lookup (name, abbrev) VALUES ('HEIGHTS', 'Hts'); 359INSERT INTO street_type_lookup (name, abbrev) VALUES ('HGTS', 'Hts'); 360INSERT INTO street_type_lookup (name, abbrev) VALUES ('HT', 'Hts'); 361INSERT INTO street_type_lookup (name, abbrev) VALUES ('HTS', 'Hts'); 362INSERT INTO street_type_lookup (name, abbrev) VALUES ('HILL', 'Hl'); 363INSERT INTO street_type_lookup (name, abbrev) VALUES ('HL', 'Hl'); 364INSERT INTO street_type_lookup (name, abbrev) VALUES ('HILLS', 'Hls'); 365INSERT INTO street_type_lookup (name, abbrev) VALUES ('HLS', 'Hls'); 366INSERT INTO street_type_lookup (name, abbrev) VALUES ('HLLW', 'Holw'); 367INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLLOW', 'Holw'); 368INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLLOWS', 'Holw'); 369INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLW', 'Holw'); 370INSERT INTO street_type_lookup (name, abbrev) VALUES ('HOLWS', 'Holw'); 371INSERT INTO street_type_lookup (name, abbrev) VALUES ('INLET', 'Inlt'); 372INSERT INTO street_type_lookup (name, abbrev) VALUES ('INLT', 'Inlt'); 373INSERT INTO street_type_lookup (name, abbrev) VALUES ('IS', 'Is'); 374INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLAND', 'Is'); 375INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLND', 'Is'); 376INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLANDS', 'Iss'); 377INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLNDS', 'Iss'); 378INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISS', 'Iss'); 379INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLE', 'Isle'); 380INSERT INTO street_type_lookup (name, abbrev) VALUES ('ISLES', 'Isle'); 381INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCT', 'Jct'); 382INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTION', 'Jct'); 383INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTN', 'Jct'); 384INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTION', 'Jct'); 385INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTN', 'Jct'); 386INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTON', 'Jct'); 387INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTNS', 'Jcts'); 388INSERT INTO street_type_lookup (name, abbrev) VALUES ('JCTS', 'Jcts'); 389INSERT INTO street_type_lookup (name, abbrev) VALUES ('JUNCTIONS', 'Jcts'); 390INSERT INTO street_type_lookup (name, abbrev) VALUES ('KEY', 'Ky'); 391INSERT INTO street_type_lookup (name, abbrev) VALUES ('KY', 'Ky'); 392INSERT INTO street_type_lookup (name, abbrev) VALUES ('KEYS', 'Kys'); 393INSERT INTO street_type_lookup (name, abbrev) VALUES ('KYS', 'Kys'); 394INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNL', 'Knl'); 395INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOL', 'Knl'); 396INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOLL', 'Knl'); 397INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNLS', 'Knls'); 398INSERT INTO street_type_lookup (name, abbrev) VALUES ('KNOLLS', 'Knls'); 399INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAKE', 'Lk'); 400INSERT INTO street_type_lookup (name, abbrev) VALUES ('LK', 'Lk'); 401INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAKES', 'Lks'); 402INSERT INTO street_type_lookup (name, abbrev) VALUES ('LKS', 'Lks'); 403INSERT INTO street_type_lookup (name, abbrev) VALUES ('LAND', 'Land'); 404INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANDING', 'Lndg'); 405INSERT INTO street_type_lookup (name, abbrev) VALUES ('LNDG', 'Lndg'); 406INSERT INTO street_type_lookup (name, abbrev) VALUES ('LNDNG', 'Lndg'); 407INSERT INTO street_type_lookup (name, abbrev) VALUES ('LA', 'Ln'); 408INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANE', 'Ln'); 409INSERT INTO street_type_lookup (name, abbrev) VALUES ('LANES', 'Ln'); 410INSERT INTO street_type_lookup (name, abbrev) VALUES ('LN', 'Ln'); 411INSERT INTO street_type_lookup (name, abbrev) VALUES ('LGT', 'Lgt'); 412INSERT INTO street_type_lookup (name, abbrev) VALUES ('LIGHT', 'Lgt'); 413INSERT INTO street_type_lookup (name, abbrev) VALUES ('LIGHTS', 'Lgts'); 414INSERT INTO street_type_lookup (name, abbrev) VALUES ('LF', 'Lf'); 415INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOAF', 'Lf'); 416INSERT INTO street_type_lookup (name, abbrev) VALUES ('LCK', 'Lck'); 417INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOCK', 'Lck'); 418INSERT INTO street_type_lookup (name, abbrev) VALUES ('LCKS', 'Lcks'); 419INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOCKS', 'Lcks'); 420INSERT INTO street_type_lookup (name, abbrev) VALUES ('LDG', 'Ldg'); 421INSERT INTO street_type_lookup (name, abbrev) VALUES ('LDGE', 'Ldg'); 422INSERT INTO street_type_lookup (name, abbrev) VALUES ('LODG', 'Ldg'); 423INSERT INTO street_type_lookup (name, abbrev) VALUES ('LODGE', 'Ldg'); 424INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOOP', 'Loop'); 425INSERT INTO street_type_lookup (name, abbrev) VALUES ('LOOPS', 'Loop'); 426INSERT INTO street_type_lookup (name, abbrev) VALUES ('MALL', 'Mall'); 427INSERT INTO street_type_lookup (name, abbrev) VALUES ('MANOR', 'Mnr'); 428INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNR', 'Mnr'); 429INSERT INTO street_type_lookup (name, abbrev) VALUES ('MANORS', 'Mnrs'); 430INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNRS', 'Mnrs'); 431INSERT INTO street_type_lookup (name, abbrev) VALUES ('MDW', 'Mdw'); 432INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEADOW', 'Mdw'); 433INSERT INTO street_type_lookup (name, abbrev) VALUES ('MDWS', 'Mdws'); 434INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEADOWS', 'Mdws'); 435INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEDOWS', 'Mdws'); 436INSERT INTO street_type_lookup (name, abbrev) VALUES ('MEWS', 'Mews'); 437INSERT INTO street_type_lookup (name, abbrev) VALUES ('MILL', 'Ml'); 438INSERT INTO street_type_lookup (name, abbrev) VALUES ('ML', 'Ml'); 439INSERT INTO street_type_lookup (name, abbrev) VALUES ('MILLS', 'Mls'); 440INSERT INTO street_type_lookup (name, abbrev) VALUES ('MLS', 'Mls'); 441INSERT INTO street_type_lookup (name, abbrev) VALUES ('MISSION', 'Msn'); 442INSERT INTO street_type_lookup (name, abbrev) VALUES ('MISSN', 'Msn'); 443INSERT INTO street_type_lookup (name, abbrev) VALUES ('MSN', 'Msn'); 444INSERT INTO street_type_lookup (name, abbrev) VALUES ('MSSN', 'Msn'); 445INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOTORWAY', 'Mtwy'); 446INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNT', 'Mt'); 447INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNT', 'Mt'); 448INSERT INTO street_type_lookup (name, abbrev) VALUES ('MT', 'Mt'); 449INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTAIN', 'Mtn'); 450INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTN', 'Mtn'); 451INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTAIN', 'Mtn'); 452INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTIN', 'Mtn'); 453INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTIN', 'Mtn'); 454INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTN', 'Mtn'); 455INSERT INTO street_type_lookup (name, abbrev) VALUES ('MNTNS', 'Mtns'); 456INSERT INTO street_type_lookup (name, abbrev) VALUES ('MOUNTAINS', 'Mtns'); 457INSERT INTO street_type_lookup (name, abbrev) VALUES ('NCK', 'Nck'); 458INSERT INTO street_type_lookup (name, abbrev) VALUES ('NECK', 'Nck'); 459INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCH', 'Orch'); 460INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCHARD', 'Orch'); 461INSERT INTO street_type_lookup (name, abbrev) VALUES ('ORCHRD', 'Orch'); 462INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVAL', 'Oval'); 463INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVL', 'Oval'); 464INSERT INTO street_type_lookup (name, abbrev) VALUES ('OVERPASS', 'Opas'); 465INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARK', 'Park'); 466INSERT INTO street_type_lookup (name, abbrev) VALUES ('PK', 'Park'); 467INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRK', 'Park'); 468INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKS', 'Park'); 469INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWAY', 'Pkwy'); 470INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWY', 'Pkwy'); 471INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWAY', 'Pkwy'); 472INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWY', 'Pkwy'); 473INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKY', 'Pkwy'); 474INSERT INTO street_type_lookup (name, abbrev) VALUES ('PARKWAYS', 'Pkwy'); 475INSERT INTO street_type_lookup (name, abbrev) VALUES ('PKWYS', 'Pkwy'); 476INSERT INTO street_type_lookup (name, abbrev) VALUES ('PASS', 'Pass'); 477INSERT INTO street_type_lookup (name, abbrev) VALUES ('PASSAGE', 'Psge'); 478INSERT INTO street_type_lookup (name, abbrev) VALUES ('PATH', 'Path'); 479INSERT INTO street_type_lookup (name, abbrev) VALUES ('PATHS', 'Path'); 480INSERT INTO street_type_lookup (name, abbrev) VALUES ('PIKE', 'Pike'); 481INSERT INTO street_type_lookup (name, abbrev) VALUES ('PIKES', 'Pike'); 482INSERT INTO street_type_lookup (name, abbrev) VALUES ('PINE', 'Pne'); 483INSERT INTO street_type_lookup (name, abbrev) VALUES ('PINES', 'Pnes'); 484INSERT INTO street_type_lookup (name, abbrev) VALUES ('PNES', 'Pnes'); 485INSERT INTO street_type_lookup (name, abbrev) VALUES ('PL', 'Pl'); 486INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLACE', 'Pl'); 487INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAIN', 'Pln'); 488INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLN', 'Pln'); 489INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAINES', 'Plns'); 490INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAINS', 'Plns'); 491INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLNS', 'Plns'); 492INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLAZA', 'Plz'); 493INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLZ', 'Plz'); 494INSERT INTO street_type_lookup (name, abbrev) VALUES ('PLZA', 'Plz'); 495INSERT INTO street_type_lookup (name, abbrev) VALUES ('POINT', 'Pt'); 496INSERT INTO street_type_lookup (name, abbrev) VALUES ('PT', 'Pt'); 497INSERT INTO street_type_lookup (name, abbrev) VALUES ('POINTS', 'Pts'); 498INSERT INTO street_type_lookup (name, abbrev) VALUES ('PTS', 'Pts'); 499INSERT INTO street_type_lookup (name, abbrev) VALUES ('PORT', 'Prt'); 500INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRT', 'Prt'); 501INSERT INTO street_type_lookup (name, abbrev) VALUES ('PORTS', 'Prts'); 502INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRTS', 'Prts'); 503INSERT INTO street_type_lookup (name, abbrev) VALUES ('PR', 'Pr'); 504INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRAIRIE', 'Pr'); 505INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRARIE', 'Pr'); 506INSERT INTO street_type_lookup (name, abbrev) VALUES ('PRR', 'Pr'); 507INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAD', 'Radl'); 508INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADIAL', 'Radl'); 509INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADIEL', 'Radl'); 510INSERT INTO street_type_lookup (name, abbrev) VALUES ('RADL', 'Radl'); 511INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAMP', 'Ramp'); 512INSERT INTO street_type_lookup (name, abbrev) VALUES ('RANCH', 'Rnch'); 513INSERT INTO street_type_lookup (name, abbrev) VALUES ('RANCHES', 'Rnch'); 514INSERT INTO street_type_lookup (name, abbrev) VALUES ('RNCH', 'Rnch'); 515INSERT INTO street_type_lookup (name, abbrev) VALUES ('RNCHS', 'Rnch'); 516INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAPID', 'Rpd'); 517INSERT INTO street_type_lookup (name, abbrev) VALUES ('RPD', 'Rpd'); 518INSERT INTO street_type_lookup (name, abbrev) VALUES ('RAPIDS', 'Rpds'); 519INSERT INTO street_type_lookup (name, abbrev) VALUES ('RPDS', 'Rpds'); 520INSERT INTO street_type_lookup (name, abbrev) VALUES ('REST', 'Rst'); 521INSERT INTO street_type_lookup (name, abbrev) VALUES ('RST', 'Rst'); 522INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDG', 'Rdg'); 523INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDGE', 'Rdg'); 524INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIDGE', 'Rdg'); 525INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDGS', 'Rdgs'); 526INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIDGES', 'Rdgs'); 527INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIV', 'Riv'); 528INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIVER', 'Riv'); 529INSERT INTO street_type_lookup (name, abbrev) VALUES ('RIVR', 'Riv'); 530INSERT INTO street_type_lookup (name, abbrev) VALUES ('RVR', 'Riv'); 531INSERT INTO street_type_lookup (name, abbrev) VALUES ('RD', 'Rd'); 532INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROAD', 'Rd'); 533INSERT INTO street_type_lookup (name, abbrev) VALUES ('RDS', 'Rds'); 534INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROADS', 'Rds'); 535INSERT INTO street_type_lookup (name, abbrev) VALUES ('ROW', 'Row'); 536INSERT INTO street_type_lookup (name, abbrev) VALUES ('RUE', 'Rue'); 537INSERT INTO street_type_lookup (name, abbrev) VALUES ('RUN', 'Run'); 538-- Start newly added 2011-7-12 -- 539INSERT INTO street_type_lookup (name, abbrev) 540 VALUES 541 ('SERVICE DRIVE', 'Svc Dr'), 542 ('SERVICE DR', 'Svc Dr'), 543 ('SERVICE ROAD', 'Svc Rd'), 544 ('SERVICE RD', 'Svc Rd') ; 545-- end newly added 2011-07-12 -- 546INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHL', 'Shl'); 547INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOAL', 'Shl'); 548INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHLS', 'Shls'); 549INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOALS', 'Shls'); 550INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOAR', 'Shr'); 551INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHORE', 'Shr'); 552INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHR', 'Shr'); 553INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHOARS', 'Shrs'); 554INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHORES', 'Shrs'); 555INSERT INTO street_type_lookup (name, abbrev) VALUES ('SHRS', 'Shrs'); 556INSERT INTO street_type_lookup (name, abbrev) VALUES ('SKYWAY', 'Skwy'); 557INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPG', 'Spg'); 558INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPNG', 'Spg'); 559INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRING', 'Spg'); 560INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRNG', 'Spg'); 561INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPGS', 'Spgs'); 562INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPNGS', 'Spgs'); 563INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRINGS', 'Spgs'); 564INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPRNGS', 'Spgs'); 565INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPUR', 'Spur'); 566INSERT INTO street_type_lookup (name, abbrev) VALUES ('SPURS', 'Spur'); 567INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQ', 'Sq'); 568INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQR', 'Sq'); 569INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQRE', 'Sq'); 570INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQU', 'Sq'); 571INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQUARE', 'Sq'); 572INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQRS', 'Sqs'); 573INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQUARES', 'Sqs'); 574INSERT INTO street_type_lookup (name, abbrev) VALUES ('STA', 'Sta'); 575INSERT INTO street_type_lookup (name, abbrev) VALUES ('STATION', 'Sta'); 576INSERT INTO street_type_lookup (name, abbrev) VALUES ('STATN', 'Sta'); 577INSERT INTO street_type_lookup (name, abbrev) VALUES ('STN', 'Sta'); 578INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRA', 'Stra'); 579INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAV', 'Stra'); 580INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVE', 'Stra'); 581INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVEN', 'Stra'); 582INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVENUE', 'Stra'); 583INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRAVN', 'Stra'); 584INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRVN', 'Stra'); 585INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRVNUE', 'Stra'); 586INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREAM', 'Strm'); 587INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREME', 'Strm'); 588INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRM', 'Strm'); 589INSERT INTO street_type_lookup (name, abbrev) VALUES ('ST', 'St'); 590INSERT INTO street_type_lookup (name, abbrev) VALUES ('STR', 'St'); 591INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREET', 'St'); 592INSERT INTO street_type_lookup (name, abbrev) VALUES ('STRT', 'St'); 593INSERT INTO street_type_lookup (name, abbrev) VALUES ('STREETS', 'Sts'); 594INSERT INTO street_type_lookup (name, abbrev) VALUES ('SMT', 'Smt'); 595INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMIT', 'Smt'); 596INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMITT', 'Smt'); 597INSERT INTO street_type_lookup (name, abbrev) VALUES ('SUMMIT', 'Smt'); 598INSERT INTO street_type_lookup (name, abbrev) VALUES ('TER', 'Ter'); 599INSERT INTO street_type_lookup (name, abbrev) VALUES ('TERR', 'Ter'); 600INSERT INTO street_type_lookup (name, abbrev) VALUES ('TERRACE', 'Ter'); 601INSERT INTO street_type_lookup (name, abbrev) VALUES ('THROUGHWAY', 'Trwy'); 602INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACE', 'Trce'); 603INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACES', 'Trce'); 604INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRCE', 'Trce'); 605INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACK', 'Trak'); 606INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRACKS', 'Trak'); 607INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAK', 'Trak'); 608INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRK', 'Trak'); 609INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRKS', 'Trak'); 610INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAFFICWAY', 'Trfy'); 611INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRFY', 'Trfy'); 612INSERT INTO street_type_lookup (name, abbrev) VALUES ('TR', 'Trl'); 613INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAIL', 'Trl'); 614INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRAILS', 'Trl'); 615INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRL', 'Trl'); 616INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRLS', 'Trl'); 617INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNEL', 'Tunl'); 618INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNL', 'Tunl'); 619INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNLS', 'Tunl'); 620INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNEL', 'Tunl'); 621INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNELS', 'Tunl'); 622INSERT INTO street_type_lookup (name, abbrev) VALUES ('TUNNL', 'Tunl'); 623INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNDERPASS', 'Upas'); 624INSERT INTO street_type_lookup (name, abbrev) VALUES ('UN', 'Un'); 625INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNION', 'Un'); 626INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNIONS', 'Uns'); 627INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLEY', 'Vly'); 628INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLY', 'Vly'); 629INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLLY', 'Vly'); 630INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLY', 'Vly'); 631INSERT INTO street_type_lookup (name, abbrev) VALUES ('VALLEYS', 'Vlys'); 632INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLYS', 'Vlys'); 633INSERT INTO street_type_lookup (name, abbrev) VALUES ('VDCT', 'Via'); 634INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIA', 'Via'); 635INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIADCT', 'Via'); 636INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIADUCT', 'Via'); 637INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIEW', 'Vw'); 638INSERT INTO street_type_lookup (name, abbrev) VALUES ('VW', 'Vw'); 639INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIEWS', 'Vws'); 640INSERT INTO street_type_lookup (name, abbrev) VALUES ('VWS', 'Vws'); 641INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILL', 'Vlg'); 642INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAG', 'Vlg'); 643INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAGE', 'Vlg'); 644INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLG', 'Vlg'); 645INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLIAGE', 'Vlg'); 646INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLG', 'Vlg'); 647INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLAGES', 'Vlgs'); 648INSERT INTO street_type_lookup (name, abbrev) VALUES ('VLGS', 'Vlgs'); 649INSERT INTO street_type_lookup (name, abbrev) VALUES ('VILLE', 'Vl'); 650INSERT INTO street_type_lookup (name, abbrev) VALUES ('VL', 'Vl'); 651INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIS', 'Vis'); 652INSERT INTO street_type_lookup (name, abbrev) VALUES ('VIST', 'Vis'); 653INSERT INTO street_type_lookup (name, abbrev) VALUES ('VISTA', 'Vis'); 654INSERT INTO street_type_lookup (name, abbrev) VALUES ('VST', 'Vis'); 655INSERT INTO street_type_lookup (name, abbrev) VALUES ('VSTA', 'Vis'); 656INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALK', 'Walk'); 657INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALKS', 'Walk'); 658INSERT INTO street_type_lookup (name, abbrev) VALUES ('WALL', 'Wall'); 659INSERT INTO street_type_lookup (name, abbrev) VALUES ('WAY', 'Way'); 660INSERT INTO street_type_lookup (name, abbrev) VALUES ('WY', 'Way'); 661INSERT INTO street_type_lookup (name, abbrev) VALUES ('WAYS', 'Ways'); 662INSERT INTO street_type_lookup (name, abbrev) VALUES ('WELL', 'Wl'); 663INSERT INTO street_type_lookup (name, abbrev) VALUES ('WELLS', 'Wls'); 664INSERT INTO street_type_lookup (name, abbrev) VALUES ('WLS', 'Wls'); 665INSERT INTO street_type_lookup (name, abbrev) VALUES ('BYU', 'Byu'); 666INSERT INTO street_type_lookup (name, abbrev) VALUES ('BLFS', 'Blfs'); 667INSERT INTO street_type_lookup (name, abbrev) VALUES ('BRKS', 'Brks'); 668INSERT INTO street_type_lookup (name, abbrev) VALUES ('BG', 'Bg'); 669INSERT INTO street_type_lookup (name, abbrev) VALUES ('BGS', 'Bgs'); 670INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTRS', 'Ctrs'); 671INSERT INTO street_type_lookup (name, abbrev) VALUES ('CIRS', 'Cirs'); 672INSERT INTO street_type_lookup (name, abbrev) VALUES ('CMN', 'Cmn'); 673INSERT INTO street_type_lookup (name, abbrev) VALUES ('CTS', 'Cts'); 674INSERT INTO street_type_lookup (name, abbrev) VALUES ('CVS', 'Cvs'); 675INSERT INTO street_type_lookup (name, abbrev) VALUES ('CRST', 'Crst'); 676INSERT INTO street_type_lookup (name, abbrev) VALUES ('XRD', 'Xrd'); 677INSERT INTO street_type_lookup (name, abbrev) VALUES ('CURV', 'Curv'); 678INSERT INTO street_type_lookup (name, abbrev) VALUES ('DRS', 'Drs'); 679INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRDS', 'Frds'); 680INSERT INTO street_type_lookup (name, abbrev) VALUES ('FRGS', 'Frgs'); 681INSERT INTO street_type_lookup (name, abbrev) VALUES ('GLNS', 'Glns'); 682INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRNS', 'Grns'); 683INSERT INTO street_type_lookup (name, abbrev) VALUES ('GRVS', 'Grvs'); 684INSERT INTO street_type_lookup (name, abbrev) VALUES ('HBRS', 'Hbrs'); 685INSERT INTO street_type_lookup (name, abbrev) VALUES ('LGTS', 'Lgts'); 686INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTWY', 'Mtwy'); 687INSERT INTO street_type_lookup (name, abbrev) VALUES ('MTNS', 'Mtns'); 688INSERT INTO street_type_lookup (name, abbrev) VALUES ('OPAS', 'Opas'); 689INSERT INTO street_type_lookup (name, abbrev) VALUES ('PSGE', 'Psge'); 690INSERT INTO street_type_lookup (name, abbrev) VALUES ('PNE', 'Pne'); 691INSERT INTO street_type_lookup (name, abbrev) VALUES ('RTE', 'Rte'); 692INSERT INTO street_type_lookup (name, abbrev) VALUES ('SKWY', 'Skwy'); 693INSERT INTO street_type_lookup (name, abbrev) VALUES ('SQS', 'Sqs'); 694INSERT INTO street_type_lookup (name, abbrev) VALUES ('STS', 'Sts'); 695INSERT INTO street_type_lookup (name, abbrev) VALUES ('TRWY', 'Trwy'); 696INSERT INTO street_type_lookup (name, abbrev) VALUES ('UPAS', 'Upas'); 697INSERT INTO street_type_lookup (name, abbrev) VALUES ('UNS', 'Uns'); 698INSERT INTO street_type_lookup (name, abbrev) VALUES ('WL', 'Wl'); 699 700-- prefix and suffix street names for highways and foreign named roads 701-- where street type is at front of streetname instead of after 702-- these usually have numbers for street names and often have spaces in type 703INSERT INTO street_type_lookup (name, abbrev, is_hw) 704SELECT name, abbrev, true 705 FROM (VALUES 706 ('CAM', 'Cam'), 707 ('CAM.', 'Cam'), 708 ('CAMINO', 'Cam'), 709 ('CO HWY', 'Co Hwy'), 710 ('COUNTY HWY', 'Co Hwy'), 711 ('COUNTY HIGHWAY', 'Co Hwy'), 712 ('COUNTY HIGH WAY', 'Co Hwy'), 713 ('COUNTY ROAD', 'Co Rd'), 714 ('COUNTY RD', 'Co Rd'), 715 ('CO RD', 'Co Rd'), 716 ('CORD', 'Co Rd'), 717 ('CO RTE', 'Co Rte'), 718 ('COUNTY ROUTE', 'Co Rte'), 719 ('CO ST AID HWY', 'Co St Aid Hwy'), 720 ('EXP', 'Expy'), 721 ('EXPR', 'Expy'), 722 ('EXPRESS', 'Expy'), 723 ('EXPRESSWAY', 'Expy'), 724 ('EXPW', 'Expy'), 725 ('EXPY', 'Expy'), 726 ('FARM RD', 'Farm Rd'), 727 ('FIRE RD', 'Fire Rd'), 728 ('FOREST RD', 'Forest Rd'), 729 ('FOREST ROAD', 'Forest Rd'), 730 ('FOREST RTE', 'Forest Rte'), 731 ('FOREST ROUTE', 'Forest Rte'), 732 ('FREEWAY', 'Fwy'), 733 ('FREEWY', 'Fwy'), 734 ('FRWAY', 'Fwy'), 735 ('FRWY', 'Fwy'), 736 ('FWY', 'Fwy'), 737 ('HIGHWAY', 'Hwy'), 738 ('HIGHWY', 'Hwy'), 739 ('HIWAY', 'Hwy'), 740 ('HIWY', 'Hwy'), 741 ('HWAY', 'Hwy'), 742 ('HWY', 'Hwy'), 743 ('I', 'I-'), 744 ('I-', 'I-'), 745 ('INTERSTATE', 'I-'), 746 ('INTERSTATE ROUTE', 'I-'), 747 ('INTERSTATE RTE', 'I-'), 748 ('INTERSTATE RTE.', 'I-'), 749 ('INTERSTATE RT', 'I-'), 750 ('LOOP', 'Loop'), 751 ('ROUTE', 'Rte'), 752 ('RTE', 'Rte'), 753 ('RT', 'Rte'), 754 ('STATE HWY', 'State Hwy'), 755 ('STATE HIGHWAY', 'State Hwy'), 756 ('STATE HIGH WAY', 'State Hwy'), 757 ('STATE RD', 'State Rd'), 758 ('STATE ROAD', 'State Rd'), 759 ('STATE ROUTE', 'State Rte'), 760 ('STATE RTE', 'State Rte'), 761 ('TPK', 'Tpke'), 762 ('TPKE', 'Tpke'), 763 ('TRNPK', 'Tpke'), 764 ('TRPK', 'Tpke'), 765 ('TURNPIKE', 'Tpke'), 766 ('TURNPK', 'Tpke'), 767 ('US HWY', 'US Hwy'), 768 ('US HIGHWAY', 'US Hwy'), 769 ('US HIGH WAY', 'US Hwy'), 770 ('U.S.', 'US Hwy'), 771 ('US RTE', 'US Rte'), 772 ('US ROUTE', 'US Rte'), 773 ('US RT', 'US Rte'), 774 ('USFS HWY', 'USFS Hwy'), 775 ('USFS HIGHWAY', 'USFS Hwy'), 776 ('USFS HIGH WAY', 'USFS Hwy'), 777 ('USFS RD', 'USFS Rd'), 778 ('USFS ROAD', 'USFS Rd') 779 ) t(name, abbrev) 780 WHERE t.name NOT IN(SELECT name FROM street_type_lookup); 781CREATE INDEX street_type_lookup_abbrev_idx ON street_type_lookup (abbrev); 782 783-- Create place and countysub lookup tables 784DROP TABLE IF EXISTS tiger.place_lookup; 785CREATE TABLE place_lookup ( 786 st_code INTEGER, 787 state VARCHAR(2), 788 pl_code INTEGER, 789 name VARCHAR(90), 790 PRIMARY KEY (st_code,pl_code) 791); 792 793/** 794INSERT INTO place_lookup 795 SELECT 796 pl.state::integer as st_code, 797 sl.abbrev as state, 798 pl.placefp::integer as pl_code, 799 pl.name as name 800 FROM 801 pl99_d00 pl 802 JOIN state_lookup sl ON (pl.state = lpad(sl.st_code,2,'0')) 803 GROUP BY pl.state, sl.abbrev, pl.placefp, pl.name; 804**/ 805CREATE INDEX place_lookup_name_idx ON place_lookup (soundex(name)); 806CREATE INDEX place_lookup_state_idx ON place_lookup (state); 807 808DROP TABLE IF EXISTS tiger.county_lookup; 809CREATE TABLE county_lookup ( 810 st_code INTEGER, 811 state VARCHAR(2), 812 co_code INTEGER, 813 name VARCHAR(90), 814 PRIMARY KEY (st_code, co_code) 815); 816 817/** 818INSERT INTO county_lookup 819 SELECT 820 co.state::integer as st_code, 821 sl.abbrev as state, 822 co.county::integer as co_code, 823 co.name as name 824 FROM 825 co99_d00 co 826 JOIN state_lookup sl ON (co.state = lpad(sl.st_code,2,'0')) 827 GROUP BY co.state, sl.abbrev, co.county, co.name; 828**/ 829CREATE INDEX county_lookup_name_idx ON county_lookup (soundex(name)); 830CREATE INDEX county_lookup_state_idx ON county_lookup (state); 831 832DROP TABLE IF EXISTS tiger.countysub_lookup; 833CREATE TABLE countysub_lookup ( 834 st_code INTEGER, 835 state VARCHAR(2), 836 co_code INTEGER, 837 county VARCHAR(90), 838 cs_code INTEGER, 839 name VARCHAR(90), 840 PRIMARY KEY (st_code, co_code, cs_code) 841); 842 843/** 844INSERT INTO countysub_lookup 845 SELECT 846 cs.state::integer as st_code, 847 sl.abbrev as state, 848 cs.county::integer as co_code, 849 cl.name as county, 850 cs.cousubfp::integer as cs_code, 851 cs.name as name 852 FROM 853 cs99_d00 cs 854 JOIN state_lookup sl ON (cs.state = lpad(sl.st_code,2,'0')) 855 JOIN county_lookup cl ON (cs.state = lpad(cl.st_code,2,'0') AND cs.county = cl.co_code) 856 GROUP BY cs.state, sl.abbrev, cs.county, cl.name, cs.cousubfp, cs.name; 857**/ 858CREATE INDEX countysub_lookup_name_idx ON countysub_lookup (soundex(name)); 859CREATE INDEX countysub_lookup_state_idx ON countysub_lookup (state); 860 861DROP TABLE IF EXISTS tiger.zip_lookup_all; 862CREATE TABLE zip_lookup_all ( 863 zip INTEGER, 864 st_code INTEGER, 865 state VARCHAR(2), 866 co_code INTEGER, 867 county VARCHAR(90), 868 cs_code INTEGER, 869 cousub VARCHAR(90), 870 pl_code INTEGER, 871 place VARCHAR(90), 872 cnt INTEGER 873); 874 875/** SET work_mem = '2GB'; 876 877INSERT INTO zip_lookup_all 878 SELECT *,count(*) as cnt FROM 879 (SELECT 880 zipl as zip, 881 rl.statel as st_code, 882 sl.abbrev as state, 883 rl.countyl as co_code, 884 cl.name as county, 885 rl.cousubl as cs_code, 886 cs.name as countysub, 887 rl.placel as pl_code, 888 pl.name as place 889 FROM 890 roads_local rl 891 JOIN state_lookup sl ON (rl.statel = lpad(sl.st_code,2,'0')) 892 LEFT JOIN county_lookup cl ON (rl.statel = lpad(cl.st_code,2,'0') AND rl.countyl = cl.co_code) 893 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) 894 LEFT JOIN place_lookup pl ON (rl.statel = lpad(pl.st_code,2,'0') AND rl.placel = pl.pl_code) 895 WHERE zipl IS NOT NULL 896 UNION ALL 897 SELECT 898 zipr as zip, 899 rl.stater as st_code, 900 sl.abbrev as state, 901 rl.countyr as co_code, 902 cl.name as county, 903 rl.cousubr as cs_code, 904 cs.name as countysub, 905 rl.placer as pl_code, 906 pl.name as place 907 FROM 908 roads_local rl 909 JOIN state_lookup sl ON (rl.stater = lpad(sl.st_code,2,'0')) 910 LEFT JOIN county_lookup cl ON (rl.stater = lpad(cl.st_code,2,'0') AND rl.countyr = cl.co_code) 911 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) 912 LEFT JOIN place_lookup pl ON (rl.stater = lpad(pl.st_code,2,'0') AND rl.placer = pl.pl_code) 913 WHERE zipr IS NOT NULL 914 ) as subquery 915 GROUP BY zip, st_code, state, co_code, county, cs_code, countysub, pl_code, place; 916**/ 917DROP TABLE IF EXISTS tiger.zip_lookup_base; 918CREATE TABLE zip_lookup_base ( 919 zip varchar(5), 920 state VARCHAR(40), 921 county VARCHAR(90), 922 city VARCHAR(90), 923 statefp varchar(2), 924 PRIMARY KEY (zip) 925); 926 927-- INSERT INTO zip_lookup_base 928-- Populate through magic 929-- If anyone knows of a good, public, free, place to pull this information from, that'd be awesome to have... 930 931DROP TABLE IF EXISTS tiger.zip_lookup; 932CREATE TABLE zip_lookup ( 933 zip INTEGER, 934 st_code INTEGER, 935 state VARCHAR(2), 936 co_code INTEGER, 937 county VARCHAR(90), 938 cs_code INTEGER, 939 cousub VARCHAR(90), 940 pl_code INTEGER, 941 place VARCHAR(90), 942 cnt INTEGER, 943 PRIMARY KEY (zip) 944); 945 946DROP TABLE IF EXISTS tiger.zcta500; 947/** 948INSERT INTO zip_lookup 949 SELECT 950 DISTINCT ON (zip) 951 zip, 952 st_code, 953 state, 954 co_code, 955 county, 956 cs_code, 957 cousub, 958 pl_code, 959 place, 960 cnt 961 FROM zip_lookup_all 962 ORDER BY zip,cnt desc; 963 **/ 964DROP TABLE IF EXISTS tiger.county; 965CREATE TABLE county 966( 967 gid SERIAL NOT NULL, 968 statefp character varying(2), 969 countyfp character varying(3), 970 countyns character varying(8), 971 cntyidfp character varying(5) NOT NULL, 972 "name" character varying(100), 973 namelsad character varying(100), 974 lsad character varying(2), 975 classfp character varying(2), 976 mtfcc character varying(5), 977 csafp character varying(3), 978 cbsafp character varying(5), 979 metdivfp character varying(5), 980 funcstat character varying(1), 981 aland bigint, 982 awater double precision, 983 intptlat character varying(11), 984 intptlon character varying(12), 985 the_geom geometry, 986 CONSTRAINT uidx_county_gid UNIQUE (gid), 987 CONSTRAINT pk_tiger_county PRIMARY KEY (cntyidfp), 988 CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2), 989 CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 990 CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269) 991); 992CREATE INDEX idx_tiger_county ON county USING btree (countyfp); 993 994DROP TABLE IF EXISTS tiger.state; 995CREATE TABLE state 996( 997 gid serial NOT NULL, 998 region character varying(2), 999 division character varying(2), 1000 statefp character varying(2), 1001 statens character varying(8), 1002 stusps character varying(2) NOT NULL, 1003 "name" character varying(100), 1004 lsad character varying(2), 1005 mtfcc character varying(5), 1006 funcstat character varying(1), 1007 aland bigint, 1008 awater bigint, 1009 intptlat character varying(11), 1010 intptlon character varying(12), 1011 the_geom geometry, 1012 CONSTRAINT uidx_tiger_state_stusps UNIQUE (stusps), 1013 CONSTRAINT uidx_tiger_state_gid UNIQUE (gid), 1014 CONSTRAINT pk_tiger_state PRIMARY KEY (statefp), 1015 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), 1016 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 1017 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) 1018); 1019CREATE INDEX idx_tiger_state_the_geom_gist ON state USING gist(the_geom); 1020 1021DROP TABLE IF EXISTS tiger.place; 1022CREATE TABLE place 1023( 1024 gid serial NOT NULL, 1025 statefp character varying(2), 1026 placefp character varying(5), 1027 placens character varying(8), 1028 plcidfp character varying(7) PRIMARY KEY, 1029 "name" character varying(100), 1030 namelsad character varying(100), 1031 lsad character varying(2), 1032 classfp character varying(2), 1033 cpi character varying(1), 1034 pcicbsa character varying(1), 1035 pcinecta character varying(1), 1036 mtfcc character varying(5), 1037 funcstat character varying(1), 1038 aland bigint, 1039 awater bigint, 1040 intptlat character varying(11), 1041 intptlon character varying(12), 1042 the_geom geometry, 1043 CONSTRAINT uidx_tiger_place_gid UNIQUE (gid), 1044 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), 1045 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 1046 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) 1047); 1048CREATE INDEX tiger_place_the_geom_gist ON place USING gist(the_geom); 1049 1050DROP TABLE IF EXISTS tiger.zip_state; 1051CREATE TABLE zip_state 1052( 1053 zip character varying(5) NOT NULL, 1054 stusps character varying(2) NOT NULL, 1055 statefp character varying(2), 1056 CONSTRAINT zip_state_pkey PRIMARY KEY (zip, stusps) 1057); 1058 1059DROP TABLE IF EXISTS tiger.zip_state_loc; 1060CREATE TABLE zip_state_loc 1061( 1062 zip character varying(5) NOT NULL, 1063 stusps character varying(2) NOT NULL, 1064 statefp character varying(2), 1065 place varchar(100), 1066 CONSTRAINT zip_state_loc_pkey PRIMARY KEY (zip, stusps, place) 1067); 1068 1069DROP TABLE IF EXISTS tiger.cousub; 1070CREATE TABLE cousub 1071( 1072 gid serial NOT NULL, 1073 statefp character varying(2), 1074 countyfp character varying(3), 1075 cousubfp character varying(5), 1076 cousubns character varying(8), 1077 cosbidfp character varying(10) NOT NULL PRIMARY KEY, 1078 "name" character varying(100), 1079 namelsad character varying(100), 1080 lsad character varying(2), 1081 classfp character varying(2), 1082 mtfcc character varying(5), 1083 cnectafp character varying(3), 1084 nectafp character varying(5), 1085 nctadvfp character varying(5), 1086 funcstat character varying(1), 1087 aland numeric(14), 1088 awater numeric(14), 1089 intptlat character varying(11), 1090 intptlon character varying(12), 1091 the_geom geometry, 1092 CONSTRAINT uidx_cousub_gid UNIQUE (gid), 1093 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), 1094 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 1095 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) 1096); 1097 1098CREATE INDEX tige_cousub_the_geom_gist ON cousub USING gist(the_geom); 1099 1100DROP TABLE IF EXISTS tiger.edges; 1101CREATE TABLE edges 1102( 1103 gid SERIAL NOT NULL PRIMARY KEY, 1104 statefp character varying(2), 1105 countyfp character varying(3), 1106 tlid bigint, 1107 tfidl numeric(10), 1108 tfidr numeric(10), 1109 mtfcc character varying(5), 1110 fullname character varying(100), 1111 smid character varying(22), 1112 lfromadd character varying(12), 1113 ltoadd character varying(12), 1114 rfromadd character varying(12), 1115 rtoadd character varying(12), 1116 zipl character varying(5), 1117 zipr character varying(5), 1118 featcat character varying(1), 1119 hydroflg character varying(1), 1120 railflg character varying(1), 1121 roadflg character varying(1), 1122 olfflg character varying(1), 1123 passflg character varying(1), 1124 divroad character varying(1), 1125 exttyp character varying(1), 1126 ttyp character varying(1), 1127 deckedroad character varying(1), 1128 artpath character varying(1), 1129 persist character varying(1), 1130 gcseflg character varying(1), 1131 offsetl character varying(1), 1132 offsetr character varying(1), 1133 tnidf numeric(10), 1134 tnidt numeric(10), 1135 the_geom geometry, 1136 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), 1137 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL), 1138 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) 1139); 1140CREATE INDEX idx_edges_tlid ON edges USING btree(tlid); 1141CREATE INDEX idx_tiger_edges_countyfp ON edges USING btree(countyfp); 1142CREATE INDEX idx_tiger_edges_the_geom_gist ON edges USING gist(the_geom); 1143 1144DROP TABLE IF EXISTS tiger.addrfeat; 1145CREATE TABLE addrfeat 1146( 1147 gid serial not null primary key, 1148 tlid bigint, 1149 statefp character varying(2) NOT NULL, 1150 aridl character varying(22), 1151 aridr character varying(22), 1152 linearid character varying(22), 1153 fullname character varying(100), 1154 lfromhn character varying(12), 1155 ltohn character varying(12), 1156 rfromhn character varying(12), 1157 rtohn character varying(12), 1158 zipl character varying(5), 1159 zipr character varying(5), 1160 edge_mtfcc character varying(5), 1161 parityl character varying(1), 1162 parityr character varying(1), 1163 plus4l character varying(4), 1164 plus4r character varying(4), 1165 lfromtyp character varying(1), 1166 ltotyp character varying(1), 1167 rfromtyp character varying(1), 1168 rtotyp character varying(1), 1169 offsetl character varying(1), 1170 offsetr character varying(1), 1171 the_geom geometry, 1172 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), 1173 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'LINESTRING'::text OR the_geom IS NULL), 1174 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) 1175); 1176CREATE INDEX idx_addrfeat_geom_gist ON addrfeat USING gist(the_geom ); 1177CREATE INDEX idx_addrfeat_tlid ON addrfeat USING btree(tlid); 1178CREATE INDEX idx_addrfeat_zipl ON addrfeat USING btree(zipl); 1179CREATE INDEX idx_addrfeat_zipr ON addrfeat USING btree(zipr); 1180 1181DROP TABLE IF EXISTS tiger.faces; 1182CREATE TABLE faces 1183( 1184gid serial NOT NULL PRIMARY KEY, 1185 tfid numeric(10,0), 1186 statefp00 varchar(2), 1187 countyfp00 varchar(3), 1188 tractce00 varchar(6), 1189 blkgrpce00 varchar(1), 1190 blockce00 varchar(4), 1191 cousubfp00 varchar(5), 1192 submcdfp00 varchar(5), 1193 conctyfp00 varchar(5), 1194 placefp00 varchar(5), 1195 aiannhfp00 varchar(5), 1196 aiannhce00 varchar(4), 1197 comptyp00 varchar(1), 1198 trsubfp00 varchar(5), 1199 trsubce00 varchar(3), 1200 anrcfp00 varchar(5), 1201 elsdlea00 varchar(5), 1202 scsdlea00 varchar(5), 1203 unsdlea00 varchar(5), 1204 uace00 varchar(5), 1205 cd108fp varchar(2), 1206 sldust00 varchar(3), 1207 sldlst00 varchar(3), 1208 vtdst00 varchar(6), 1209 zcta5ce00 varchar(5), 1210 tazce00 varchar(6), 1211 ugace00 varchar(5), 1212 puma5ce00 varchar(5), 1213 statefp varchar(2), 1214 countyfp varchar(3), 1215 tractce varchar(6), 1216 blkgrpce varchar(1), 1217 blockce varchar(4), 1218 cousubfp varchar(5), 1219 submcdfp varchar(5), 1220 conctyfp varchar(5), 1221 placefp varchar(5), 1222 aiannhfp varchar(5), 1223 aiannhce varchar(4), 1224 comptyp varchar(1), 1225 trsubfp varchar(5), 1226 trsubce varchar(3), 1227 anrcfp varchar(5), 1228 ttractce varchar(6), 1229 tblkgpce varchar(1), 1230 elsdlea varchar(5), 1231 scsdlea varchar(5), 1232 unsdlea varchar(5), 1233 uace varchar(5), 1234 cd111fp varchar(2), 1235 sldust varchar(3), 1236 sldlst varchar(3), 1237 vtdst varchar(6), 1238 zcta5ce varchar(5), 1239 tazce varchar(6), 1240 ugace varchar(5), 1241 puma5ce varchar(5), 1242 csafp varchar(3), 1243 cbsafp varchar(5), 1244 metdivfp varchar(5), 1245 cnectafp varchar(3), 1246 nectafp varchar(5), 1247 nctadvfp varchar(5), 1248 lwflag varchar(1), 1249 "offset" varchar(1), 1250 atotal double precision, 1251 intptlat varchar(11), 1252 intptlon varchar(12), 1253 the_geom geometry, 1254 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), 1255 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 1256 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269) 1257); 1258CREATE INDEX idx_tiger_faces_tfid ON faces USING btree (tfid); 1259CREATE INDEX idx_tiger_faces_countyfp ON faces USING btree(countyfp); 1260CREATE INDEX tiger_faces_the_geom_gist ON faces USING gist(the_geom); 1261 1262DROP TABLE IF EXISTS tiger.featnames; 1263CREATE TABLE featnames 1264( 1265 gid SERIAL NOT NULL, 1266 tlid bigint, 1267 fullname character varying(100), 1268 "name" character varying(100), 1269 predirabrv character varying(15), 1270 pretypabrv character varying(50), 1271 prequalabr character varying(15), 1272 sufdirabrv character varying(15), 1273 suftypabrv character varying(50), 1274 sufqualabr character varying(15), 1275 predir character varying(2), 1276 pretyp character varying(3), 1277 prequal character varying(2), 1278 sufdir character varying(2), 1279 suftyp character varying(3), 1280 sufqual character varying(2), 1281 linearid character varying(22), 1282 mtfcc character varying(5), 1283 paflag character varying(1), 1284 CONSTRAINT featnames_pkey PRIMARY KEY (gid) 1285); 1286ALTER TABLE featnames ADD COLUMN statefp character varying(2); 1287CREATE INDEX idx_tiger_featnames_snd_name ON featnames USING btree (soundex(name)); 1288CREATE INDEX idx_tiger_featnames_lname ON featnames USING btree (lower(name)); 1289CREATE INDEX idx_tiger_featnames_tlid_statefp ON featnames USING btree (tlid,statefp); 1290 1291CREATE TABLE addr 1292( 1293 gid SERIAL NOT NULL, 1294 tlid bigint, 1295 fromhn character varying(12), 1296 tohn character varying(12), 1297 side character varying(1), 1298 zip character varying(5), 1299 plus4 character varying(4), 1300 fromtyp character varying(1), 1301 totyp character varying(1), 1302 fromarmid integer, 1303 toarmid integer, 1304 arid character varying(22), 1305 mtfcc character varying(5), 1306 CONSTRAINT addr_pkey PRIMARY KEY (gid) 1307); 1308ALTER TABLE addr ADD COLUMN statefp character varying(2); 1309 1310CREATE INDEX idx_tiger_addr_tlid_statefp ON addr USING btree(tlid,statefp); 1311CREATE INDEX idx_tiger_addr_zip ON addr USING btree (zip); 1312 1313--DROP TABLE IF EXISTS tiger.zcta5; 1314CREATE TABLE zcta5 1315( 1316 gid serial NOT NULL, 1317 statefp character varying(2), 1318 zcta5ce character varying(5), 1319 classfp character varying(2), 1320 mtfcc character varying(5), 1321 funcstat character varying(1), 1322 aland double precision, 1323 awater double precision, 1324 intptlat character varying(11), 1325 intptlon character varying(12), 1326 partflg character varying(1), 1327 the_geom geometry, 1328 CONSTRAINT uidx_tiger_zcta5_gid UNIQUE (gid), 1329 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), 1330 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL), 1331 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4269), 1332 CONSTRAINT pk_tiger_zcta5_zcta5ce PRIMARY KEY (zcta5ce,statefp) 1333 ); 1334