From ecb0988b2b160054fef62485ae34956511514f9c Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Thu, 10 Jan 2019 10:14:30 -0500 Subject: [PATCH] better usps suffix handling Signed-off-by: Jason Etheridge --- sql/base/base.sql | 1074 ++++++++++++++++++++++++++-------------------------- 1 files changed, 537 insertions(+), 537 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index af6340a..3dffd83 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -648,552 +648,552 @@ CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEX ]; $$ LANGUAGE PLPERLU STABLE; --- FIXME: there's probably a more efficient way to implement this +DROP TABLE IF EXISTS migration_tools.usps_suffixes; +CREATE UNLOGGED TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT ); +INSERT INTO migration_tools.usps_suffixes VALUES + ('ALLEE','ALY'), + ('ALLEY','ALY'), + ('ALLY','ALY'), + ('ALY','ALY'), + ('ANEX','ANX'), + ('ANNEX','ANX'), + ('ANNX','ANX'), + ('ANX','ANX'), + ('ARCADE','ARC'), + ('ARC','ARC'), + ('AV','AVE'), + ('AVE','AVE'), + ('AVEN','AVE'), + ('AVENU','AVE'), + ('AVENUE','AVE'), + ('AVN','AVE'), + ('AVNUE','AVE'), + ('BAYOO','BYU'), + ('BAYOU','BYU'), + ('BCH','BCH'), + ('BEACH','BCH'), + ('BEND','BND'), + ('BLF','BLF'), + ('BLUF','BLF'), + ('BLUFF','BLF'), + ('BLUFFS','BLFS'), + ('BLVD','BLVD'), + ('BND','BND'), + ('BOT','BTM'), + ('BOTTM','BTM'), + ('BOTTOM','BTM'), + ('BOUL','BLVD'), + ('BOULEVARD','BLVD'), + ('BOULV','BLVD'), + ('BRANCH','BR'), + ('BR','BR'), + ('BRDGE','BRG'), + ('BRG','BRG'), + ('BRIDGE','BRG'), + ('BRK','BRK'), + ('BRNCH','BR'), + ('BROOK','BRK'), + ('BROOKS','BRKS'), + ('BTM','BTM'), + ('BURG','BG'), + ('BURGS','BGS'), + ('BYPA','BYP'), + ('BYPAS','BYP'), + ('BYPASS','BYP'), + ('BYP','BYP'), + ('BYPS','BYP'), + ('CAMP','CP'), + ('CANYN','CYN'), + ('CANYON','CYN'), + ('CAPE','CPE'), + ('CAUSEWAY','CSWY'), + ('CAUSWAY','CSWY'), + ('CEN','CTR'), + ('CENT','CTR'), + ('CENTER','CTR'), + ('CENTERS','CTRS'), + ('CENTR','CTR'), + ('CENTRE','CTR'), + ('CIRC','CIR'), + ('CIR','CIR'), + ('CIRCL','CIR'), + ('CIRCLE','CIR'), + ('CIRCLES','CIRS'), + ('CK','CRK'), + ('CLB','CLB'), + ('CLF','CLF'), + ('CLFS','CLFS'), + ('CLIFF','CLF'), + ('CLIFFS','CLFS'), + ('CLUB','CLB'), + ('CMP','CP'), + ('CNTER','CTR'), + ('CNTR','CTR'), + ('CNYN','CYN'), + ('COMMON','CMN'), + ('COR','COR'), + ('CORNER','COR'), + ('CORNERS','CORS'), + ('CORS','CORS'), + ('COURSE','CRSE'), + ('COURT','CT'), + ('COURTS','CTS'), + ('COVE','CV'), + ('COVES','CVS'), + ('CP','CP'), + ('CPE','CPE'), + ('CRCL','CIR'), + ('CRCLE','CIR'), + ('CR','CRK'), + ('CRECENT','CRES'), + ('CREEK','CRK'), + ('CRESCENT','CRES'), + ('CRES','CRES'), + ('CRESENT','CRES'), + ('CREST','CRST'), + ('CRK','CRK'), + ('CROSSING','XING'), + ('CROSSROAD','XRD'), + ('CRSCNT','CRES'), + ('CRSE','CRSE'), + ('CRSENT','CRES'), + ('CRSNT','CRES'), + ('CRSSING','XING'), + ('CRSSNG','XING'), + ('CRT','CT'), + ('CSWY','CSWY'), + ('CT','CT'), + ('CTR','CTR'), + ('CTS','CTS'), + ('CURVE','CURV'), + ('CV','CV'), + ('CYN','CYN'), + ('DALE','DL'), + ('DAM','DM'), + ('DIV','DV'), + ('DIVIDE','DV'), + ('DL','DL'), + ('DM','DM'), + ('DR','DR'), + ('DRIV','DR'), + ('DRIVE','DR'), + ('DRIVES','DRS'), + ('DRV','DR'), + ('DVD','DV'), + ('DV','DV'), + ('ESTATE','EST'), + ('ESTATES','ESTS'), + ('EST','EST'), + ('ESTS','ESTS'), + ('EXP','EXPY'), + ('EXPRESS','EXPY'), + ('EXPRESSWAY','EXPY'), + ('EXPR','EXPY'), + ('EXPW','EXPY'), + ('EXPY','EXPY'), + ('EXTENSION','EXT'), + ('EXTENSIONS','EXTS'), + ('EXT','EXT'), + ('EXTN','EXT'), + ('EXTNSN','EXT'), + ('EXTS','EXTS'), + ('FALL','FALL'), + ('FALLS','FLS'), + ('FERRY','FRY'), + ('FIELD','FLD'), + ('FIELDS','FLDS'), + ('FLAT','FLT'), + ('FLATS','FLTS'), + ('FLD','FLD'), + ('FLDS','FLDS'), + ('FLS','FLS'), + ('FLT','FLT'), + ('FLTS','FLTS'), + ('FORD','FRD'), + ('FORDS','FRDS'), + ('FOREST','FRST'), + ('FORESTS','FRST'), + ('FORGE','FRG'), + ('FORGES','FRGS'), + ('FORG','FRG'), + ('FORK','FRK'), + ('FORKS','FRKS'), + ('FORT','FT'), + ('FRD','FRD'), + ('FREEWAY','FWY'), + ('FREEWY','FWY'), + ('FRG','FRG'), + ('FRK','FRK'), + ('FRKS','FRKS'), + ('FRRY','FRY'), + ('FRST','FRST'), + ('FRT','FT'), + ('FRWAY','FWY'), + ('FRWY','FWY'), + ('FRY','FRY'), + ('FT','FT'), + ('FWY','FWY'), + ('GARDEN','GDN'), + ('GARDENS','GDNS'), + ('GARDN','GDN'), + ('GATEWAY','GTWY'), + ('GATEWY','GTWY'), + ('GATWAY','GTWY'), + ('GDN','GDN'), + ('GDNS','GDNS'), + ('GLEN','GLN'), + ('GLENS','GLNS'), + ('GLN','GLN'), + ('GRDEN','GDN'), + ('GRDN','GDN'), + ('GRDNS','GDNS'), + ('GREEN','GRN'), + ('GREENS','GRNS'), + ('GRN','GRN'), + ('GROVE','GRV'), + ('GROVES','GRVS'), + ('GROV','GRV'), + ('GRV','GRV'), + ('GTWAY','GTWY'), + ('GTWY','GTWY'), + ('HARB','HBR'), + ('HARBOR','HBR'), + ('HARBORS','HBRS'), + ('HARBR','HBR'), + ('HAVEN','HVN'), + ('HAVN','HVN'), + ('HBR','HBR'), + ('HEIGHT','HTS'), + ('HEIGHTS','HTS'), + ('HGTS','HTS'), + ('HIGHWAY','HWY'), + ('HIGHWY','HWY'), + ('HILL','HL'), + ('HILLS','HLS'), + ('HIWAY','HWY'), + ('HIWY','HWY'), + ('HL','HL'), + ('HLLW','HOLW'), + ('HLS','HLS'), + ('HOLLOW','HOLW'), + ('HOLLOWS','HOLW'), + ('HOLW','HOLW'), + ('HOLWS','HOLW'), + ('HRBOR','HBR'), + ('HT','HTS'), + ('HTS','HTS'), + ('HVN','HVN'), + ('HWAY','HWY'), + ('HWY','HWY'), + ('INLET','INLT'), + ('INLT','INLT'), + ('IS','IS'), + ('ISLAND','IS'), + ('ISLANDS','ISS'), + ('ISLANDS','SLNDS'), + ('ISLANDS','SS'), + ('ISLE','ISLE'), + ('ISLES','ISLE'), + ('ISLND','IS'), + ('I','SLNDS'), + ('ISS','ISS'), + ('JCTION','JCT'), + ('JCT','JCT'), + ('JCTN','JCT'), + ('JCTNS','JCTS'), + ('JCTS','JCTS'), + ('JUNCTION','JCT'), + ('JUNCTIONS','JCTS'), + ('JUNCTN','JCT'), + ('JUNCTON','JCT'), + ('KEY','KY'), + ('KEYS','KYS'), + ('KNL','KNL'), + ('KNLS','KNLS'), + ('KNOL','KNL'), + ('KNOLL','KNL'), + ('KNOLLS','KNLS'), + ('KY','KY'), + ('KYS','KYS'), + ('LAKE','LK'), + ('LAKES','LKS'), + ('LA','LN'), + ('LANDING','LNDG'), + ('LAND','LAND'), + ('LANE','LN'), + ('LANES','LN'), + ('LCK','LCK'), + ('LCKS','LCKS'), + ('LDGE','LDG'), + ('LDG','LDG'), + ('LF','LF'), + ('LGT','LGT'), + ('LIGHT','LGT'), + ('LIGHTS','LGTS'), + ('LK','LK'), + ('LKS','LKS'), + ('LNDG','LNDG'), + ('LNDNG','LNDG'), + ('LN','LN'), + ('LOAF','LF'), + ('LOCK','LCK'), + ('LOCKS','LCKS'), + ('LODGE','LDG'), + ('LODG','LDG'), + ('LOOP','LOOP'), + ('LOOPS','LOOP'), + ('MALL','MALL'), + ('MANOR','MNR'), + ('MANORS','MNRS'), + ('MDW','MDW'), + ('MDWS','MDWS'), + ('MEADOW','MDW'), + ('MEADOWS','MDWS'), + ('MEDOWS','MDWS'), + ('MEWS','MEWS'), + ('MILL','ML'), + ('MILLS','MLS'), + ('MISSION','MSN'), + ('MISSN','MSN'), + ('ML','ML'), + ('MLS','MLS'), + ('MNR','MNR'), + ('MNRS','MNRS'), + ('MNTAIN','MTN'), + ('MNT','MT'), + ('MNTN','MTN'), + ('MNTNS','MTNS'), + ('MOTORWAY','MTWY'), + ('MOUNTAIN','MTN'), + ('MOUNTAINS','MTNS'), + ('MOUNTIN','MTN'), + ('MOUNT','MT'), + ('MSN','MSN'), + ('MSSN','MSN'), + ('MTIN','MTN'), + ('MT','MT'), + ('MTN','MTN'), + ('NCK','NCK'), + ('NECK','NCK'), + ('ORCHARD','ORCH'), + ('ORCH','ORCH'), + ('ORCHRD','ORCH'), + ('OVAL','OVAL'), + ('OVERPASS','OPAS'), + ('OVL','OVAL'), + ('PARK','PARK'), + ('PARKS','PARK'), + ('PARKWAY','PKWY'), + ('PARKWAYS','PKWY'), + ('PARKWY','PKWY'), + ('PASSAGE','PSGE'), + ('PASS','PASS'), + ('PATH','PATH'), + ('PATHS','PATH'), + ('PIKE','PIKE'), + ('PIKES','PIKE'), + ('PINE','PNE'), + ('PINES','PNES'), + ('PK','PARK'), + ('PKWAY','PKWY'), + ('PKWY','PKWY'), + ('PKWYS','PKWY'), + ('PKY','PKWY'), + ('PLACE','PL'), + ('PLAINES','PLNS'), + ('PLAIN','PLN'), + ('PLAINS','PLNS'), + ('PLAZA','PLZ'), + ('PLN','PLN'), + ('PLNS','PLNS'), + ('PL','PL'), + ('PLZA','PLZ'), + ('PLZ','PLZ'), + ('PNES','PNES'), + ('POINT','PT'), + ('POINTS','PTS'), + ('PORT','PRT'), + ('PORTS','PRTS'), + ('PRAIRIE','PR'), + ('PRARIE','PR'), + ('PRK','PARK'), + ('PR','PR'), + ('PRR','PR'), + ('PRT','PRT'), + ('PRTS','PRTS'), + ('PT','PT'), + ('PTS','PTS'), + ('RADIAL','RADL'), + ('RADIEL','RADL'), + ('RADL','RADL'), + ('RAD','RADL'), + ('RAMP','RAMP'), + ('RANCHES','RNCH'), + ('RANCH','RNCH'), + ('RAPID','RPD'), + ('RAPIDS','RPDS'), + ('RDGE','RDG'), + ('RDG','RDG'), + ('RDGS','RDGS'), + ('RD','RD'), + ('RDS','RDS'), + ('REST','RST'), + ('RIDGE','RDG'), + ('RIDGES','RDGS'), + ('RIVER','RIV'), + ('RIV','RIV'), + ('RIVR','RIV'), + ('RNCH','RNCH'), + ('RNCHS','RNCH'), + ('ROAD','RD'), + ('ROADS','RDS'), + ('ROUTE','RTE'), + ('ROW','ROW'), + ('RPD','RPD'), + ('RPDS','RPDS'), + ('RST','RST'), + ('RUE','RUE'), + ('RUN','RUN'), + ('RVR','RIV'), + ('SHL','SHL'), + ('SHLS','SHLS'), + ('SHOAL','SHL'), + ('SHOALS','SHLS'), + ('SHOAR','SHR'), + ('SHOARS','SHRS'), + ('SHORE','SHR'), + ('SHORES','SHRS'), + ('SHR','SHR'), + ('SHRS','SHRS'), + ('SKYWAY','SKWY'), + ('SMT','SMT'), + ('SPG','SPG'), + ('SPGS','SPGS'), + ('SPNG','SPG'), + ('SPNGS','SPGS'), + ('SPRING','SPG'), + ('SPRINGS','SPGS'), + ('SPRNG','SPG'), + ('SPRNGS','SPGS'), + ('SPUR','SPUR'), + ('SPURS','SPUR'), + ('SQRE','SQ'), + ('SQR','SQ'), + ('SQRS','SQS'), + ('SQ','SQ'), + ('SQUARE','SQ'), + ('SQUARES','SQS'), + ('SQU','SQ'), + ('STA','STA'), + ('STATION','STA'), + ('STATN','STA'), + ('STN','STA'), + ('STRA','STRA'), + ('STRAVEN','STRA'), + ('STRAVENUE','STRA'), + ('STRAVE','STRA'), + ('STRAVN','STRA'), + ('STRAV','STRA'), + ('STREAM','STRM'), + ('STREETS','STS'), + ('STREET','ST'), + ('STREME','STRM'), + ('STRM','STRM'), + ('STR','ST'), + ('STRT','ST'), + ('STRVN','STRA'), + ('STRVNUE','STRA'), + ('ST','ST'), + ('SUMIT','SMT'), + ('SUMITT','SMT'), + ('SUMMIT','SMT'), + ('TERRACE','TER'), + ('TERR','TER'), + ('TER','TER'), + ('THROUGHWAY','TRWY'), + ('TPKE','TPKE'), + ('TPK','TPKE'), + ('TRACES','TRCE'), + ('TRACE','TRCE'), + ('TRACKS','TRAK'), + ('TRACK','TRAK'), + ('TRAFFICWAY','TRFY'), + ('TRAILS','TRL'), + ('TRAIL','TRL'), + ('TRAK','TRAK'), + ('TRCE','TRCE'), + ('TRFY','TRFY'), + ('TRKS','TRAK'), + ('TRK','TRAK'), + ('TRLS','TRL'), + ('TRL','TRL'), + ('TRNPK','TPKE'), + ('TRPK','TPKE'), + ('TR','TRL'), + ('TUNEL','TUNL'), + ('TUNLS','TUNL'), + ('TUNL','TUNL'), + ('TUNNELS','TUNL'), + ('TUNNEL','TUNL'), + ('TUNNL','TUNL'), + ('TURNPIKE','TPKE'), + ('TURNPK','TPKE'), + ('UNDERPASS','UPAS'), + ('UNIONS','UNS'), + ('UNION','UN'), + ('UN','UN'), + ('VALLEYS','VLYS'), + ('VALLEY','VLY'), + ('VALLY','VLY'), + ('VDCT','IA'), + ('VIADCT','VIA'), + ('VIADUCT','IA'), + ('VIADUCT','VIA'), + ('VIA','VIA'), + ('VIEWS','VWS'), + ('VIEW','VW'), + ('VILLAGES','VLGS'), + ('VILLAGE','VLG'), + ('VILLAG','VLG'), + ('VILLE','VL'), + ('VILLG','VLG'), + ('VILLIAGE','VLG'), + ('VILL','VLG'), + ('VISTA','VIS'), + ('VIST','VIS'), + ('VIS','VIS'), + ('VLGS','VLGS'), + ('VLG','VLG'), + ('VLLY','VLY'), + ('VL','VL'), + ('VLYS','VLYS'), + ('VLY','VLY'), + ('VSTA','VIS'), + ('VST','VIS'), + ('VWS','VWS'), + ('VW','VW'), + ('WALKS','WALK'), + ('WALK','WALK'), + ('WALL','WALL'), + ('WAYS','WAYS'), + ('WAY','WAY'), + ('WELLS','WLS'), + ('WELL','WL'), + ('WLS','WLS'), + ('WY','WAY'), + ('XING','XING'); + CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$ DECLARE street1 TEXT := $1; _r RECORD; BEGIN - DROP TABLE IF EXISTS mig_usps_suffixes; - CREATE TEMP TABLE mig_usps_suffixes ( suffix_from TEXT, suffix_to TEXT ); - INSERT INTO mig_usps_suffixes VALUES - ('ALLEE','ALY'), - ('ALLEY','ALY'), - ('ALLY','ALY'), - ('ALY','ALY'), - ('ANEX','ANX'), - ('ANNEX','ANX'), - ('ANNX','ANX'), - ('ANX','ANX'), - ('ARCADE','ARC'), - ('ARC','ARC'), - ('AV','AVE'), - ('AVE','AVE'), - ('AVEN','AVE'), - ('AVENU','AVE'), - ('AVENUE','AVE'), - ('AVN','AVE'), - ('AVNUE','AVE'), - ('BAYOO','BYU'), - ('BAYOU','BYU'), - ('BCH','BCH'), - ('BEACH','BCH'), - ('BEND','BND'), - ('BLF','BLF'), - ('BLUF','BLF'), - ('BLUFF','BLF'), - ('BLUFFS','BLFS'), - ('BLVD','BLVD'), - ('BND','BND'), - ('BOT','BTM'), - ('BOTTM','BTM'), - ('BOTTOM','BTM'), - ('BOUL','BLVD'), - ('BOULEVARD','BLVD'), - ('BOULV','BLVD'), - ('BRANCH','BR'), - ('BR','BR'), - ('BRDGE','BRG'), - ('BRG','BRG'), - ('BRIDGE','BRG'), - ('BRK','BRK'), - ('BRNCH','BR'), - ('BROOK','BRK'), - ('BROOKS','BRKS'), - ('BTM','BTM'), - ('BURG','BG'), - ('BURGS','BGS'), - ('BYPA','BYP'), - ('BYPAS','BYP'), - ('BYPASS','BYP'), - ('BYP','BYP'), - ('BYPS','BYP'), - ('CAMP','CP'), - ('CANYN','CYN'), - ('CANYON','CYN'), - ('CAPE','CPE'), - ('CAUSEWAY','CSWY'), - ('CAUSWAY','CSWY'), - ('CEN','CTR'), - ('CENT','CTR'), - ('CENTER','CTR'), - ('CENTERS','CTRS'), - ('CENTR','CTR'), - ('CENTRE','CTR'), - ('CIRC','CIR'), - ('CIR','CIR'), - ('CIRCL','CIR'), - ('CIRCLE','CIR'), - ('CIRCLES','CIRS'), - ('CK','CRK'), - ('CLB','CLB'), - ('CLF','CLF'), - ('CLFS','CLFS'), - ('CLIFF','CLF'), - ('CLIFFS','CLFS'), - ('CLUB','CLB'), - ('CMP','CP'), - ('CNTER','CTR'), - ('CNTR','CTR'), - ('CNYN','CYN'), - ('COMMON','CMN'), - ('COR','COR'), - ('CORNER','COR'), - ('CORNERS','CORS'), - ('CORS','CORS'), - ('COURSE','CRSE'), - ('COURT','CT'), - ('COURTS','CTS'), - ('COVE','CV'), - ('COVES','CVS'), - ('CP','CP'), - ('CPE','CPE'), - ('CRCL','CIR'), - ('CRCLE','CIR'), - ('CR','CRK'), - ('CRECENT','CRES'), - ('CREEK','CRK'), - ('CRESCENT','CRES'), - ('CRES','CRES'), - ('CRESENT','CRES'), - ('CREST','CRST'), - ('CRK','CRK'), - ('CROSSING','XING'), - ('CROSSROAD','XRD'), - ('CRSCNT','CRES'), - ('CRSE','CRSE'), - ('CRSENT','CRES'), - ('CRSNT','CRES'), - ('CRSSING','XING'), - ('CRSSNG','XING'), - ('CRT','CT'), - ('CSWY','CSWY'), - ('CT','CT'), - ('CTR','CTR'), - ('CTS','CTS'), - ('CURVE','CURV'), - ('CV','CV'), - ('CYN','CYN'), - ('DALE','DL'), - ('DAM','DM'), - ('DIV','DV'), - ('DIVIDE','DV'), - ('DL','DL'), - ('DM','DM'), - ('DR','DR'), - ('DRIV','DR'), - ('DRIVE','DR'), - ('DRIVES','DRS'), - ('DRV','DR'), - ('DVD','DV'), - ('DV','DV'), - ('ESTATE','EST'), - ('ESTATES','ESTS'), - ('EST','EST'), - ('ESTS','ESTS'), - ('EXP','EXPY'), - ('EXPRESS','EXPY'), - ('EXPRESSWAY','EXPY'), - ('EXPR','EXPY'), - ('EXPW','EXPY'), - ('EXPY','EXPY'), - ('EXTENSION','EXT'), - ('EXTENSIONS','EXTS'), - ('EXT','EXT'), - ('EXTN','EXT'), - ('EXTNSN','EXT'), - ('EXTS','EXTS'), - ('FALL','FALL'), - ('FALLS','FLS'), - ('FERRY','FRY'), - ('FIELD','FLD'), - ('FIELDS','FLDS'), - ('FLAT','FLT'), - ('FLATS','FLTS'), - ('FLD','FLD'), - ('FLDS','FLDS'), - ('FLS','FLS'), - ('FLT','FLT'), - ('FLTS','FLTS'), - ('FORD','FRD'), - ('FORDS','FRDS'), - ('FOREST','FRST'), - ('FORESTS','FRST'), - ('FORGE','FRG'), - ('FORGES','FRGS'), - ('FORG','FRG'), - ('FORK','FRK'), - ('FORKS','FRKS'), - ('FORT','FT'), - ('FRD','FRD'), - ('FREEWAY','FWY'), - ('FREEWY','FWY'), - ('FRG','FRG'), - ('FRK','FRK'), - ('FRKS','FRKS'), - ('FRRY','FRY'), - ('FRST','FRST'), - ('FRT','FT'), - ('FRWAY','FWY'), - ('FRWY','FWY'), - ('FRY','FRY'), - ('FT','FT'), - ('FWY','FWY'), - ('GARDEN','GDN'), - ('GARDENS','GDNS'), - ('GARDN','GDN'), - ('GATEWAY','GTWY'), - ('GATEWY','GTWY'), - ('GATWAY','GTWY'), - ('GDN','GDN'), - ('GDNS','GDNS'), - ('GLEN','GLN'), - ('GLENS','GLNS'), - ('GLN','GLN'), - ('GRDEN','GDN'), - ('GRDN','GDN'), - ('GRDNS','GDNS'), - ('GREEN','GRN'), - ('GREENS','GRNS'), - ('GRN','GRN'), - ('GROVE','GRV'), - ('GROVES','GRVS'), - ('GROV','GRV'), - ('GRV','GRV'), - ('GTWAY','GTWY'), - ('GTWY','GTWY'), - ('HARB','HBR'), - ('HARBOR','HBR'), - ('HARBORS','HBRS'), - ('HARBR','HBR'), - ('HAVEN','HVN'), - ('HAVN','HVN'), - ('HBR','HBR'), - ('HEIGHT','HTS'), - ('HEIGHTS','HTS'), - ('HGTS','HTS'), - ('HIGHWAY','HWY'), - ('HIGHWY','HWY'), - ('HILL','HL'), - ('HILLS','HLS'), - ('HIWAY','HWY'), - ('HIWY','HWY'), - ('HL','HL'), - ('HLLW','HOLW'), - ('HLS','HLS'), - ('HOLLOW','HOLW'), - ('HOLLOWS','HOLW'), - ('HOLW','HOLW'), - ('HOLWS','HOLW'), - ('HRBOR','HBR'), - ('HT','HTS'), - ('HTS','HTS'), - ('HVN','HVN'), - ('HWAY','HWY'), - ('HWY','HWY'), - ('INLET','INLT'), - ('INLT','INLT'), - ('IS','IS'), - ('ISLAND','IS'), - ('ISLANDS','ISS'), - ('ISLANDS','SLNDS'), - ('ISLANDS','SS'), - ('ISLE','ISLE'), - ('ISLES','ISLE'), - ('ISLND','IS'), - ('I','SLNDS'), - ('ISS','ISS'), - ('JCTION','JCT'), - ('JCT','JCT'), - ('JCTN','JCT'), - ('JCTNS','JCTS'), - ('JCTS','JCTS'), - ('JUNCTION','JCT'), - ('JUNCTIONS','JCTS'), - ('JUNCTN','JCT'), - ('JUNCTON','JCT'), - ('KEY','KY'), - ('KEYS','KYS'), - ('KNL','KNL'), - ('KNLS','KNLS'), - ('KNOL','KNL'), - ('KNOLL','KNL'), - ('KNOLLS','KNLS'), - ('KY','KY'), - ('KYS','KYS'), - ('LAKE','LK'), - ('LAKES','LKS'), - ('LA','LN'), - ('LANDING','LNDG'), - ('LAND','LAND'), - ('LANE','LN'), - ('LANES','LN'), - ('LCK','LCK'), - ('LCKS','LCKS'), - ('LDGE','LDG'), - ('LDG','LDG'), - ('LF','LF'), - ('LGT','LGT'), - ('LIGHT','LGT'), - ('LIGHTS','LGTS'), - ('LK','LK'), - ('LKS','LKS'), - ('LNDG','LNDG'), - ('LNDNG','LNDG'), - ('LN','LN'), - ('LOAF','LF'), - ('LOCK','LCK'), - ('LOCKS','LCKS'), - ('LODGE','LDG'), - ('LODG','LDG'), - ('LOOP','LOOP'), - ('LOOPS','LOOP'), - ('MALL','MALL'), - ('MANOR','MNR'), - ('MANORS','MNRS'), - ('MDW','MDW'), - ('MDWS','MDWS'), - ('MEADOW','MDW'), - ('MEADOWS','MDWS'), - ('MEDOWS','MDWS'), - ('MEWS','MEWS'), - ('MILL','ML'), - ('MILLS','MLS'), - ('MISSION','MSN'), - ('MISSN','MSN'), - ('ML','ML'), - ('MLS','MLS'), - ('MNR','MNR'), - ('MNRS','MNRS'), - ('MNTAIN','MTN'), - ('MNT','MT'), - ('MNTN','MTN'), - ('MNTNS','MTNS'), - ('MOTORWAY','MTWY'), - ('MOUNTAIN','MTN'), - ('MOUNTAINS','MTNS'), - ('MOUNTIN','MTN'), - ('MOUNT','MT'), - ('MSN','MSN'), - ('MSSN','MSN'), - ('MTIN','MTN'), - ('MT','MT'), - ('MTN','MTN'), - ('NCK','NCK'), - ('NECK','NCK'), - ('ORCHARD','ORCH'), - ('ORCH','ORCH'), - ('ORCHRD','ORCH'), - ('OVAL','OVAL'), - ('OVERPASS','OPAS'), - ('OVL','OVAL'), - ('PARK','PARK'), - ('PARKS','PARK'), - ('PARKWAY','PKWY'), - ('PARKWAYS','PKWY'), - ('PARKWY','PKWY'), - ('PASSAGE','PSGE'), - ('PASS','PASS'), - ('PATH','PATH'), - ('PATHS','PATH'), - ('PIKE','PIKE'), - ('PIKES','PIKE'), - ('PINE','PNE'), - ('PINES','PNES'), - ('PK','PARK'), - ('PKWAY','PKWY'), - ('PKWY','PKWY'), - ('PKWYS','PKWY'), - ('PKY','PKWY'), - ('PLACE','PL'), - ('PLAINES','PLNS'), - ('PLAIN','PLN'), - ('PLAINS','PLNS'), - ('PLAZA','PLZ'), - ('PLN','PLN'), - ('PLNS','PLNS'), - ('PL','PL'), - ('PLZA','PLZ'), - ('PLZ','PLZ'), - ('PNES','PNES'), - ('POINT','PT'), - ('POINTS','PTS'), - ('PORT','PRT'), - ('PORTS','PRTS'), - ('PRAIRIE','PR'), - ('PRARIE','PR'), - ('PRK','PARK'), - ('PR','PR'), - ('PRR','PR'), - ('PRT','PRT'), - ('PRTS','PRTS'), - ('PT','PT'), - ('PTS','PTS'), - ('RADIAL','RADL'), - ('RADIEL','RADL'), - ('RADL','RADL'), - ('RAD','RADL'), - ('RAMP','RAMP'), - ('RANCHES','RNCH'), - ('RANCH','RNCH'), - ('RAPID','RPD'), - ('RAPIDS','RPDS'), - ('RDGE','RDG'), - ('RDG','RDG'), - ('RDGS','RDGS'), - ('RD','RD'), - ('RDS','RDS'), - ('REST','RST'), - ('RIDGE','RDG'), - ('RIDGES','RDGS'), - ('RIVER','RIV'), - ('RIV','RIV'), - ('RIVR','RIV'), - ('RNCH','RNCH'), - ('RNCHS','RNCH'), - ('ROAD','RD'), - ('ROADS','RDS'), - ('ROUTE','RTE'), - ('ROW','ROW'), - ('RPD','RPD'), - ('RPDS','RPDS'), - ('RST','RST'), - ('RUE','RUE'), - ('RUN','RUN'), - ('RVR','RIV'), - ('SHL','SHL'), - ('SHLS','SHLS'), - ('SHOAL','SHL'), - ('SHOALS','SHLS'), - ('SHOAR','SHR'), - ('SHOARS','SHRS'), - ('SHORE','SHR'), - ('SHORES','SHRS'), - ('SHR','SHR'), - ('SHRS','SHRS'), - ('SKYWAY','SKWY'), - ('SMT','SMT'), - ('SPG','SPG'), - ('SPGS','SPGS'), - ('SPNG','SPG'), - ('SPNGS','SPGS'), - ('SPRING','SPG'), - ('SPRINGS','SPGS'), - ('SPRNG','SPG'), - ('SPRNGS','SPGS'), - ('SPUR','SPUR'), - ('SPURS','SPUR'), - ('SQRE','SQ'), - ('SQR','SQ'), - ('SQRS','SQS'), - ('SQ','SQ'), - ('SQUARE','SQ'), - ('SQUARES','SQS'), - ('SQU','SQ'), - ('STA','STA'), - ('STATION','STA'), - ('STATN','STA'), - ('STN','STA'), - ('STRA','STRA'), - ('STRAVEN','STRA'), - ('STRAVENUE','STRA'), - ('STRAVE','STRA'), - ('STRAVN','STRA'), - ('STRAV','STRA'), - ('STREAM','STRM'), - ('STREETS','STS'), - ('STREET','ST'), - ('STREME','STRM'), - ('STRM','STRM'), - ('STR','ST'), - ('STRT','ST'), - ('STRVN','STRA'), - ('STRVNUE','STRA'), - ('ST','ST'), - ('SUMIT','SMT'), - ('SUMITT','SMT'), - ('SUMMIT','SMT'), - ('TERRACE','TER'), - ('TERR','TER'), - ('TER','TER'), - ('THROUGHWAY','TRWY'), - ('TPKE','TPKE'), - ('TPK','TPKE'), - ('TRACES','TRCE'), - ('TRACE','TRCE'), - ('TRACKS','TRAK'), - ('TRACK','TRAK'), - ('TRAFFICWAY','TRFY'), - ('TRAILS','TRL'), - ('TRAIL','TRL'), - ('TRAK','TRAK'), - ('TRCE','TRCE'), - ('TRFY','TRFY'), - ('TRKS','TRAK'), - ('TRK','TRAK'), - ('TRLS','TRL'), - ('TRL','TRL'), - ('TRNPK','TPKE'), - ('TRPK','TPKE'), - ('TR','TRL'), - ('TUNEL','TUNL'), - ('TUNLS','TUNL'), - ('TUNL','TUNL'), - ('TUNNELS','TUNL'), - ('TUNNEL','TUNL'), - ('TUNNL','TUNL'), - ('TURNPIKE','TPKE'), - ('TURNPK','TPKE'), - ('UNDERPASS','UPAS'), - ('UNIONS','UNS'), - ('UNION','UN'), - ('UN','UN'), - ('VALLEYS','VLYS'), - ('VALLEY','VLY'), - ('VALLY','VLY'), - ('VDCT','IA'), - ('VIADCT','VIA'), - ('VIADUCT','IA'), - ('VIADUCT','VIA'), - ('VIA','VIA'), - ('VIEWS','VWS'), - ('VIEW','VW'), - ('VILLAGES','VLGS'), - ('VILLAGE','VLG'), - ('VILLAG','VLG'), - ('VILLE','VL'), - ('VILLG','VLG'), - ('VILLIAGE','VLG'), - ('VILL','VLG'), - ('VISTA','VIS'), - ('VIST','VIS'), - ('VIS','VIS'), - ('VLGS','VLGS'), - ('VLG','VLG'), - ('VLLY','VLY'), - ('VL','VL'), - ('VLYS','VLYS'), - ('VLY','VLY'), - ('VSTA','VIS'), - ('VST','VIS'), - ('VWS','VWS'), - ('VW','VW'), - ('WALKS','WALK'), - ('WALK','WALK'), - ('WALL','WALL'), - ('WAYS','WAYS'), - ('WAY','WAY'), - ('WELLS','WLS'), - ('WELL','WL'), - ('WLS','WLS'), - ('WY','WAY'), - ('XING','XING'); - FOR _r IN (SELECT * FROM mig_usps_suffixes) LOOP + FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP street1 := REGEXP_REPLACE( street1, ' ' || _r.suffix_from || '$', ' ' || _r.suffix_to, 'i'); END LOOP; RETURN street1; END; -$$ LANGUAGE PLPGSQL STRICT VOLATILE; +$$ LANGUAGE PLPGSQL STRICT STABLE; CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$ DECLARE -- 1.7.2.5