sql/base/05-addresses.sql

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