#!/bin/bash # Tries really really hard to salvage a usable address out of the gobbldygook OnTrac sticks in the address field. # Can fix most OnTrac-caused issues, and many customer-caused issues, but there's no fixing something that's just plain wrong, or not an address... # Looks for any stops with addresses that haven't been fixed yet (that is, no addr table entry), and tries to fix them. # Copyright 2017 Randy Gardner # All rights reserved. Commercial use or distribution prohibited. # Where to output the SQL to. sqltemp="/tmp/foo.sql" sqlfile="" savesql=0 # sqlite3 database database="ontrac" addtodb=0 wipedb=0 # Verbosity. 0 prints everything, 1 only prints addresses, 2 only prints summary, 3 is silent. quiet=2 # A manually edited list of known street names, to aid in finding apartments. streetsfile="streets.txt" # A manually edited list of regexs to fix things too broken to automatically fix. fixupsfile="fixups.txt" # Temporary file. Should be random. I'm lazy. addrtmp="/tmp/addrtmp.txt" showhelp=0 while getopts ":s:q:d:wh" opt do case $opt in h) showhelp=1 ;; w) wipedb=1 if (( quiet < 3 )) then echo "Creating / wiping and re-creating tables." fi ;; d) database="$OPTARG" addtodb=1 if (( quiet < 2 )) then echo "Using database '$database'." fi ;; s) sqlfile="$OPTARG" savesql=1 if (( quiet < 2 )) then echo "Saving SQL output to '$sqlfile'." fi ;; q) quiet="$OPTARG" if (( quiet < 2 )) then echo "Setting verbosity to $quiet." fi ;; \?) echo "Dunno what -$OPTARG is..." showhelp=1 ;; :) echo "Option -$OPTARG requires an argument." showhelp=1 ;; esac done shift $((OPTIND-1)) if (( showhelp > 0 )) then echo "OnTrac address fixer version 0.1. Copyright 2017 Randy Gardner." echo "Usage: $(basename "$0") [-q #] [-s sqlfile] [-d database] [-w] [-h]" echo " -q quietness. Less is more. Current range is -3 to 3." echo " -s save the sql in the specified file instead of adding it to the database." echo " -d uses the specified sqlite3 database." echo " -w creates the database, wiping tables if they already exist." echo " -h You're lookin' at it, pal!" exit fi if (( savesql == 0 )) && (( addtodb == 0 )) then if (( quiet < 2 )) then echo "Using default database '$database'." fi addtodb=1 fi # &3 is the sql file. exec 3> "$sqltemp" # Initialize a new database, or wipe and start over. if (( wipedb == 1 )) then # drop existing table, if it exists echo "drop table if exists addr;" >&3 # create table echo "CREATE TABLE addr(rawaddr varchar(100), mapaddr varchar(100), fixedaddr varchar(100), house varchar(20), street varchar(50), apt varchar(50), junk varchar(50), city varchar(50), state varchar(20), zip varchar(20), shortzip varchar(10));" >&3 # create indexes (indicies?) echo "CREATE INDEX addridx1 on addr (rawaddr);" >&3 echo "CREATE INDEX addridx2 on addr (city);" >&3 echo "CREATE INDEX addridx3 on addr (street);" >&3 echo "CREATE INDEX addridx4 on addr (house);" >&3 echo "CREATE INDEX addridx5 on addr (city, street);" >&3 fi # One transaction. sqlite really likes transactions. use transactions. echo "BEGIN;" >&3 #sedpipe1="/tmp/sedpipe1" #sedpipe2="/tmp/sedpipe2" #mkfifo "$sedpipe1" #mkfifo "$sedpipe2" #sed -r -e "$fixups" <"$sedpipe1" >"$sedpipe2" & streets="$(cat "$streetsfile" | sed 's/^#.*//' | grep -v '^$' | sed 's/^/^/' | tr '\n' '|' | sed 's/|$//')" #echo "Streets: '$streets'" fixups="$(cat "$fixupsfile" | sed 's/^#.*//' | grep -v '^$' | tr '\n' ';' | sed 's/;$//')" #echo "Fixups: '$fixups'" # common suffixes for street names. suffixes="(ST|AVE|DR|RD|WY|WAY|CT|PL|LN|BLVD|CIR|TER|LOOP|TRL|HWY [0-9]*|HIGHWAY [0-9]*)" apts='(PO BOX|BOX|BX|#|APT|STE|SPC|UNIT|HSE|SUITE|PO|POB|-|RM|ROOM|BLDG|NO[ .]|SPACE)' # states states="(AL|AK|AS|AZ|AR|CA|CO|CT|DE|DC|FM|FL|GA|GU|HI|ID|IL|IN|IA|KS|KY|LA|ME|MH|MD|MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|NY|NC|ND|MP|OH|OK|OR|PW|PA|PR|RI|SC|SD|TN|TX|UT|VT|VI|VA|WA|WV|WI|WY)" # If we're wiping the table, we shouldn't check what was there before - and the table might not exist yet. if (( wipedb == 0 )) then query="select distinct addr from stop where (select count(*) from addr where addr.rawaddr = stop.addr) = 0;" else query="select distinct addr from stop;" fi # sqlite will output one addr per line, for stops with addrs that are not in the addr table addrcount=0 while IFS='' read -r addr || [[ -n "$addr" ]] do # rawaddr="$addr" [[ "$addr" =~ ^(.*)\|\ *(.*)\ *$ ]] rawaddr="${BASH_REMATCH[1]}" addr="${BASH_REMATCH[2]}" # slooooooow, but I'm too lazy to finish the fifo version, and since it only does _new_ addresses... # addr="$(echo "$addr" | sed -r -e "$fixups")" # replaced with much faster version, but leaving that commented out until I'm sure it's non-buggy if (( quiet < 1 )) && [[ "$addr" != "$rawaddr" ]] then echo "'$rawaddr' -> '$addr'" fi addr="${addr^^}" re='^([0-9]*)([A-Z]*) ?(1/2)? (.*), ([[:print:]]*) ([0-9]{5})-?([0-9]{4})?' [[ "$addr" =~ $re ]] house="${BASH_REMATCH[1]}" street="${BASH_REMATCH[4]}" city="${BASH_REMATCH[5]}" state="" # not present on most lines, let it get lumped into city zip="${BASH_REMATCH[6]}-${BASH_REMATCH[7]}" shortzip="${BASH_REMATCH[6]}" apt="${BASH_REMATCH[2]}${BASH_REMATCH[3]}" junk="" #echo "~$house@ #$street% ^$city& ($zip)" # Occasionally we get a state. Very, very occasionally. If the last part of the city ends with a valid state code, separate it out. re="^(.*) ($states)\$" if [[ "$city" =~ $re ]] then city="${BASH_REMATCH[1]}" state="${BASH_REMATCH[2]}" #echo "city with state: '$city', '$state'." fi street="${street// STREET/ ST}" street="${street// DRIVE/ DR}" street="${street// ROAD/ RD}" street="${street// LANE/ LN}" street="${street// COURT/ CT}" street="${street// AVENUE/ AVE}" street="${street// CIRCLE/ CIR}" street="${street// PLACE/ PL}" street="${street// WAY/ WY}" if [[ "$zip" =~ (.*)-$ ]] then zip="${BASH_REMATCH[1]}" fi if [[ "$street" =~ ^(.*)\ $ ]] then # If the street portion ends in a space, the apt field was blank, and the address is probably not fucked up. Remove the space and continue. street="${BASH_REMATCH[1]}" else firstpassfixed=0 if [[ "$street" =~ \ \ ]] then # Does it have a double space? Streets with double spaces tend to contain the house number and address repeated, or two letters that may be the state # followed by part of the zip. And the zip is virtually always missing the hyphen, giving a redundant way to identify these, since the double space is new. # Could also look for the house number again. Is it cause? Effect? Use of crack cocaine? OnTrac! # To fix these, remove everything after the duplicate house number re1="(.*) $house .*" re2='(.*) [A-Z]{2} [0-9]+' re3="(.*) ${city:0:3}[A-Z]*" re4="(.*) (.*)" if [[ "$house" != "" ]] && [[ "$street" =~ $re1 ]] then # Looks like we found a duplicated address portion. Keep the first part. Don't bother saving the junk. if (( quiet < 1 )) then echo "Original '$street' de-dup fixed to '${BASH_REMATCH[1]}'. (House '$house')" fi street="${BASH_REMATCH[1]}" firstpassfixed=1 #fi elif [[ "$street" =~ $re2 ]] then # Looks like we found a state(?) followed by two spaces and a zip part. Keep first part again. Toss junk again. if (( quiet < 1 )) then echo "Original '$street' un-statezip fixed to '${BASH_REMATCH[1]}'." fi street="${BASH_REMATCH[1]}" firstpassfixed=1 #fi elif [[ "$street" =~ $re3 ]] then # Looks like it's part of the city name. Again, don't keep it.. if (( quiet < 1 )) then echo "Original '$street' un-city fixed to '${BASH_REMATCH[1]}'." fi street="${BASH_REMATCH[1]}" firstpassfixed=1 else # Last ditch effort, just chop off anything after the double space, and hope whatever was before looks reasonable. [[ "$street" =~ $re4 ]] if (( quiet < 1 )) then echo "Original '$street' double-space-chop fixed to '${BASH_REMATCH[1]}' with junk '${BASH_REMATCH[2]}'." fi street="${BASH_REMATCH[1]}" junk="$junk ${BASH_REMATCH[2]}" fi fi # Try to match and remove actual apt fields re1="(.*) $apts[.: ]*([A-Z0-9-]+)( ([A-Z0-9]*))?\$" re2="(.* $suffixes) ([A-Z0-9])\$" re3="(.* $suffixes) (.*)\$" if [[ "$street" =~ $re1 ]] then # Matched what looks like a useful apartment number if (( quiet < 1 )) then echo "Original '$street' apt fixed to '${BASH_REMATCH[1]}' '${BASH_REMATCH[2]} ${BASH_REMATCH[3]}', extra '${BASH_REMATCH[5]}'." fi street="${BASH_REMATCH[1]}" apt="$apt ${BASH_REMATCH[2]} ${BASH_REMATCH[3]}" junk="$junk ${BASH_REMATCH[5]}" #fi elif [[ "$street" =~ $re2 ]] then # Assume a single character or digit at the end of a valid street suffix is an apartment if (( quiet < 1 )) then echo "Original '$street' maybe-apt fixed to '${BASH_REMATCH[1]}' '${BASH_REMATCH[3]}'." fi street="${BASH_REMATCH[1]}" apt="$apt ${BASH_REMATCH[3]}" #fi elif [[ "$street" =~ $re3 ]] then # Last-ditch effort. Cut off anything after a street suffix and hope. if (( quiet < 1 )) then echo "Original '$street' junk fixed to '${BASH_REMATCH[1]}' with junk '${BASH_REMATCH[3]}'." fi street="${BASH_REMATCH[1]}" junk="$junk ${BASH_REMATCH[3]}" else # We didn't find a space at the end, so it had an apartment field, but we couldn't figure out where it was. if (( firstpassfixed==0 )) then # Only gripe if we didn't fix it above, to reduce excessive gripes. if (( quiet < 1 )) then echo "Don't know how to fix '$street'." fi fi fi fi # Check for PO Boxes at the start instead of end of address re='^(PO BOX|POBOX|BOX)( *[0-9]+) ([0-9]*[A-Z]*( 1/2)?) ([^,]*)' if [[ "$street" =~ $re ]] then # Matched what looks like a leading PO Box if (( quiet < 1 )) then echo "Original '$street' PO Box fixed to '(${BASH_REMATCH[3]}${BASH_REMATCH[4]}) ${BASH_REMATCH[5]}' with '${BASH_REMATCH[1]}${BASH_REMATCH[2]}'." fi house="${BASH_REMATCH[3]}${BASH_REMATCH[4]}" street="${BASH_REMATCH[5]}" junk="$junk ${BASH_REMATCH[1]}${BASH_REMATCH[2]}" fi # Check if all we have is a bare PO Box, which is useless... re1='^(P[. ]*O[. ]* BOX|POBOX|BOX)( *[0-9]+) *$' re2='^(P[. ]*O[. ]* BOX|POBOX|BOX)$' if [[ "$street" =~ $re1 ]] then # Matched what looks like just a PO Box if (( quiet < 1 )) then echo "Original '$street' can't be fixed, just a PO BOX: '${BASH_REMATCH[1]}${BASH_REMATCH[2]}'." fi street="" apt="$apt ${BASH_REMATCH[1]}${BASH_REMATCH[2]}" #fi elif [[ "$street" =~ $re2 ]] && [[ "$house" != "" ]] then # Matched what looks like just the string PO Box, but we got a house number... backwards? if (( quiet < 1 )) then echo "Original '$street' can't be fixed, just a PO BOX: '${BASH_REMATCH[1]} $house'." fi street="" apt="$apt ${BASH_REMATCH[1]} $house" house="" else # Check to see if we can find an apartment, again re1="(.*) $apts[.: ]*([A-Z0-9-]+)\$" if [[ "$street" =~ $re1 ]] then # Matched what looks like a useful apartment number if (( quiet < 1 )) then echo "Original '$street' apt fixed on the second pass to '${BASH_REMATCH[1]}' '${BASH_REMATCH[2]} ${BASH_REMATCH[3]}'." fi street="${BASH_REMATCH[1]}" apt="$apt ${BASH_REMATCH[2]} ${BASH_REMATCH[3]}" fi # Check for remaining junk after a known street name re2="($streets) (.*)\$" if [[ "$street" =~ $re2 ]] then # If it's after a known street name, it's hopefully not useful, assuming the known streets file is very very carefully worded... if (( quiet < 1 )) then echo "Original '$street' known-street fixed to '${BASH_REMATCH[1]}' with junk '${BASH_REMATCH[2]}'." fi street="${BASH_REMATCH[1]}" junk="$junk ${BASH_REMATCH[2]}" fi # Check for remaining junk after a street suffix re3="(.* $suffixes) (.*)\$" if [[ "$street" =~ $re3 ]] then # Last-ditch effort. Cut off anything after a street suffix. Should we make sure it's not another street suffix? if (( quiet < 1 )) then echo "Original '$street' junk fixed on the second pass to '${BASH_REMATCH[1]}' with junk '${BASH_REMATCH[3]}'." fi street="${BASH_REMATCH[1]}" junk="$junk ${BASH_REMATCH[3]}" fi # Check for remaining junk after a comma re3="([^,]*),(.*)\$" if [[ "$street" =~ $re3 ]] then # Last-ditch effort. Cut off anything after a comma. It's either badly broken or a parse error. if (( quiet < 1 )) then echo "Original '$street' comma fixed to '${BASH_REMATCH[1]}' with junk '${BASH_REMATCH[2]}'." fi street="${BASH_REMATCH[1]}" junk="$junk ${BASH_REMATCH[2]}" fi fi # Lazy concatenation above gives a leading space [[ "$apt" =~ \ *(.*) ]] apt="${BASH_REMATCH[1]}" [[ "$junk" =~ \ *(.*) ]] junk="${BASH_REMATCH[1]}" #echo "$street" if (( quiet < 2 )) then echo "'$addr' => '$house $street ($apt) <$junk>, $city $zip'" fi echo -n "insert into addr(rawaddr, mapaddr, fixedaddr, house, street, apt, junk, city, state, zip, shortzip) values " >&3 echo "(\"$rawaddr\", \"$house $street, $city $zip\", \"$house $street $apt, $city $zip\", \"$house\", \"$street\", \"$apt\", \"$junk\", \"$city\", \"$state\", \"$zip\", \"$shortzip\");" >&3 (( addrcount++ )) # done < <( sqlite3 "$database" "$query" ) # done < <( sqlite3 "$database" "$query" | tee "$addrtmp" | sed -r -e "$fixups" | paste -d '|' "-" "$addrtmp" ) done < <( sqlite3 "$database" "$query" >"$addrtmp" ; sed -r -e "$fixups" "$addrtmp" | paste -d '|' "$addrtmp" "-" ) # saves the addresses to a temp file, processes them through the fixups regexps, then joins them back with the original addresses with a pipe separator. # this lets you write regexps without having to worry about what else is on the line, and lets me not need to deal with running them through a fifo or otherwise # having to think too hard. if (( quiet < 3 )) then echo "Fixed $addrcount addresses." fi echo "COMMIT;" >&3 exec 3>&- if (( addtodb > 0 )) then cat "$sqltemp" | sqlite3 "$database" fi if (( savesql > 0 )) then mv "$sqltemp" "$sqlfile" fi # old dedup code, handily kept here if I ever need it again. #sed -r 's/^([0-9]+ +[A-Z0-9 .]{1,})([^,]*) \1,(.*)$/\1\2, \3/' #sed -r 's/^([0-9]*) ([A-Z0-9 .]{2,})([^,]*) \2,(.*)$/\1 \2\3, \4/'