From: Jason Etheridge Date: Wed, 9 Jan 2019 19:29:43 +0000 (-0500) Subject: migration_tools.normalize_address_suffix X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=da0881a95d08d653877723aad92460935cd942e6 migration_tools.normalize_address_suffix Signed-off-by: Jason Etheridge --- diff --git a/sql/base/base.sql b/sql/base/base.sql index 8d39a96..b435a66 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -648,6 +648,553 @@ 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 +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 + street1 := REGEXP_REPLACE( street1, ' ' || _r.suffix_from || '$', ' ' || _r.suffix_to, 'i'); + END LOOP; + RETURN street1; + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$ DECLARE n TEXT := o;