Manuals, Timing, Ham Radio, Test Equipment

Help keep this site free:
(More Info)
        

Hamlookup Ham Radio Database

This article describes a new Ham Radio Database. The HamLookup database contains mailing addresses and other ham radio related information such as grid square, DXCC country, US county and other which can be used to obtain QSL cards and awards. Several factors set this database apart from the better known ones. This project is purely non-commercial (with an uncluttered presentation). This database offers the capability to store multiple addresses for a given callsign, to reflect the fact that ham radio operators do move and special event callsigns may be reused by different teams at different times and therefore may have multiple, different mailing addresses. The database offers a free and effective API (Application Programming Interface) using the xml language that is free to use without the need to register. Information in the HamLookup database is intended to be maintained primarily by its users.

What is it?

The Alpha version of the HamLookup Database is accessible at http://www.ko4bb.com/hamlookup/index.php

The purpose of the HamLookup Database is to provide address and other information necessary for hams everywhere to obtain awards and QSLs, and to complete log information when participating in contests, no less and no more.

It is a work in progress, and it is done generally under the Open Source spirit, if not the letter for now. Eventually, the code will be released, but it is just too fluid at the moment.

The HamLookup database has basic web-based editing interface capability: lookup, add, modify, delete, what I would call the "management" interface, and an API for XML and ADIF formatted search queries. The XML API seems to work with Logger32 with just the right configuration parameter, however, the existing Logger32 interface does not take advantage of one of the more interesting feature of the database.

The HamLookup database supports multiple entries for each callsign. There are many cases where this is useful, because people move and callsigns are eventually recycled. It is good to be able to lookup someone's information as of, say, November 5, 2005, to validate an old QSO for which you may need a QSL. You can see an example of that if you look for my callsign: KO4BB. It will show the two places where I have lived since becoming a ham. The search is not case sensitive, so looking for "ko4bb" is the same as looking for "KO4BB"

I anticipate this feature will be also very useful for DX operations where the same callsign may be used by different teams over and over again, and the QSL route may be different each time. Also, many hams move during their ham career and most countries do not require them to change callsign when they do, and this may affect eligibility for awards, so it is important to be able to track the addresses over time. You may use it to record a location where you operated from during a contest or holidays.

Please note that you can also search the database on a name or partial name. This is done automatically if the program does not find a callsign matching your query string. In this case, it will look for a name that contains the search string. For instance, if you search for "juges", it will return several records (my son and I for now). Unlike the name search, the callsign search requires an exact match (i.e. searching for KO4B will not find a record for KO4BB). There is no search on other fields for now. If the need comes, that can certainly be added.

There are two ways to enter data into the database.

The user can enter/edit his or her own information one record at a time using the web interface, and/or manage other ham's information.
The user can upload a large amount of data at once using the Upload Log button in the top row.
To edit information about a callsign, enter the callsign in the box and click the Search Database button. Once you see what's there, select the entry you want to edit, then click the Edit button. Please note that while you can select multiple callsigns, only the first selected callsign will be opened for edit.

To create a new entry from scratch, click the Create New Entry button in the top row. You cannot use an existing entry to create a new one.

The Upload Log function is used as follows: if you happen to be using computer logging software, and you have recorded the name and address of at least some of your contacts, you can most probably export and save the entire log as an ADIF file. That file can be uploaded to HamLookup using the Upload Log button. When such a file is uploaded, the program strips all QSO information (date, time, reports, frequency and what not) and only keeps the name, address, grid, county and other geographical information from the standard ADIF (or XML) fields that are available. Records which do not contain at least a name and an address are not used. All other information is optional.

The Upload Log function supports two file formats: ADIF and XML. The ADIF format is supported by most amateur logging software. The XML format is more general but less common in ham software.

The Upload Log function rejects identical entries, so there should be no problem (other than a waste of CPU cycles and your time) if you upload the same file again and again (on purpose or by accident).

Since the date feature is not supported (that I know of) by other popular on-line ham databases, the date information is likely not available anywhere and will have to be entered manually by the holder of the callsign or his/her representative, unless someone comes up with a better idea.

All changes are done using the honor system, in the spirit of cooperation, and there is no desire or attempt at keeping someone out of someone else's record, a la Wikipedia (except that some Wikipedia articles are moderated). Users are of course expected to enter their callsign in the "Your callsign" box when they make a change, but even that is not required. Unlike Wikipedia and most other sites, there is no need to register before being able to make changes. That policy may change later if I find that this service is being abused. When registration is implemented, at least there is a valid email address that can be associated with a log upload or a record modification. For now, the only thing that is always stored is the IP address of whoever makes a change in the database.

As of January 2012, the HamLookup database structure is almost complete. The intent is to support all the main awards, so feel free to make suggestions for additional information that should be recorded.

The date feature uses the fields called "start" and "end".

These fields are intended to contain the range of dates where the entry is valid. You can see an example with my callsign. I moved in 1994. This is one problem with the existing on-line databases (QRZ.COM, Hamcall, HamQTH) in that they only have the capability of one record for one callsign, so if the callsign is reassigned, or if the operator moves, you get the wrong answer and they is no way to resolve it.

With the HamLookup database, the user has the capability (via the API) to enter a callsign and a specific date and the database will return the record for that date (if that date falls within a range that has been loaded in the database). If no date is specified, it will return the most recent entry for that callsign based on the "start" field, not based on the date when the record was added or revised. If there are several records and they all have a blank "start" field, the first one found is returned, which is not necessarily the first one you see on the screen when doing a query. When you do a query from the web interface, all callsign matching records are returned, and the date range for each record is displayed, so you can choose the address for the date you are interested in.

You can see this in action with the following API query (the date format is YYYY-MM-DD).

http://www.ko4bb.com/hamlookup/xml.php?cs=ko4bb&date=2005-12-31

This query will return something like the following html/xml code (please note that I have listed ALL the fields that could be returned, some with dummy data but in actuality only those field that contain information (not blank) are returned):

<html>
<body>
<?xml version="1.0" ?>
<HAMLookup version="0.1" info=" http://www.ko4bb.com">
<qsl_info>
<callsign>KO4BB</callsign>
<name>Didier Juges</name>
<addr>47 Magnolia Ave, Shalimar, FL32579</addr>
<country>USA</country>
<dxcc>UNITED STATES</dxcc>
<cnty>Okaloosa</cnty>
<grid>EM60ql</grid>
<itu>7</itu>
<cq>5</cq>
<iota>NA-234</iota>
<1010>xxxxxx</1010>
<rda>xx</rda>
<jcc>xxx</jcc>
<jcg>xxx</jcg>
<pref>xxx</pref>
<dok>xxx</dok>
<qsl_via>xxxxxx</qsl_via>
<email>didier@qrz.com</email>
<start>1994-11-1</start>
<updated>2010-08-04</updated>
<updater>KO4BB</updater>
</qsl_info>
</body>
</html>

To display the xml tags, use your browser's View Source menu.
Only non-blank fields are returned (except for name and address). Name and address are always returned, even if blank, however, they should never be blank, as the name and address is the primary purpose of this database.

Now try this query and see that the different date returned a different address:

http://www.ko4bb.com/hamlookup/xml.php?cs=ko4bb&date=1990-12-31

The date field is optional, without it, the query would look like:

http://www.ko4bb.com/hamlookup/xml.php?cs=ko4bb

This query would match the first record with the matching callsign, regardless of date.
Searches are not case sensitive.

The ADIF access is similar and returns a slightly differently formatted string, but the date feature is not yet supported on that query:

http://www.ko4bb.com/hamlookup/adif.php?cs=ko4bb

This query will return something like the following code (here also, all fields are listed):

<html>
<body>
<HAMLookup version="0.1" info=" http://www.ko4bb.com">
<CALL:5>KO4BB
<NAME:12>Didier Juges
<ADDRESS:36>47 Magnolia Ave, Shalimar, FL32579
<CNTY:8>Okaloosa,
<GRIDSQUARE:6>EM60ql
<EMAIL:14>didier@qrz.com
<QSL_VIA:5>KO4BB
<START:9>1994-11-1
<END:9>2013-01-01
<UPDATED:10>2010-08-04
<UPDATER:5>KO4BB
<EOR>
</HAMLookup>
</body>
</html>

There is no capability to check that date ranges entered in the database are valid. They are not even required. If there is no end date, the assumption is that the address is still valid today. If there is no start date, that means that the entry was valid since the beginning of times. I may add date-checking code later if I find a way to make it easy to manage and understand. Therefore, in this database, multiple entries for a given callsign are allowed, you can create multiple entries for a given callsign if you want to, even though identical entries are not allowed (i.e. for an entry to be accepted, it must have at least a one character different with an existing entry).

I am in the process of automatically incorporating the FCC database's weekly updates for US hams. The FCC also provides daily updates for the most current data, and I may support this in the future.

Errors?

Here is a quick guide to the errors you may come across.

Most errors will have to do with the Upload Log function.

When uploading log files, the program looks for a specific type of header, depending on the type of file being uploaded (ADIF or XML). The header should be found in the first 15 lines of the file (including optional blank lines.)

For an ADIF formatted file, the program looks specifically for a record containing the string "<EOH>" (without quotes.)

For an XML formatted file, the program looks for a record containing the string "ADIFLookupDB" (without quotes.) These files are generated by my own ADIFLookup program.

If no valid header is found, the upload is canceled with an error message.

Once the header is found, the program parses the information and will enter a new record if it contains at least the following fields: (name AND postal address) OR a qsl manager OR an email address, and if certain fields are not identical to those in an existing record. The following fields are checked: callsign, name, address, country, dxcc number, county, grid, qslmgr and email.

The program will also generate error messages if it cannot access the database, but there is nothing you can do about that other than try again later and notify me if it continues.

When uploading a file, the program looks for US County information formatted as follows: "SS,Cccccccc", "Cccccccc,SS" or "Cccccccc" where "SS" is the two letter state abbreviation and "Cccccc" is the county name. If the state letters are not provided, the program will use the state from the address if provided, or generate an error message if the state is not found.

The following keywords are searched for:

for ADIF files: "<CALL", "<NAME", "<ADDRESS", "<COUNTRY", "<DXCC", "<CNTY", "<GRID", " <IOTA", "<ITUZ", "<PFX", "<EMAIL", "<START", "<END", "<UPDATED", " <UPDATER", "<UPDATER_IP"
for XML files: ' <callsign>', '<name>', '<address>', '<country>', '<adif_country>', ' <county>', '<grid>', '<iota>', ' <ituz>', '<pfx>', '<email>', '<start>', '<end>', '<updated> ', '<updater>','<updater_ip>'
Note that not all fields listed above are currently stored in the database, but it is coming.

How does it work?

The HamLookup Database runs on php code accessing a MySQL database.

There is backup capability for the databases, but it has to be done somewhat manually at the moment. That too will be automated.

For now, there are four tables: the ham database itself (which I expect will be maintained by users and helpers), a US County table (from the one available at http://www.countyhunter.com/), a DXCC/ADIF Country table (from the official one at http://www.hosenose.com/adif/adif.html) and an upload log. The US County and DXCC Country tables are maintained locally (by me) as semi-colon delimited flat files and uploaded to the server when changes are required, there is no web-based management of these tables for now, and they cannot be queried independently.

The ko4bb site does not keep much information other than the database itself. The following information is recorded: file uploads (filename, date, time), the number of records processed, the number of entries created, the number of duplicate entries and the number of entries that are not used because they have no name/address/QSL Manager info. I also record the IP address of the computer (or home router, or your ISP's) which uploaded the file, and the callsign and email of the person doing the upload, if provided.

There is no intention to log the individual edits made through the web interface. I also do not log queries (that would be silly), however overall queries are tracked by the Apache web server's logging facility, so global statistics on usage of the service will be available.

The whole thing is quite speedy. I uploaded a large XML file from my own log (several times) and it took between 5 and 40 seconds (depending on time of day and server load I presume) to process all 13,000 entries.

All comments and suggestions are welcome, including a different name for this service.

Didier KO4BB