1--\timing 2SELECT '#887' As ticket, * FROM normalize_address('2450 N COLORADO ST, PHILADELPHIA, PA, 19132'); 3SELECT '#1051a' As ticket, * FROM normalize_address('212 3rd Ave N Suite 560, Minneapolis, MN 55401'); 4SELECT '#1051b' As ticket, * FROM normalize_address('3937 43RD AVE S, MINNEAPOLIS, MN 55406'); 5SELECT '#1051c' As ticket, * FROM normalize_address('212 N 3rd Ave, Minneapolis, MN 55401'); 6-- City missing , -- NOTE this one won't normalize right if you don't have MN data loaded 7SELECT '#1051d' As ticket, * FROM normalize_address('212 3rd Ave N Minneapolis, MN 55401'); 8-- comma in wrong spot 9SELECT * FROM normalize_address('529 Main Street, Boston MA, 02129'); 10-- comma in right spot 11SELECT * FROM normalize_address('529 Main Street, Boston,MA 02129'); 12-- partial address 13SELECT * FROM normalize_address('529 Main Street, Boston, MA'); 14-- Full address with suite using , 15SELECT * FROM normalize_address('529 Main Street, Apt 201, Boston, MA 02129'); 16-- Full address with apart using space 17SELECT * FROM normalize_address('529 Main Street Apt 201, Boston, MA 02129'); 18-- Partial address with apartment 19SELECT * FROM normalize_address('529 Main Street, Apt 201, Boston, MA'); 20 21--This one fails so lead out for now 22SELECT '#1108a' As ticket, * FROM normalize_address('529 Main Street, Suite 201, Boston, MA 02129'); 23 24-- Partial and Mangled zipcodes 25SELECT '#1073a' As ticket, * FROM normalize_address('212 3rd Ave N, MINNEAPOLIS, MN 553404'); 26SELECT '#1073b' As ticket, * FROM normalize_address('212 3rd Ave N, MINNEAPOLIS, MN 55401-'); 27SELECT '#1073c' As ticket, * FROM normalize_address('529 Main Street, Boston, MA 021'); 28 29-- comma in wrong position 30SELECT '#1086a' As ticket, * FROM normalize_address('949 N 3rd St, New Hyde Park, NY, 11040'); 31 32-- comma in right position -- 33SELECT '#1086b' As ticket, * FROM normalize_address('949 N 3rd St, New Hyde Park, NY 11040'); 34 35-- country roads and highways with spaces in street type 36SELECT '#1076a' As ticket, * FROM normalize_address('16725 Co Rd 24, Plymouth, MN 55447'); 37SELECT '#1076b' As ticket, * FROM normalize_address('16725 County Road 24, Plymouth, MN 55447'); 38SELECT '#1076c' As ticket, * FROM normalize_address('13800 County Hwy 9, Andover, MN 55304'); 39SELECT '#1076d' As ticket, * FROM normalize_address('13800 9, Andover, MN 55304'); 40-- this one is a regular street that happens to have a street type as the name 41SELECT '#1076e' As ticket, * FROM normalize_address('14 Forest Road, Acton, MA'); 42 43-- A country road with a letter name and direction 44-- NOTE this doesn't completely normalize right since the direction W is being cut off -- 45SELECT '#1076f' As ticket, * FROM normalize_address('1940 County Road C W, Roseville, MN 55113'); 46 47-- Route with a name that sounds like a direction -- 48SELECT '#1076g' As ticket, * FROM normalize_address('3900 Route 6, Eastham, Massachusetts 02642'); 49 50-- Street that has same name as type -- 51SELECT '#1076h' As ticket, * FROM normalize_address('4533 PARK AVE S, MINNEAPOLIS, MN 55407'); 52-- same street with alternate county name 53SELECT '#1076i' As ticket, * FROM normalize_address('4533 County Road 33, MINNEAPOLIS, MN 55407'); 54 55-- Same case of street type that has name as a type -- 56-- this matches - 57SELECT '#1109a' As ticket, * from normalize_address('4373 LAKE DRIVE, ROBBINSDALE, MN 55422'); 58 59-- this failed -- 60SELECT '#1109b' As ticket, * from normalize_address('4373 LAKE DR, ROBBINSDALE, MN 55422'); 61 62-- another type (Is) that is part of street name but a compound street name 63SELECT '#1074a' As ticket, * FROM normalize_address('3420 RHODE ISLAND AVE S, ST. LOUIS PARK, MN 55426'); 64 65-- another type that is part of street name -- 66SELECT '#1074b' As ticket, * FROM normalize_address('26 Court Street, Boston,MA 02109'); 67 68-- service roads and interstates 69SELECT '#1112a' As ticket, * FROM normalize_address('8401 W 35W Service Dr NE, Blaine, MN 55449'); 70SELECT '#1112b' As ticket, * FROM normalize_address('8401 35W, Blaine, MN 55449'); 71SELECT '#1112c' As ticket, * FROM normalize_address('8401 35W West, Blaine, MN 55449'); 72SELECT '#1112d' As ticket, * FROM normalize_address('8401 West 35W, Blaine, MN 55449'); 73SELECT '#1112e' As ticket, * FROM normalize_address('8401 W 35W, Blaine, MN 55449'); 74 75-- Testing pretty print of highway addresses 76-- These tests excerpted from Brian Hamlin's CASS failures 77-- in #1077 78SELECT '#1125a' As ticket, pprint_addy(normalize_address('19596 COUNTY ROAD 480, COLCORD, OK 74338')); 79--(somehow broke #1125b 80SELECT '#1125b' As ticket, pprint_addy(addy), addy.* FROM normalize_address('4345 ROUTE 353, SALAMANCA, NY 14779') AS addy; 81SELECT '#1125c' As ticket, pprint_addy(addy), addy.* FROM normalize_address('19799 STATE ROUTE O, COSBY, MO 64436') AS addy; 82 83-- some more to test interstate permutations - currently fails 84SELECT '#1125d' As ticket, pprint_addy(addy), addy.* FROM normalize_address('Interstate 90,Boston, MA') As addy; 85-- this one is wrong (because the lack of space trips it up) but will fix later 86SELECT '#1125e' As ticket, pprint_addy(addy), addy.* FROM normalize_address('I-90,Boston, MA') As addy; 87--broke this one too 88SELECT '#1125f' As ticket, pprint_addy(addy), addy.* FROM normalize_address('I 90,Boston, MA') As addy; 89 90-- location with prefixes getting caught in post prefix 91SELECT '#1310a' As ticket, pprint_addy(addy), addy.* FROM normalize_address('1110 W CAPITOL AVE, WEST SACRAMENTO, CA') As addy; 92 93-- #1614 County Rd 94SELECT '#1614a' As ticket, pprint_addy(addy), addy.* FROM normalize_address('8435 COUNTY RD 20 SE, ROCHESTER, MN 55904') As addy; 95SELECT '#1614b' As ticket, pprint_addy(addy), addy.* FROM normalize_address('3208 U.S. 52, Rochester, MN 55901') As addy; 96 97--internal address prefix sometimes get caught in post dir 98SELECT '#1108a' As ticket, pprint_addy(addy), addy.* FROM normalize_address('529 Main Street, Suite 201, Boston, MA 02129') AS addy; 99--\timing 100