FCC Callsign Database Script

This is a Bourne Shell script I wrote several years ago that will automagically download the FCC Amateur Radio license database, parse it, and populate a MySQL database. The database can be searched using the command line and is very fast. The download can be put into crontab in order to periodically download a database, so you always have fresh data. The program works on Linux, but undoubtedly works on other flavors of Unix.

I have not used this script for years, however at the time of this writing the FCC had not changed its database structure or the FTP download site.  Your mileage may vary, and you may need to tweak it.

Instructions

1. Copy and paste the code below into a file called hamdb . Place the script in /usr/local/bin or your favorite place.
2. If you do not have MySQL Version 11.13 or better, download and install it.
3. If you do not have wget on your system, download and install. To see if you have it, simply run wget and you will see some help text displayed if you have it. You can also search your hard drive with find / -name wget 
4. Run the script /usr/local/bin/hamdb . It will create a file in your home directory called .hamdb . Edit this file so that the proper MySQL username and pasword is configured.
5. Now we’ll create the database that hamdb will use. Run /usr/local/bin/hamdb makedatabase
6. Next we’ll download the FCC database and populate the MySQL database. Run /usr/local/bin/hamdb populatedb . This will download the database from the FCC and parse it. The raw database is about 50 MB, so it may take awhile to download depending on your Internet connection.
7. After the population completes, you can search the database by simply typing /usr/local/bin/hamdb <callsign> such as /usr/local/bin/hamdb k3ng .
8. To do a wildcard search, use the following: /usr/local/bin/hamdb like <expression> . Where <expression> is a portion of the callsign and the % symbol is a wildcard, much like * is used for file system commands. /usr/local/bin/hamdb like k3ng% would find all callsigns beginning with “k3ng”.
9. If you’d like to make this easier to use, place an alias in your system profile. I have the following line in /etc/profile: alias h=”/usr/local/bin/hamdb” With this, the “h” command can be used quickly to search such as h k3ng .

#!/bin/bash

# Hamdb
# Copyright (C) 2001-2008 Anthony Good

# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

# You may contact the author at goody@fast.net

# 07/31/2008 - Modified to download/import daily transaction files - AE5IR

STOREDIR=/tmp/hamdb.temp
VERSION=0.7.0
CONFIGFILE=/etc/hamdb.cnf
DEBUG=0
WGET=/usr/bin/wget
UNZIP=/usr/bin/unzip
DAY=`date -d "-1 day" | tr '[:upper:]' '[:lower:]' | sed 's/\([a-z]*\).*/\1/'`
if [ ${DAY} == sun ]; then
	FCC_FILE_LOCATION=http://wireless.fcc.gov/uls/data/complete/l_amat.zip
	DOWNLOAD_FILE=l_amat.zip
else
	FCC_FILE_LOCATION=http://wireless.fcc.gov/uls/data/daily/l_am_${DAY}.zip
	DOWNLOAD_FILE=l_am_${DAY}.zip
fi
if [ ${DEBUG} -eq 1 ]; then
  echo "In debug mode..."
fi

if [ -f ${CONFIGFILE} ]; then
  . ${CONFIGFILE}
else
  echo -n "Hamdb configuration file ${CONFIGFILE} not found.  Would you like to create the file? [y/N]"
  read userinput
  #echo -n "Enter MySQL login with God-like privleges: "
  #read MYSQLGODUSERNAME
  #echo -n "Enter MySQL God password: "
  #read MYSQLGODPASSWD
  #echo -n "Enter new MySQL user to create for hamdb database access: "
  echo -n "Enter MySQL username: "
  read MYSQLUSERNAME
  #echo -n "Enter new MySQL user's password: "
  echo -n "Enter MySQL user's password: "
  read MYSQLPASSWD
  #echo "show databases;" | mysql --user=${MYSQLGODUSERNAME} --password=${MYSQLGODPASSWD}
  echo "#!/bin/sh" > ${CONFIGFILE}
  echo "MYSQLUSERNAME=$MYSQLUSERNAME" >> ${CONFIGFILE}
  echo "MYSQLPASSWD=$MYSQLPASSWD" >> ${CONFIGFILE}
  chmod 700 ${CONFIGFILE}
fi

if [ ${DEBUG} -eq 1 ]; then
  echo "Username is ${MYSQLUSERNAME}"
  echo "Password is ${MYSQLPASSWD}"
  echo "\s" | mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}|grep "Server version:" | awk '{print $3}'
fi

case "$1" in

  getfccfile)
      echo "Downloading database from FCC..."
      #$WGET -nd --passive-ftp -d ftp://ftp.fcc.gov/pub/Bureaus/Wireless/Databases/uls/complete/l_amat.zip
      $WGET -nd -d $FCC_FILE_LOCATION
  ;;

  populatedb)
    if [ ! -d ${STOREDIR} ]; then
      mkdir ${STOREDIR}
    fi
    cd ${STOREDIR}

    if [ ! -f "${DOWNLOAD_FILE}" ]; then
      echo "Downloading database from FCC..."
      #wget -nd --passive-ftp -d ftp://ftp.fcc.gov/pub/Bureaus/Wireless/Databases/uls/complete/l_amat.zip
      $WGET -nd -d $FCC_FILE_LOCATION

    fi

    if [ ! -f EN.dat ]; then
      echo "Unzipping database file EN.dat..."
      ${UNZIP} ./${DOWNLOAD_FILE} EN.dat
    fi

    if [ ! -f AM.dat ]; then
      echo "Unzipping database file AM.dat..."
      ${UNZIP} ./${DOWNLOAD_FILE} AM.dat
    fi

    if [ ! -f HD.dat ]; then
      echo "Unzipping database file HD.dat..."
      ${UNZIP} ./${DOWNLOAD_FILE} HD.dat
    fi

    if [ ! -f tempfile_en.txt ]; then
      echo "Creating temporary file for table en..."
      cat ./EN.dat |sed s/[\\\"]//g|awk -F "|" '{print "\""$2"\",\""$5"\",\""$8"\",\""$9"\",\""$10"\",\""$11"\",\""$16"\",\""$17"\",\""$18"\",\""$19"\""}'>./tempfile_en.txt
    fi

    if [ ! -f tempfile_am.txt ]; then
      echo "Creating temporary file for table am..."
      cat ./AM.dat |sed s/[\\\"]//g|awk -F "|" '{print "\""$2"\",\""$5"\",\""$6"\",\""$7"\",\""$8"\",\""$10"\",\""$16"\",\""$17"\""}'>./tempfile_am.txt
    fi

    if [ ! -f tempfile_hd.txt ]; then
      echo "Creating temporary file for table hd..."
      cat ./HD.dat |sed s/[\\\"]//g|awk -F "|" '{print "\""$2"\",\""$5"\",\""$6"\""}'>./tempfile_hd.txt
    fi

	if [ ${DAY} == sun ]; then

    echo "Creating temporary tables in Mysql..."

    echo "use fcc_amateur;drop table if exists en_temp;create table en_temp (fccid int not null, callsign varchar(8) not null, primary key(fccid),full_name varchar(32),first varchar(20),middle varchar(1), last varchar(20), address1 varchar(32), city varchar(20), state varchar(2), zip varchar(10), index idx_zip (zip), index idx_callsign (callsign));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    #echo "use fcc_amateur;drop table if exists en_temp;create table en_temp (fccid int, callsign varchar(8) not null, primary key(callsign),full_name varchar(32),first varchar(20),middle varchar(1), last varchar(20), address1 varchar(32), city varchar(20), state varchar(2), zip varchar(10), index idx_zip (zip), index idx_fccid (fccid));"|mysql --user=${MYSQLUSERNAME} --password=$MYSQLPASSWD

    echo "use fcc_amateur;drop table if exists am_temp;create table am_temp (fccid int not null, callsign varchar(8) not null, primary key(fccid), class varchar(1), col4 varchar(1), col5 varchar(2), col6 varchar(3), former_call varchar(8), former_class varchar(1), index idx_callsign (callsign), index idx_class(class));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    #echo "use fcc_amateur;drop table if exists am_temp;create table am_temp (fccid int, callsign varchar(8) not null, primary key(callsign), class varchar(1), col4 varchar(1), col5 varchar(2), col6 varchar(3), former_call varchar(8), former_class varchar(1), index idx_fccid (fccid), index idx_class(class));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "use fcc_amateur;drop table if exists hd_temp;create table hd_temp (fccid int not null, callsign varchar(8) not null, primary key(fccid), status varchar(1), index idx_callsign (callsign), index idx_status (status) );"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "Populating database table en..."

    echo "use fcc_amateur;load data infile '${STOREDIR}/tempfile_en.txt' replace into table en_temp fields terminated by ',' enclosed by '\"' lines terminated by '\n';"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "use fcc_amateur;rename table en to en_old, en_temp to en;drop table en_old;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "Populating database table am..."

    echo "use fcc_amateur;load data infile '${STOREDIR}/tempfile_am.txt' replace into table am_temp fields terminated by ',' enclosed by '\"' lines terminated by '\n';"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "use fcc_amateur;rename table am to am_old, am_temp to am;drop table am_old;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "Populating database table hd..."

    echo "use fcc_amateur;load data infile '${STOREDIR}/tempfile_hd.txt' replace into table hd_temp fields terminated by ',' enclosed by '\"' lines terminated by '\n';"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "use fcc_amateur;rename table hd to hd_old, hd_temp to hd;drop table hd_old;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

	else

    echo "Updating database table en..."

    echo "use fcc_amateur;load data infile '${STOREDIR}/tempfile_en.txt' replace into table en fields terminated by ',' enclosed by '\"' lines terminated by '\n';"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "Updating database table am..."

    echo "use fcc_amateur;load data infile '${STOREDIR}/tempfile_am.txt' replace into table am fields terminated by ',' enclosed by '\"' lines terminated by '\n';"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "Updating database table hd..."

    echo "use fcc_amateur;load data infile '${STOREDIR}/tempfile_hd.txt' replace into table hd fields terminated by ',' enclosed by '\"' lines terminated by '\n';"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

	fi

    if [ ${DEBUG} -eq 0 ]; then
      echo "Cleaning up..."
      rm ./tempfile_*.txt
      rm ./*.dat
      rm ./*.zip
      rm ./counts
      cd -
      rmdir ${STOREDIR}
    fi

    echo "Done..."

    ;;

  makedatabase)

    echo "Creating database fcc_amateur...";

    echo "create database fcc_amateur;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "use fcc_amateur;create table en (fccid int not null, callsign varchar(8) not null, primary key(fccid),full_name varchar(32),first varchar(20),middle varchar(1), last varchar(20), address1 varchar(32), city varchar(20), state varchar(2), zip varchar(10), index idx_zip (zip), index idx_callsign (callsign));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "use fcc_amateur;create table am (fccid int not null, callsign varchar(8) not null, primary key(fccid), class varchar(1), col4 varchar(1), col5 varchar(2), col6 varchar(3), former_call varchar(8), former_class varchar(1), index idx_callsign (callsign), index idx_class(class));"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

    echo "use fcc_amateur;create table hd (fccid int not null, callsign varchar(8) not null, primary key(fccid), status varchar(1), index idx_callsign (callsign), index idx_status (status) );"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}

  ;;

  removedatabase)
    echo "drop database fcc_amateur;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;

  removetables)
    echo "use fcc_amateur;drop table if exists en;drop table if exists am;drop table if exists hd;drop table if exists en_temp;drop table if exists am_temp;drop table if exists hd_temp"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;

  zipcodebatch)
    echo "use fcc_amateur;select en.callsign,class,first,last,address1,city,state,zip from en, am, hd  where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and zip like \"$2%\";" | mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD} -N --batch | awk -F "\t" '{print $1","$2","$3","$4","$5","$6","$7","$8}'
  ;;

  dumptableen)
    echo "use fcc_amateur;select * from en;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;

  dbcount)
    echo "Table en count: "
    echo "use fcc_amateur;select COUNT(fccid) from en;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "Table am count: "
    echo "use fcc_amateur;select COUNT(fccid) from am;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "Table hd count: "
    echo "use fcc_amateur;select COUNT(fccid) from hd;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    echo "Table hd status counts: "
    echo "use fcc_amateur;select status, count(fccid) from hd group by status;"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;

  -h | --help | help)
    echo "Help:"
    ;;

  -v | --version | version)
    echo ${VERSION}
    ;;

  like)
    CALLSIGN=`echo $2|sed y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUVWXYZ/`
    echo "use fcc_amateur;select en.callsign, am.class, full_name, address1, city, state, zip, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and en.callsign like  \"$CALLSIGN%\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
  ;;

  *)
    CALLSIGN=`echo $1|sed y/abcdefghijklmnopqrstuvwxyz/ABCDEFGHIJKLMNOPQRSTUVWXYZ/`
    echo "use fcc_amateur;select en.callsign, am.class, full_name, address1, city, state, zip, former_call from en, am, hd where en.fccid=am.fccid and en.fccid=hd.fccid and hd.status=\"A\" and en.callsign=\"$CALLSIGN\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    #echo "use fcc_amateur;select * from en where en.callsign=\"$CALLSIGN\";"|mysql --user=${MYSQLUSERNAME} --password=${MYSQLPASSWD}
    exit 0
esac