[MyHosting.com]   [KO4BB Home Page]   [Manuals Home Page]   [KO4BB Wiki]
 

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

ham_radio:ham_database_mysql [2013/01/12 19:00]
ham_radio:ham_database_mysql [2013/01/12 19:00] (current)
Line 1: Line 1:
 +====== HamLookup Database (Mysql) ======
 +
 +//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. Finally the database offers a free and effective API (Application Programming Interface.) The database is intended to be maintained primarily by the users.//
 +
 +//For a quick look at the HamLookup database page, click here: [[http://​www.ko4bb.com/​hamlookup/​]].//​
 +
 +===== Why? =====
 +
 +//Warning: rant mode was on while I wrote this paragraph. If you just want to know what the HamLookup database can do for you, skip this paragraph and go to "​What"​ directly ...//
 +
 +I have been frustrated by the absence of truly freely accessible on-line ham database. There are several on-line ham databases, and most offer some sort of free web-based access, but most owners have made accessing their databases through a software Application Programming Interface (API) more and more difficult.
 +
 +The reason is probably that they have found enough people willing to pay to get access to this data, and therefore they now charge everybody for accessing it.
 +
 +The story goes like that: it costs money to run servers to provide this service, so they have to put ads on the web page, and when you access the data through a program, you do not see the ads, therefore they make no money and cannot pay for the servers.
 +
 +I have several problems with that line of reasoning. I have been hosting a free test equipment manual service on the ko4bb.com web site for almost 10 years. Last year, the average monthly volume of data served through ko4bb.com was 170GB. This service costs me $6.00/​month,​ and that was good for 1 terabyte of data per month until they totally removed any limit on it last year, so while bandwidth is not totally free, it's at most very inexpensive. Please note that I am aware that servicing a single 10MB request is not the same as servicing 10,000 1kB requests, so I am fully aware and I expect to have to move this function to a higher grade service. I provide this service for free, because I like to get free stuff, so I do not mind returning the favor when I can. $6/month is cheap feel good, and I have learned a lot and made many friends through this project. I do not make money off it. I have thought about having a Paypal Donation button on my web site, but am trying to resist the temptation until I am really broke (I am only virtually broke at the moment, since the 401k money that has gone away in 2008 was not really mine, at least until I was old enough to collect it anyhow...)
 +
 +The actual amount of useful information transferred through a ham database transaction is about 1kB in the worst case, more like a few hundred bytes in average (name, address, and a few ham-related pieces of information such as grid and whatnot), so 170GB of data is equivalent to at least 170 millions lookups per month. I am not sure how much traffic sites like QRZ.com experience, but if they had 170 million callsign lookups per month, all they would need is a $6.00/month service to provide it.
 +
 +The problem with the ad-supported service is that by the time the server delivers all the little blinking icons and other sales and marketing gimmicks, instead of 1kB of data, they send you about 50kB of data or more. Then they also have to have the forums, the for-sale stuff, anything they can think of to drive traffic to the site because that's how they make money. Finally, all the locks and bolts they have to prevent access to their service (registration,​ login, ssl, session keys and the like) require more resources that are now tied up and not available to actually send you the data you are looking for. In sum, the problem is largely self-inflicted because of the desire to make money off of it.
 +
 +I do not have a problem with people wanting to make money. Heck, I like to make money too, but in my opinion, there are a lot of people who would like to just be able to access amateur radio operator information such as name, address, QSL route, email and other geographic or award related information,​ and have no interest in what's on sale at HRO this month.
 +
 +More importantly,​ while we all have to buy our radios (or buy the parts to build one), one of the things that attracts a lot of people to ham radio (or used to) is that the cost to actually operate this hobby on an on-going basis is (or was) low. That means no monthly or yearly subscription to a QSL information service.
 +
 +Keeping in mind that I am not a lawyer, it is my opinion that the information we are after is somewhere between public domain, and the property of the ham in question. It is not the property of some web-based company. I am more than a little miffed that some company charges to provide information I voluntarily entered into their web site because at the time accessing that information was free, or because they got it when they downloaded the free FCC database. I would like to return the control of that information where it belongs, in the hands of the user.
 +
 +My intention is to keep this service free for users forever. If I have to go to a commercial grade server, I may stick a Paypal donation box somewhere for those who feel like contributing,​ but I will never require a *subscription* or *payment* in order to access it. If it gets to be too expensive or time consuming, it is my intention to donate it to a club or DX association. It is not intended to be competition for the *commercial* services as there will be no other service or feature associated with this database. No ads, no forum, no for-sale, just a ham database. Since the commercial services claim that ham lookup uses too much of their bandwidth/​resources to make it free or wide open, I believe they should welcome this development. It will make their servers more available to send ads.
 +
 +===== What? =====
 +
 +The Alpha version of the HamLookup Database is accessible at http://​www.ko4bb.com/​hamlookup/​
 +
 +The purpose of the HamLookup Database is to provide QSL information and other information necessary for awards to hams everywhere, 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 a callsign and select Edit to the left of the record you want to modify, then click the Submit button. Please note that while you can select multiple callsigns, if you select Edit, only the first selected callsign will be opened for edit. However, you can delete multiple records at once with this feature. I may remove the Delete feature later as it may be dangerous (there is no "​undo",​) but for testing now it is convenient.
 +
 +To create a new entry from scratch, click the Create New Entry button in the top row.
 +
 +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 whatnot) 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 "Enter 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, the main reason for requiring registration is to make sure there is a valid email address that can be associated with a log upload. For now, the only thing that is always stored is the IP address of whoever makes a change in the database.
 +
 +The HamLookup database structure is not complete (missing 10-10, oblast and a few other fields I am sure). 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) 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 QSO date and the database will return the record for that date range (if specified). 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>​\\
 +''​
 +
 +Please note that 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.
 +
 +The date field is optional, without it, the query would look like:
 +
 +http://​www.ko4bb.com/​hamlookup/​xml.php?​cs=ko4bb
 +
 +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 difference 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 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? =====
 +
 +The HamLookup Database runs on php code accessing a series of MySQL databases, on my "home grade" service contract with 1&1 (http://​1and1.com) (www.ko4bb.com,​ $6/month), with some unspecified limitations on how many MySQL connections I can have and how much I tie the server up. If this project is successful, that may not last very long, and I have started asking questions about what are the actual limitations and what will be the next grade of service I may need. However, strictly based on data transfer, the service I subscribe to now should be good for a long time. The code is generic PHP and it uses a generic MySQL database, so it would be very easy to move this functionality anywhere. I will probably buy a new domain for it at some point anyhow. In my 3 or 4 years with them, 1&1 has provided excellent service. They have been very reliable and data transfers are very fast.
 +
 +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 main ham table is actually spread over 10 independent databases, to spread the load during heavy use and to overcome the size limit of each database imposed by my ISP. The total capacity available over the 10 databases is 1GB. I can access a total of 25 databases with my contract, so I have room to grow. The FCC database of all US hams (1 million records) uses about 100 MB of space.
 +
 +The 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 public IP address from which the upload is made. Most likely, this is the IP address assigned to your cable or DSL modem as assigned by your ISP. Finally I also record 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
  
 
ham_radio/ham_database_mysql.txt ยท Last modified: 2013/01/12 19:00 (external edit)
 
Recent changes RSS feed Creative Commons License Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki
Except as noted, this entire site Copyright © 2002-2017. KO4BB All rights reserved.