sql/base/05-addresses.sql

   1 CREATE OR REPLACE FUNCTION msgration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
   2     DECLARE
   3         city_state_zip TEXT := $1;
   4         city TEXT := '';
   5         state TEXT := '';
   6         zip TEXT := '';
   7     BEGIN
   8         zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END;
   9         city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
  10         IF city_state_zip ~ ',' THEN
  11             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
  12             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
  13         ELSE
  14             IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
  15                 state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
  16                 city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
  17             ELSE
  18                 IF city_state_zip ~ E'^\\S+$'  THEN
  19                     city := city_state_zip;
  20                     state := 'N/A';
  21                 ELSE
  22                     state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
  23                     city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
  24                 END IF;
  25             END IF;
  26         END IF;
  27         RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ];
  28     END;
  29 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  30
  31 -- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
  32 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
  33     DECLARE
  34         fullstring TEXT := $1;
  35         address1 TEXT := '';
  36         address2 TEXT := '';
  37         scratch1 TEXT := '';
  38         scratch2 TEXT := '';
  39         city TEXT := '';
  40         state TEXT := '';
  41         zip TEXT := '';
  42     BEGIN
  43         zip := CASE
  44             WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
  45             THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
  46             ELSE ''
  47         END;
  48         fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
  49
  50         IF fullstring ~ ',' THEN
  51             state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
  52             scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
  53         ELSE
  54             IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
  55                 state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
  56                 scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
  57             ELSE
  58                 IF fullstring ~ E'^\\S+$'  THEN
  59                     scratch1 := fullstring;
  60                     state := 'N/A';
  61                 ELSE
  62                     state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
  63                     scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
  64                 END IF;
  65             END IF;
  66         END IF;
  67
  68         IF scratch1 ~ '[\$]' THEN
  69             scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
  70             city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
  71         ELSE
  72             IF scratch1 ~ '\s' THEN
  73                 scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
  74                 city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
  75             ELSE
  76                 scratch2 := 'N/A';
  77                 city := scratch1;
  78             END IF;
  79         END IF;
  80
  81         IF scratch2 ~ '^\d' THEN
  82             address1 := scratch2;
  83             address2 := '';
  84         ELSE
  85             address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
  86             address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
  87         END IF;
  88
  89         RETURN ARRAY[
  90              TRIM(BOTH ' ' FROM address1)
  91             ,TRIM(BOTH ' ' FROM address2)
  92             ,TRIM(BOTH ' ' FROM city)
  93             ,TRIM(BOTH ' ' FROM state)
  94             ,TRIM(BOTH ' ' FROM zip)
  95         ];
  96     END;
  97 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
  98
  99 CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
 100     my ($address) = @_;
 101
 102     use Geo::StreetAddress::US;
 103
 104     my $a = Geo::StreetAddress::US->parse_location($address);
 105
 106     return [
 107          "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
 108         ,"$a->{sec_unit_type} $a->{sec_unit_num}"
 109         ,$a->{city}
 110         ,$a->{state}
 111         ,$a->{zip}
 112     ];
 113 $$ LANGUAGE PLPERLU STABLE;
 114
 115 DROP TABLE IF EXISTS migration_tools.usps_suffixes;
 116 CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
 117 INSERT INTO migration_tools.usps_suffixes VALUES
 118     ('ALLEE','ALY'),
 119     ('ALLEY','ALY'),
 120     ('ALLY','ALY'),
 121     ('ALY','ALY'),
 122     ('ANEX','ANX'),
 123     ('ANNEX','ANX'),
 124     ('ANNX','ANX'),
 125     ('ANX','ANX'),
 126     ('ARCADE','ARC'),
 127     ('ARC','ARC'),
 128     ('AV','AVE'),
 129     ('AVE','AVE'),
 130     ('AVEN','AVE'),
 131     ('AVENU','AVE'),
 132     ('AVENUE','AVE'),
 133     ('AVN','AVE'),
 134     ('AVNUE','AVE'),
 135     ('BAYOO','BYU'),
 136     ('BAYOU','BYU'),
 137     ('BCH','BCH'),
 138     ('BEACH','BCH'),
 139     ('BEND','BND'),
 140     ('BLF','BLF'),
 141     ('BLUF','BLF'),
 142     ('BLUFF','BLF'),
 143     ('BLUFFS','BLFS'),
 144     ('BLVD','BLVD'),
 145     ('BND','BND'),
 146     ('BOT','BTM'),
 147     ('BOTTM','BTM'),
 148     ('BOTTOM','BTM'),
 149     ('BOUL','BLVD'),
 150     ('BOULEVARD','BLVD'),
 151     ('BOULV','BLVD'),
 152     ('BRANCH','BR'),
 153     ('BR','BR'),
 154     ('BRDGE','BRG'),
 155     ('BRG','BRG'),
 156     ('BRIDGE','BRG'),
 157     ('BRK','BRK'),
 158     ('BRNCH','BR'),
 159     ('BROOK','BRK'),
 160     ('BROOKS','BRKS'),
 161     ('BTM','BTM'),
 162     ('BURG','BG'),
 163     ('BURGS','BGS'),
 164     ('BYPA','BYP'),
 165     ('BYPAS','BYP'),
 166     ('BYPASS','BYP'),
 167     ('BYP','BYP'),
 168     ('BYPS','BYP'),
 169     ('CAMP','CP'),
 170     ('CANYN','CYN'),
 171     ('CANYON','CYN'),
 172     ('CAPE','CPE'),
 173     ('CAUSEWAY','CSWY'),
 174     ('CAUSWAY','CSWY'),
 175     ('CEN','CTR'),
 176     ('CENT','CTR'),
 177     ('CENTER','CTR'),
 178     ('CENTERS','CTRS'),
 179     ('CENTR','CTR'),
 180     ('CENTRE','CTR'),
 181     ('CIRC','CIR'),
 182     ('CIR','CIR'),
 183     ('CIRCL','CIR'),
 184     ('CIRCLE','CIR'),
 185     ('CIRCLES','CIRS'),
 186     ('CK','CRK'),
 187     ('CLB','CLB'),
 188     ('CLF','CLF'),
 189     ('CLFS','CLFS'),
 190     ('CLIFF','CLF'),
 191     ('CLIFFS','CLFS'),
 192     ('CLUB','CLB'),
 193     ('CMP','CP'),
 194     ('CNTER','CTR'),
 195     ('CNTR','CTR'),
 196     ('CNYN','CYN'),
 197     ('COMMON','CMN'),
 198     ('COR','COR'),
 199     ('CORNER','COR'),
 200     ('CORNERS','CORS'),
 201     ('CORS','CORS'),
 202     ('COURSE','CRSE'),
 203     ('COURT','CT'),
 204     ('COURTS','CTS'),
 205     ('COVE','CV'),
 206     ('COVES','CVS'),
 207     ('CP','CP'),
 208     ('CPE','CPE'),
 209     ('CRCL','CIR'),
 210     ('CRCLE','CIR'),
 211     ('CR','CRK'),
 212     ('CRECENT','CRES'),
 213     ('CREEK','CRK'),
 214     ('CRESCENT','CRES'),
 215     ('CRES','CRES'),
 216     ('CRESENT','CRES'),
 217     ('CREST','CRST'),
 218     ('CRK','CRK'),
 219     ('CROSSING','XING'),
 220     ('CROSSROAD','XRD'),
 221     ('CRSCNT','CRES'),
 222     ('CRSE','CRSE'),
 223     ('CRSENT','CRES'),
 224     ('CRSNT','CRES'),
 225     ('CRSSING','XING'),
 226     ('CRSSNG','XING'),
 227     ('CRT','CT'),
 228     ('CSWY','CSWY'),
 229     ('CT','CT'),
 230     ('CTR','CTR'),
 231     ('CTS','CTS'),
 232     ('CURVE','CURV'),
 233     ('CV','CV'),
 234     ('CYN','CYN'),
 235     ('DALE','DL'),
 236     ('DAM','DM'),
 237     ('DIV','DV'),
 238     ('DIVIDE','DV'),
 239     ('DL','DL'),
 240     ('DM','DM'),
 241     ('DR','DR'),
 242     ('DRIV','DR'),
 243     ('DRIVE','DR'),
 244     ('DRIVES','DRS'),
 245     ('DRV','DR'),
 246     ('DVD','DV'),
 247     ('DV','DV'),
 248     ('ESTATE','EST'),
 249     ('ESTATES','ESTS'),
 250     ('EST','EST'),
 251     ('ESTS','ESTS'),
 252     ('EXP','EXPY'),
 253     ('EXPRESS','EXPY'),
 254     ('EXPRESSWAY','EXPY'),
 255     ('EXPR','EXPY'),
 256     ('EXPW','EXPY'),
 257     ('EXPY','EXPY'),
 258     ('EXTENSION','EXT'),
 259     ('EXTENSIONS','EXTS'),
 260     ('EXT','EXT'),
 261     ('EXTN','EXT'),
 262     ('EXTNSN','EXT'),
 263     ('EXTS','EXTS'),
 264     ('FALL','FALL'),
 265     ('FALLS','FLS'),
 266     ('FERRY','FRY'),
 267     ('FIELD','FLD'),
 268     ('FIELDS','FLDS'),
 269     ('FLAT','FLT'),
 270     ('FLATS','FLTS'),
 271     ('FLD','FLD'),
 272     ('FLDS','FLDS'),
 273     ('FLS','FLS'),
 274     ('FLT','FLT'),
 275     ('FLTS','FLTS'),
 276     ('FORD','FRD'),
 277     ('FORDS','FRDS'),
 278     ('FOREST','FRST'),
 279     ('FORESTS','FRST'),
 280     ('FORGE','FRG'),
 281     ('FORGES','FRGS'),
 282     ('FORG','FRG'),
 283     ('FORK','FRK'),
 284     ('FORKS','FRKS'),
 285     ('FORT','FT'),
 286     ('FRD','FRD'),
 287     ('FREEWAY','FWY'),
 288     ('FREEWY','FWY'),
 289     ('FRG','FRG'),
 290     ('FRK','FRK'),
 291     ('FRKS','FRKS'),
 292     ('FRRY','FRY'),
 293     ('FRST','FRST'),
 294     ('FRT','FT'),
 295     ('FRWAY','FWY'),
 296     ('FRWY','FWY'),
 297     ('FRY','FRY'),
 298     ('FT','FT'),
 299     ('FWY','FWY'),
 300     ('GARDEN','GDN'),
 301     ('GARDENS','GDNS'),
 302     ('GARDN','GDN'),
 303     ('GATEWAY','GTWY'),
 304     ('GATEWY','GTWY'),
 305     ('GATWAY','GTWY'),
 306     ('GDN','GDN'),
 307     ('GDNS','GDNS'),
 308     ('GLEN','GLN'),
 309     ('GLENS','GLNS'),
 310     ('GLN','GLN'),
 311     ('GRDEN','GDN'),
 312     ('GRDN','GDN'),
 313     ('GRDNS','GDNS'),
 314     ('GREEN','GRN'),
 315     ('GREENS','GRNS'),
 316     ('GRN','GRN'),
 317     ('GROVE','GRV'),
 318     ('GROVES','GRVS'),
 319     ('GROV','GRV'),
 320     ('GRV','GRV'),
 321     ('GTWAY','GTWY'),
 322     ('GTWY','GTWY'),
 323     ('HARB','HBR'),
 324     ('HARBOR','HBR'),
 325     ('HARBORS','HBRS'),
 326     ('HARBR','HBR'),
 327     ('HAVEN','HVN'),
 328     ('HAVN','HVN'),
 329     ('HBR','HBR'),
 330     ('HEIGHT','HTS'),
 331     ('HEIGHTS','HTS'),
 332     ('HGTS','HTS'),
 333     ('HIGHWAY','HWY'),
 334     ('HIGHWY','HWY'),
 335     ('HILL','HL'),
 336     ('HILLS','HLS'),
 337     ('HIWAY','HWY'),
 338     ('HIWY','HWY'),
 339     ('HL','HL'),
 340     ('HLLW','HOLW'),
 341     ('HLS','HLS'),
 342     ('HOLLOW','HOLW'),
 343     ('HOLLOWS','HOLW'),
 344     ('HOLW','HOLW'),
 345     ('HOLWS','HOLW'),
 346     ('HRBOR','HBR'),
 347     ('HT','HTS'),
 348     ('HTS','HTS'),
 349     ('HVN','HVN'),
 350     ('HWAY','HWY'),
 351     ('HWY','HWY'),
 352     ('INLET','INLT'),
 353     ('INLT','INLT'),
 354     ('IS','IS'),
 355     ('ISLAND','IS'),
 356     ('ISLANDS','ISS'),
 357     ('ISLANDS','SLNDS'),
 358     ('ISLANDS','SS'),
 359     ('ISLE','ISLE'),
 360     ('ISLES','ISLE'),
 361     ('ISLND','IS'),
 362     ('I','SLNDS'),
 363     ('ISS','ISS'),
 364     ('JCTION','JCT'),
 365     ('JCT','JCT'),
 366     ('JCTN','JCT'),
 367     ('JCTNS','JCTS'),
 368     ('JCTS','JCTS'),
 369     ('JUNCTION','JCT'),
 370     ('JUNCTIONS','JCTS'),
 371     ('JUNCTN','JCT'),
 372     ('JUNCTON','JCT'),
 373     ('KEY','KY'),
 374     ('KEYS','KYS'),
 375     ('KNL','KNL'),
 376     ('KNLS','KNLS'),
 377     ('KNOL','KNL'),
 378     ('KNOLL','KNL'),
 379     ('KNOLLS','KNLS'),
 380     ('KY','KY'),
 381     ('KYS','KYS'),
 382     ('LAKE','LK'),
 383     ('LAKES','LKS'),
 384     ('LA','LN'),
 385     ('LANDING','LNDG'),
 386     ('LAND','LAND'),
 387     ('LANE','LN'),
 388     ('LANES','LN'),
 389     ('LCK','LCK'),
 390     ('LCKS','LCKS'),
 391     ('LDGE','LDG'),
 392     ('LDG','LDG'),
 393     ('LF','LF'),
 394     ('LGT','LGT'),
 395     ('LIGHT','LGT'),
 396     ('LIGHTS','LGTS'),
 397     ('LK','LK'),
 398     ('LKS','LKS'),
 399     ('LNDG','LNDG'),
 400     ('LNDNG','LNDG'),
 401     ('LN','LN'),
 402     ('LOAF','LF'),
 403     ('LOCK','LCK'),
 404     ('LOCKS','LCKS'),
 405     ('LODGE','LDG'),
 406     ('LODG','LDG'),
 407     ('LOOP','LOOP'),
 408     ('LOOPS','LOOP'),
 409     ('MALL','MALL'),
 410     ('MANOR','MNR'),
 411     ('MANORS','MNRS'),
 412     ('MDW','MDW'),
 413     ('MDWS','MDWS'),
 414     ('MEADOW','MDW'),
 415     ('MEADOWS','MDWS'),
 416     ('MEDOWS','MDWS'),
 417     ('MEWS','MEWS'),
 418     ('MILL','ML'),
 419     ('MILLS','MLS'),
 420     ('MISSION','MSN'),
 421     ('MISSN','MSN'),
 422     ('ML','ML'),
 423     ('MLS','MLS'),
 424     ('MNR','MNR'),
 425     ('MNRS','MNRS'),
 426     ('MNTAIN','MTN'),
 427     ('MNT','MT'),
 428     ('MNTN','MTN'),
 429     ('MNTNS','MTNS'),
 430     ('MOTORWAY','MTWY'),
 431     ('MOUNTAIN','MTN'),
 432     ('MOUNTAINS','MTNS'),
 433     ('MOUNTIN','MTN'),
 434     ('MOUNT','MT'),
 435     ('MSN','MSN'),
 436     ('MSSN','MSN'),
 437     ('MTIN','MTN'),
 438     ('MT','MT'),
 439     ('MTN','MTN'),
 440     ('NCK','NCK'),
 441     ('NECK','NCK'),
 442     ('ORCHARD','ORCH'),
 443     ('ORCH','ORCH'),
 444     ('ORCHRD','ORCH'),
 445     ('OVAL','OVAL'),
 446     ('OVERPASS','OPAS'),
 447     ('OVL','OVAL'),
 448     ('PARK','PARK'),
 449     ('PARKS','PARK'),
 450     ('PARKWAY','PKWY'),
 451     ('PARKWAYS','PKWY'),
 452     ('PARKWY','PKWY'),
 453     ('PASSAGE','PSGE'),
 454     ('PASS','PASS'),
 455     ('PATH','PATH'),
 456     ('PATHS','PATH'),
 457     ('PIKE','PIKE'),
 458     ('PIKES','PIKE'),
 459     ('PINE','PNE'),
 460     ('PINES','PNES'),
 461     ('PK','PARK'),
 462     ('PKWAY','PKWY'),
 463     ('PKWY','PKWY'),
 464     ('PKWYS','PKWY'),
 465     ('PKY','PKWY'),
 466     ('PLACE','PL'),
 467     ('PLAINES','PLNS'),
 468     ('PLAIN','PLN'),
 469     ('PLAINS','PLNS'),
 470     ('PLAZA','PLZ'),
 471     ('PLN','PLN'),
 472     ('PLNS','PLNS'),
 473     ('PL','PL'),
 474     ('PLZA','PLZ'),
 475     ('PLZ','PLZ'),
 476     ('PNES','PNES'),
 477     ('POINT','PT'),
 478     ('POINTS','PTS'),
 479     ('PORT','PRT'),
 480     ('PORTS','PRTS'),
 481     ('PRAIRIE','PR'),
 482     ('PRARIE','PR'),
 483     ('PRK','PARK'),
 484     ('PR','PR'),
 485     ('PRR','PR'),
 486     ('PRT','PRT'),
 487     ('PRTS','PRTS'),
 488     ('PT','PT'),
 489     ('PTS','PTS'),
 490     ('RADIAL','RADL'),
 491     ('RADIEL','RADL'),
 492     ('RADL','RADL'),
 493     ('RAD','RADL'),
 494     ('RAMP','RAMP'),
 495     ('RANCHES','RNCH'),
 496     ('RANCH','RNCH'),
 497     ('RAPID','RPD'),
 498     ('RAPIDS','RPDS'),
 499     ('RDGE','RDG'),
 500     ('RDG','RDG'),
 501     ('RDGS','RDGS'),
 502     ('RD','RD'),
 503     ('RDS','RDS'),
 504     ('REST','RST'),
 505     ('RIDGE','RDG'),
 506     ('RIDGES','RDGS'),
 507     ('RIVER','RIV'),
 508     ('RIV','RIV'),
 509     ('RIVR','RIV'),
 510     ('RNCH','RNCH'),
 511     ('RNCHS','RNCH'),
 512     ('ROAD','RD'),
 513     ('ROADS','RDS'),
 514     ('ROUTE','RTE'),
 515     ('ROW','ROW'),
 516     ('RPD','RPD'),
 517     ('RPDS','RPDS'),
 518     ('RST','RST'),
 519     ('RUE','RUE'),
 520     ('RUN','RUN'),
 521     ('RVR','RIV'),
 522     ('SHL','SHL'),
 523     ('SHLS','SHLS'),
 524     ('SHOAL','SHL'),
 525     ('SHOALS','SHLS'),
 526     ('SHOAR','SHR'),
 527     ('SHOARS','SHRS'),
 528     ('SHORE','SHR'),
 529     ('SHORES','SHRS'),
 530     ('SHR','SHR'),
 531     ('SHRS','SHRS'),
 532     ('SKYWAY','SKWY'),
 533     ('SMT','SMT'),
 534     ('SPG','SPG'),
 535     ('SPGS','SPGS'),
 536     ('SPNG','SPG'),
 537     ('SPNGS','SPGS'),
 538     ('SPRING','SPG'),
 539     ('SPRINGS','SPGS'),
 540     ('SPRNG','SPG'),
 541     ('SPRNGS','SPGS'),
 542     ('SPUR','SPUR'),
 543     ('SPURS','SPUR'),
 544     ('SQRE','SQ'),
 545     ('SQR','SQ'),
 546     ('SQRS','SQS'),
 547     ('SQ','SQ'),
 548     ('SQUARE','SQ'),
 549     ('SQUARES','SQS'),
 550     ('SQU','SQ'),
 551     ('STA','STA'),
 552     ('STATION','STA'),
 553     ('STATN','STA'),
 554     ('STN','STA'),
 555     ('STRA','STRA'),
 556     ('STRAVEN','STRA'),
 557     ('STRAVENUE','STRA'),
 558     ('STRAVE','STRA'),
 559     ('STRAVN','STRA'),
 560     ('STRAV','STRA'),
 561     ('STREAM','STRM'),
 562     ('STREETS','STS'),
 563     ('STREET','ST'),
 564     ('STREME','STRM'),
 565     ('STRM','STRM'),
 566     ('STR','ST'),
 567     ('STRT','ST'),
 568     ('STRVN','STRA'),
 569     ('STRVNUE','STRA'),
 570     ('ST','ST'),
 571     ('SUMIT','SMT'),
 572     ('SUMITT','SMT'),
 573     ('SUMMIT','SMT'),
 574     ('TERRACE','TER'),
 575     ('TERR','TER'),
 576     ('TER','TER'),
 577     ('THROUGHWAY','TRWY'),
 578     ('TPKE','TPKE'),
 579     ('TPK','TPKE'),
 580     ('TRACES','TRCE'),
 581     ('TRACE','TRCE'),
 582     ('TRACKS','TRAK'),
 583     ('TRACK','TRAK'),
 584     ('TRAFFICWAY','TRFY'),
 585     ('TRAILS','TRL'),
 586     ('TRAIL','TRL'),
 587     ('TRAK','TRAK'),
 588     ('TRCE','TRCE'),
 589     ('TRFY','TRFY'),
 590     ('TRKS','TRAK'),
 591     ('TRK','TRAK'),
 592     ('TRLS','TRL'),
 593     ('TRL','TRL'),
 594     ('TRNPK','TPKE'),
 595     ('TRPK','TPKE'),
 596     ('TR','TRL'),
 597     ('TUNEL','TUNL'),
 598     ('TUNLS','TUNL'),
 599     ('TUNL','TUNL'),
 600     ('TUNNELS','TUNL'),
 601     ('TUNNEL','TUNL'),
 602     ('TUNNL','TUNL'),
 603     ('TURNPIKE','TPKE'),
 604     ('TURNPK','TPKE'),
 605     ('UNDERPASS','UPAS'),
 606     ('UNIONS','UNS'),
 607     ('UNION','UN'),
 608     ('UN','UN'),
 609     ('VALLEYS','VLYS'),
 610     ('VALLEY','VLY'),
 611     ('VALLY','VLY'),
 612     ('VDCT','IA'),
 613     ('VIADCT','VIA'),
 614     ('VIADUCT','IA'),
 615     ('VIADUCT','VIA'),
 616     ('VIA','VIA'),
 617     ('VIEWS','VWS'),
 618     ('VIEW','VW'),
 619     ('VILLAGES','VLGS'),
 620     ('VILLAGE','VLG'),
 621     ('VILLAG','VLG'),
 622     ('VILLE','VL'),
 623     ('VILLG','VLG'),
 624     ('VILLIAGE','VLG'),
 625     ('VILL','VLG'),
 626     ('VISTA','VIS'),
 627     ('VIST','VIS'),
 628     ('VIS','VIS'),
 629     ('VLGS','VLGS'),
 630     ('VLG','VLG'),
 631     ('VLLY','VLY'),
 632     ('VL','VL'),
 633     ('VLYS','VLYS'),
 634     ('VLY','VLY'),
 635     ('VSTA','VIS'),
 636     ('VST','VIS'),
 637     ('VWS','VWS'),
 638     ('VW','VW'),
 639     ('WALKS','WALK'),
 640     ('WALK','WALK'),
 641     ('WALL','WALL'),
 642     ('WAYS','WAYS'),
 643     ('WAY','WAY'),
 644     ('WELLS','WLS'),
 645     ('WELL','WL'),
 646     ('WLS','WLS'),
 647     ('WY','WAY'),
 648     ('XING','XING');
 649
 650 -- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
 651 CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
 652     DECLARE
 653         suffix TEXT := $1;
 654                 _r RECORD;
 655     BEGIN
 656         --RAISE INFO 'suffix = %', suffix;
 657                 FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
 658                         suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
 659                 END LOOP;
 660                 RETURN suffix;
 661     END;
 662 $$ LANGUAGE PLPGSQL STRICT STABLE;
 663
 664 CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
 665     BEGIN
 666                 RETURN CASE
 667             WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
 668             ELSE $1
 669         END;
 670     END;
 671 $$ LANGUAGE PLPGSQL STRICT STABLE;
 672