A Southern California Guide to the FCC Universal Licensing System

Wayne Smith   [ n6lhv@arrl.net ]

Creative Commons License
The SQL, scripts and text on this web page are licensed under a Creative Commons License.

[data download date: Mon 01/28/2013 ]  
[web page updated: Thursday, May 31, 2012]  

Passion holds up the bottom of the universe and genius paints up its roof. -----Chang Ch'ao


The purpose of this document is to describe how I utilize the public access databases available originating from the Universal Licensing System (ULS). The ULS system is managed by the Wireless Telecommunications Bureau (WTB) of the Federal Communications Commission (FCC). To the extent that understanding radio systems is enhanced by a rich knowledge of the ULS, this document may benefit others as well. The approach, methodology, technologies, and results detailed within this document are intended to be informative only. Normative questions regarding the database content, database structure, or more generally, suitability to any given database application, should be directed to FCC ULS Technical Staff at ulscomm@fcc.gov. Other than the modest documentation provided by the FCC {uls}, the literature on the ULS is relatively sparse (see, for example {abernathy}; {martin}). And finally, "public" data is probably best served by "open" data policies (e.g., architectural standards), practices (e.g., file formats), and procedures (e.g., access mechanisms). For an interesting essay on this subject related to NOAA weather data, see {schwartz}. Note also that the Federal Government does have a reasonably sophisticated interface for Census data. This web-based interface is affectionally refered to as "Data Ferret". And while "DataFerret" can access some public data beyond Census data, much public data (including the FCC ULS) is not available via this (bulk-extract) interface.

This document is for individuals desiring an detailed description of the FCC ULS databases at the level of technical mastery of data and technological mastery of databases. In particular, this document deals with the three main Land Mobile license databases, Land Mobile Private (LMpriv), Land Mobile Commercial (LMcomm), Land Mobile Broadcast (LMbcast), Coast (coast), and the Coast (Microwave) license database. Individuals desiring ULS data without significant end-user manipulation can obtain such information from several for-profit organizations (e.g., PerCon). Individuals desiring summary analyses of data and qualitative commentary on regulatory policy can obtain such information from many for-profit organizations (e.g., Pike and Fisher). Maybe most useful to hobbists and some professionals, Rich McVicar volunteers his time to provide weekly summaries (email-based and web-based) of the changes in the Land Mobile databases at FCC-Grant-CA. Achieving a comprehensive understanding the data stored in ULS databases is apparently non-trivial. Most strikingly, even FCC field offices feel the need to procure at least some fraction of the FCC ULS data from private vendors (see, e.g., {perconwebpage.pdf}).

Although it may at first appear to be counterintuitive, the requirements for some "hobbyist" activities may exceed those for some "professional" activities. The current (or previous) FCC ULS search functionality is probably adequate for relatively straightforward queries, such as when the precise frequency or precise callsign is known. In some cases, the point radius option of the "Location" query (within the current system), based upon the latitude and longitude of the transmitter, may also be helpful. However, I have often found the current ULS search application to be inadequate for recurring radio system activities, much less professional spectrum management and interference avoidance. My speculation is that this inadequacy is due less to the inherent design of the ULS system per se and more to the unique characteristics of Southern California. Due partly to the geographical size of California and partly to the diversity of the populated communities within California, understanding the radio system licensing details of California, particularly Southern California, is non-trivial. At a minimum, the sheer volume of licenses, much less the sublties of the data relationships and inadvertent anomalies, can be intimitating. And finally, note that amateur radio operators have long played a key role in the development of many different aspects of land mobile radio, especially "repeaters" and other land mobile-related relay systems. See, for example, the pioneering work of the late Art Gentry (W6MEP) in the 1950's {Pas04}. It may be of some benefit for those within the affected service region to contribute to the body of the knowledge measured and managed at the national level by the FCC.

Many of the queries in this document relate to Los Angeles in general, occasionally the San Fernando Valley specifically. This approach is generally aligned with another document from this author {valleyscan}. More important, however, is that California, the Southern California counties, the County of Los Angeles, the City of Los Angeles are so large in comparision to their geographic couterparts elsewhere in the country, that the full richness of the ULS databases can, in fact, be explored with a high degree of rigor. The techniques, technologies, and tools described in this document should not only be generalizable to other geographical regions, but also to other ULS professional and research contexts. Within the exception of maybe New York, Florida, Illinois, and Texas, the sizes of the non-California ULS databases, and therefore the vagarities of database management and manipulation, not to mention radio systems management and interference mitigation, are relatively small and more manageable by comparison.

The primary database technology described in this document is MySQL. My observation has been that productive use of the FCC ULS databases is not a function of specific database platform choice. However, an open-source technology approach may tend to be more inclusive and replicable than a closed-source (proprietary) alternative. Most of the datasets described in the later sections of this document can be used with many types of computer applications, including in most cases, modern database and spreadsheet applications found on a typical desktop or notebook computer. In order to perform all of the procedural steps below, I recommend allocating at least 10 GB of available (and unfragmented) disk space.

Words in braces ("{}") are references (listed at the end of this document). Words in brackets ("[]") are links (i.e., external to this document). Additionally, I have strived to make this document accessible to all users, including the physically challenged. If the format of this document is not conductive for access, use, or understanding by all individuals, please let me know [ n6lhv@arrl.net ] and I will alter it as needed. As a final note, I am interested in feedback from individuals who have found this document to be of some benefit in their personal or professional radio activities.

Thanks, and happy monitoring.

Wayne Smith, N6LHV

Automating the entire process

The master ULS databases are updated weekly each Friday afternoon (Washington D.C. local time). This information is helpful for those who wish to automate the download with cron (non-Windows) or AT (MS-Windows). The ULS databases are also updated daily; Tuesday morning through Saturday morning {pa_intro9}. These "daily transaction" databases are in essentially the same format as the "master" databases and as such, are not described in detail in this document. "Daily transaction" databases could be derived from the "master" databases as needed or used in place of the "master" databases at nearly any step in the data process. From a functional and technological perspective, possibly the best use of the "daily transaction" files is to transform them into various XML formats, especially subscription-centric formats such as Atom and RSS.

Automating the entire process (scripts)

There are many steps in database processing. I suspect most individuals would prefer to automate these steps to the largest degree possible.

Note: In terms of compatibility at the operating system-level, these scripts should be relatively flavor-agnostic (non-Windows) or version-agnostic (MS-Windows) respectively.

Downloading ULS LM data

In the FCC taxonomy, the Land Mobile Private (LMpriv) database is the database of licensees who are also the end users of a system. The Land Mobile Commercial (LMcomm) database is the database of licensees who own and manage systems that provide a for-profit service to end users. The Land Mobile Broadbast Auxillary (LMbcast) database is the database of licensees who own and manage traditional broadcast-oriented systems. Beyond the Land Mobile licenses, there are other several other licensed databases as well, some of which may be of use to radio hobbists and professionals, such as the Coast database. Also, recall that some radio systems do not require a license at all (such as narrowband Family Radio Service or broadband Wi-Fi).

Downloading ULS LM data (scripts)

A useful tool for automating the download of files on the web is wget. In a nutshell, "wget" is a web browser without the graphical interface.

Note: I can't seem to get more than about 900 KByte/sec file transfer rates {wgetlog} from the FCC web site. It is possible that the network connection at the FCC is rate-limited either at the network edge or on one or more specific LAN segment(s). It may also be possible that the throughput for a single connection to the web server has been throttled downward in order to service a larger, or more likely, peak, number of public web users. This limitation, while not significant, requires more investigation.

Extracting ULS LM data

Each master ULS database is really a collection several different database "segments". In terms of the public access portion of the ULS, each of these "segments" is provided in a different ASCII text file. Each collection of ASCII text files, including the segments that comprise the Land Mobile databases, is "zipped". This "zip" format essentially provides two basic features--1), a "zip" file is a package that combines several small files into a single large file, and 2) a "zip" file is highly compressed.

Extracting ULS LM data (scripts)

A useful tool for extracting "zipped" files is gzip. Although gzip has been ported to MS-Windows, MS-Windows users often use other popular "zip" tools as well, including versions of 7-Zip, PKZIP, Info-ZIP, or WinZIP.

Note: Compared to the sizes of their "zipped" (compressed) counterparts, the newly "unzipped" (i.e., "uncompressed" or "expanded") Land Mobile files will consume approximately six (6) times more disk space. For example, as of August 1, 2004, the (compressed) size of l_LMpriv.zip is about 168 MB; uncompressed, the sizes of all of the individual files total to 1.01 GB (or equivalently, 1,010 MB). In principle, if disk space was at a premium and one was absolutely sure which of the fields were needed, one could import only those ASCII files that contain the desired fields. In all but the most trivial cases, the number of ASCII files cum tables needed will still no doubt be larger than one, therefore requiring a (database processing-intensive) SQL "JOIN" step anyway.

Note: The "zip" file also contains a text file named "counts". This file lists the number of lines in each file. After the import process (the step following the next immediate step), the figures in this "counts" text file can be compared with the number of records in each table (See for example, {describedatabase}).

Note: Again, these scripts should be relatively indifferent to various operating system platforms.

Preprocessing the "flat file" (text files) data

The FCC distributes the "text" files without eliminating embedded CR/LF's in in the middle of fields. This leads to a number of issues, not the least of which is a difficult import process into a database. There is not necessarily a one-to-one correspondence between the number of the lines in the ASCII file and the number of records, a non-trivial problem that most DBMS "import" utilities (including the bulk loader in MySQL) cannot deal with effectively. I've communicated with the FCC ULS team on this matter, but this situation is apparently something that the FCC feels is outside of the scope of their responsibilities, presumably because the ULS data is "self-reported". See [ HD0000000328491.pdf ].

Preprocessing the "flat file" (text files) data

In addition to the preprocessing needed to "fix" the embedded CR/LF's, I also delete the blank lines before the import process and append the name of originating database to each line in each file as well.

Note: These "fixes" are probably not optimal, but they are effective in the short-run.

Creating the DB and the Tables

The FCC provides a sample SQL file with the specific table definitions {pa_ddef18}. I modify the data definitions in this file partly to rectify the anomalies in the SQL syntax, partly to alter the field types to match the capabilities of the MySQL database management system, and partly to add indexes that will be needed in subsequent steps. I find it useful to create indexes on the "unique_system_identifier", "ULS_file_number", "callsign", "state", "county", and "city". Note that while the "unique_system_identifier" field is named consistently between tables, the precise names of the "ULS_file_number", "callsign", and "state" fields varies slightly between tables.

Creating the DB and the Tables

For this MySQL SQL query (and the ones that follow), I typically used redirection to not only provide the query engine with the specific query procedure, but also to capture the query process log. For example, the MySQL query below was run with the following command "mysql -vvv <createdbandtables-mysql.sql >createdbandtables-mysql.log" (the "-vvv" option of mysql command-line client enables verbose messages, including warnings). It is likely that the SQL Data Description Language (DDL) used to create the databases and tables will vary slightly among database management systems.

Note: Since the purpose of downloading ULS data is for data querying (decision-support) and not data submission (transaction-support) per se, the need to ascribe primary and foreign keys, integrity constraints, and other structural formalisms to the database is somewhat minimized (although some database purists might disagree). Those desiring a more robust (and potentially more efficient) database design are encouraged to augment the resulting table structures with traditional relational modeling techniques as needed.

Note: There are some emerging approaches to handling the automatic import and export database schema and SQL Database Definition Language (DDL) statements across multiple database approaches. See, for example, SQLFairy (a play on the term "ferry") Perl modules.

Importing ULS LM data

The key step in the extract, transform, and load workflow for the ULS data is the import process.

Importing ULS LM data (SQL DDL "bulk load" commands)

The bulk load command, if available in a database management system, is probably faster than the equivalent SQL "INSERT" commands. However, one advantage of the SQL "INSERT" command however, aside from being generically more cross-platform, is that one could add a "WHERE" clause to the command to include or exclude specific records (e.g., to import just records relating to California or between a specific frequency range).

Note: Compared to the sizes of their file-based counterparts, the newly imported Land Mobile database tables will be approximately twice as large. For example, as of August 1, 2004, the size of the "unzipped" (i.e., uncompressed, or expanded) Land Mobile Private files is approximately 1 GB; imported into a relational schema, the size of the resulting database (in MySQL) grows to almost 1.8 GB {describedatabase}.

Note: The import results described in the ".log" files can be helpful. For example, the number of records imported into a specific LMpriv table can be compared to the number of lines in the LMpriv "counts" file as a cross-check on the number of records that were imported successfully.

Note: It should also be possible to write a script in say, Perl or Python, to automatically generate (and execute) the correct SQL on a platform-by-platform basis, but this document does not describe such a script.

Frequency Distributions

Determining the scope and magnitude of the various anomalies in the data can be assisted by reviewing the frequency distributions of each field.

Frequency Distributions

At some point in the database workflow, a conscious decision needs to be made as to how to handle the various "anomolies" and inconsistencies in the ULS data (see below). One could generate queries to replace incorrect or misspelled data, or one could adjust the queries (probably by augmenting the WHERE clause) to ensure that the desired query remains collectively exhaustive and mutually exclusive.

Note: The results of such a query are too bulky to be shown here. Suffice to say that having the frequency distributions of each field in a table, ready to be queried or "looked-up", is useful.

Deleting Unwanted Records

Although a WHERE clause can always be added to a query to constrain a future query, if some records will never be requested, then they might as well be deleted.

Deleting Unwanted Records

The definition of "land mobile", or rather, the definition of "land mobile bands" may be changing. At a minimum, as the 700 MHz public safety allocations come into usage, it may be prudent to look all of the allocation (regardless of "radio service type") in and around these new allocations (including 220 MHz).

Currently, I don't need the non-State of CA records (although, of course, I would for the "all US" query). My determination of what is State of CA record is not just whether the "transmitter" location is "CA", but regardless of the "transmitter" location, whether the "control" location is "CA" or "license" location is "CA". Others may reasonably differ on this determination. I prefer the more broader listings. I also delete the listings for Nextel, as these transmissions cannot be monitored with consumer-grade scanning equipment.

Creating Indexes

The most important use of indexes in this application is to speed SQL Table JOINs. Naturally, other indexes can be applied at this step in anticipation of the specific needs of subsequent ad hoc queries. For example, even the current FCC ULS query application offers geographical granularity no deeper than the County-level (within a State), and even then, this level is not available in all queries. By way of an example, the County of Los Angeles is larger than all but approximately nine of the States {censuspop}. Additionally, the City of Los Angeles is not only larger than most Counties in the U.S., the City of Los Angeles is also larger than approximately half of the States {censuspop}. The current FCC query system may simply not be granular enough for the greater Southern California region.

Creating Indexes

Creating the appropriate indexes is an iterative and incremental process. Even with good schema meta-data and modeling (only a fraction of which is provided publicly by the FCC), the use of each index should be vigorously tested in practice. For this ULS application, the proper use of indexes leads directly to the speed (and in some case, merely completion) of query results, and therefore, overall useability and emergent functionality.

Note: Again, it should also be possible to write a script in say, Perl or Python, to automatically generate (and execute) the correct SQL on a platform-by-platform basis, but this document does not describe such a script.

Generating a "flat" table for recurring use

As provided by the FCC, the ULS tables are structured in a relatively efficient manner. However, the data of most interest to scanner radio hobbists is located in more than one table. Therefore, at least one large, multi-table JOIN operation will need to be performed. Occassionally, I find it more effective to process a single, large JOIN once and then subset various datasets from this new "master" file as needed. However, more complex queries (or more likely, unanticipated queries) will almost always require additional fields, some of which originate in other (non-JOIN'ed) tables. Whether it is more efficient for subsequent queries to simply subset from an existing JOIN'ed table or to continue to process more brute-force JOIN's from the original "imported" tables depends on a number of contextual factors. It is possible that by using SQL "views" properly (e.g., in MySQL 5.0), this issue can be avoided all together.

Naturally, individuals may differ as which fields are the most useful in any given context. My field choices are based partly on my thirty years of experience in the scanning radio hobby and partly on personal speculation regarding what queries not available on the FCC ULS search page may be of some productive use to me in the future. For example, I often need to be able to search within a field, particularly within the "license_id" or "entity_name" field.

I don't current run an "all US" query. Such a query crashes on my (admittedly old) desktop system. It is also possible that I have not yet "tuned" MySQL to accomodate large queries.

Generating a "flat" table for recurring use

Note: The "California Only" data in an MS-Access database format is shown below.

Note: Even on the best of hardware, this query may take some time to complete. Most uses of ULS data, at least hobbyist uses, probably don't require data that encompasses the entire United States. I usually either physically delete, or logically ignore, non-CA records (but see "Errors and Ommissions" below regarding "non-blank State" records. In MySQL, increasing the size of the "key buffer" parameter from 5MB (the default) to 64MB seems to be optimal.

Note: Compared to the sizes of their non-JOIN'ed counterparts, the newly JOIN'ed tables are approximately 2.2 times larger. For example, as of August 1, 2004, the size of the non-JOIN'ed tables is about 1.8 GB; JOIN'ed, even with fewer fields, the size of the JOIN'ed tables (in MySQL) is about 3.9 GB {describeJOINedtables}. Limiting the number of fields returned in any given query is prudent.

Note: The "EXPLAIN" keyword in a "SELECT" query reports "how" the SELECT would proceed, in particular, which indexes would be used for which purpose. This is important given the relative sizes of the ULS database on typical desktop hardware. For our purposes, the two most important columns in the MySQL "EXPLAIN" output are probably the "Key" column and the "Rows" column. ULS users will want to ensure that the value listed in the "Key" column is indeed the correct index (generated with index procedure described previously. Additionally, the time to complete a query is approximately proportional to the the product (i.e., multiplication) of all of the values in the "Rows" column. Note that in some of the queries that follow in this document, the order of the tables in the SELECT query varies. This is occasionally the best (or only, in the case of MySQL) way to ensure that the correct index is being used by the database query engine.

Note: Some database users might refer to this single, large "flat" file as an SQL "view". The file is not, strictly speaking, an SQL "view", but for all practical purposes, the file is used like an SQL "view".

Export strategies

Successful exchange of datasets of any kind requires some type of interoperable data format. Some example formats that can be generated by MySQL are shown below. There are various strengths and weaknesses to each alternative. The primary constraint for the MS-Access database is the maximum file size of 2 GB. The primary constraint for the spreadsheet format (e.g., MS-Excel) is the maximum individual worksheet size of approximately 64K rows.

Export strategies

Note: The large MS-Access database above contains some sample queries from which users can use to build other queries of interest.

Note: Since the dBase file format limits field names to 8 characters, I simply altered the name of field "location_county" to "loc_county".

Note: The Perl script above requires the DBI/DBD module and the Spreadsheet::Write module. Both are available on CPAN.

Note: The file sizes for the .zipped files are slightly understated due to an anomaly in the current version of the Apache web server. However, the downloaded file is indeed unaltered and the filesize is correct.

Note: OpenOffice.org Base (.odb) can open MS-Access (.mdb) files directly. However, for some purposes, better and more direct import and export programmatic capability is required. Towards that end, UN*X users may wish to consider using MDB Tools. More generally, see also Converter Tools.

Note: It may seem odd that all of this data transformation work is necessary just to take one set of public files and produce yet another set of public files. But I'm just not sure how to maximize the leverage of the potential of the public ULS database without this effort, at least in the short run. In the long run, XML may hold the potential to reduce some of this effort, but this even this intervention appears unlikely (and even if likely, can be relatively inefficient in terms of storage and/or transmission). Another option may be to assist the FCC ULS staff directly in building productive data access services that practical address "bulk" data use cases.

Geographic Information System (GIS) Analysis

A number of important applications benefit from the linkage of license data to geographic (spatial) data. A few simple GIS manipulations can be done with SQL queries, but most will require a more specialized software tool, such as GRASS or monoGIS. A nice set of open source GIS applications for Windows is FWTools. The source .kml files have been uploaded, but are not explicitly shown below.

Geographic Information System (GIS) Analysis

Note: The spatial extensions in MySQL 4.1x or the native geometric datatypes (and corresponding R-tree index) in PostgreSQL can be utilized for ULS data.

Errors and Omissions

An entire book could be written about the inconsistencies, irregularities, and anomalies in the ULS data. While the FCC has recently enacted additional data validation and verification techniques, the fact that the ULS data is self-reported and therefore, submitted by end-users often without basic validity checking, fundamentally leads to a dataset of questionable integrity for some purposes.

Errors and Ommissions

Note: Although the land mobile data has improved since the days of microfiche, continued due diligence on the part of all stakeholders in the area of data quality may be prudent. The probability of an anomaly, including a simple mispelling, increases with the length of the field. Queries that use long fields, especially the "entity_name" field, are particularly vulnerable to inconsistent spelling and typographical errors. Other types of errors include incorrect sites and records for systems that are no longer in use. Many other types of data integrity issues are possible as well. See {fcca-notice} for an example of a notice by a frequency coordination organization to its members pleading with them to update their license address data.

Note: One problem in particular is the inclusion of embedded CR/LF's in some of the files, most notably the pubacc_cp file. The appears to be undocumented.

Imputation for Blanks

While it is mostly likely very difficult to impute all of the blank fields, it may be possible to impute some blanks fields in a deterministic (precise) manner. The benefit is the imputation is that it leads to more complete data set for analytical purposes. The risk is that the imputation is not, indeed, accurate (but in principle, that risk should be able to be mimimized by careful study, including manual inspection).

Imputation for Blanks

Note: Although the land mobile data has improved since the days of microfiche, continued due diligence on the part of all stakeholders in the area of data quality may be prudent. The probability of an anomaly, including a simple mispelling, increases with the length of the field. Queries that use long fields, especially the "entity_name" field, are particularly vulnerable to inconsistent spelling and typographical errors. Other types of errors include incorrect sites and records for systems that are no longer in use. Many other types of data integrity issues are possible as well. See {fcca-notice} for an example of a notice by a frequency coordination organization to its members pleading with them to update their license address data.

Note: One problem in particular is the inclusion of embedded CR/LF's in some of the files, most notably the pubacc_cp file. The appears to be undocumented.

Geographical Queries

Potentially the most important type of query for scanner radio hobbiest is one that narrows the geographical scope of the ULS data.

Examples of Geographical Queries (SQL)

Note: Even a seemingly straightforward query such as the "California only" query above requires some knowledge regarding how radio systems are designed in general and where the geo-political boundries are defined specifically. For example, a better "California only" query might use two "State" fields (located, unfortunately in terms of query processing speed, in two different tables)--the first to include the records where "CA" is the State of the "license holder" and the second to include the records where "CA" is the State where the "transmitter is located" (two very different result sets). Naturally, subtle differences in query intent may lead to substantive differences in query implementation. While learning about the ULS in general, it is probably best to instantiate queries that tend to be inclusive, or rather, that tend not to inadvertently (and unknowingly) exclude records of relevance or potential interest.

Note: Given a variety of factors, not the least of which are the immutable physics of electromagnetic radiation, narrowing the ULS data table to just one or more State(s) seems reasonable. Note that sequencing the queries from a larger geographic scope (e.g., "All of California") to a smaller geographic scope (e.g., "Los Angeles City only") should enable the table and index caching capability (if any) in the database management system to speed the query results. This greatly improves the performance of the smaller geographical scope queries.

Organizational Queries

Potentially the most important type of query for a land mobile professional is one that narrows the organizational scope of the ULS data.

Examples of Organizational Queries (SQL)

Note: Even a seemingly straightforward query such as the "City of Los Angeles only" query above requires some knowledge regarding the scope of the managed assets and more important, the authority and provenance of service provision and delivery. For organizational queries, this often means wildcard-based searches particularly within the "license_id" or "entity_name" field.

Note: Generating indexes on recurring search fields can help speed query results.

Sample queries

The set of all relevant ad hoc queries of FCC ULS data is no doubt at least as large as the number of individuals that use radio equipment. Queries that I have found to be especially useful or instructive are shown below.

Sample queries (SQL)

Note: Depending on the power of the hardware, it may be beneficial to add one or more indexes to the "master" table before these and other queries are executed.

Emulating existing products and artifacts

There are several organizations that provide FCC ULS data on for-profit basis. It may be possible to generate one or more datasets that approximate these products.

Emulating existing products and artifacts (SQL)

Note: Clearly, there is some value-add to the each of the commercial products listed above. It is likely that this will continue into the foreseeable future.

Descriptive Statistics

Describing the data in the FCC ULS LM database is relatively straightforward. In general, the same database technology that was used in the initial data transformations above can be used to generate summary and univariate statistics.

Descriptive Statistics

Plotting and Charting

The ULS contains many fields that are numeric in nature. With a little querying and little scripting, we can automatically generate some useful graphs, especially plots over time. The following graphs were generated with R, although several other programs, such as gnuplot, could be used as well.

In general, the .png files are best for a display-based medium and the .pdf files are best for a print-based medium. The .svg files are suitable for both, but require a SVG-compatible browser, such as Mozilla Firefox. And while additional editing of the plots can be done with the (bitmap) .png or (postscript) .pdf formats, additional editing of the plots is most likely best done with the (vector) .svg format. One option for editing such .svg files is Inkscape.

Plotting and Charting

Time-Series Analysis

The ULS contains about a dozen fields that are dates. These dates can used in interesting ways, particularly for econometric or "event study" financial analyses. Some time-series techniques can be done with SQL queries, but others require a more specialized software tool (e.g., GRETL, eViews, or RATS).

Time-Series Analysis

Matrix Algebra

Some problems in radio systems engineering (e.g., signal processing and communications theory) involve solving a set of linear equations. The ULS contains data that can assist in addressing these problems. Some matrix-algebra techniques can be done with SQL queries, but others require a more specialized software tool, such as Octave. Some commercial alternatives are eViews and RATS.

Matrix Algebra

Statistical Inference

With the data in a workable database form, additional statistical tests useful in research can be employed. Most statistical inference techniques cannot be done with SQL queries, but require a more specialized software tool such as R, Stata, SPSS, or SAS.

Statistical Inference

Note: Frequency "Bands" are defined here as 25-50 MHz, 150-174 MHz, 216-220 MHz, 450-512 MHz, 806-870 MHz, and 896-940 MHz.

Note: IQV is useful for estimating the diversity when the variables in question are of a nominal (categorical) type. IQV ranges from 0 (no diversity) to 1 (full diversity). For an informal summary of IQV, see {FNLG02} or {Neu}. For a fuller treatment of IQV, see {Hea02}.

Structural Equation Modeling (SEM)

Some research questions are best addressed by going beyond measurement-level modeling into structural-level modeling. SEM techniques require software tools such as LISREL (student version) or the sna package in R. Some commercial alternatives are AMOS and EQS.

Structural Equation Modeling (SEM)

Data Mining

Traditional statistical techniques are a necessary, but occasionally insufficient, method to exploring a large dataset. Often, data mining techniques (also known as "machine learning" or "knowledge discovery in databases") can be leveraged to more fully understand such datasets. A practical resource to data mining is {kdnuggts}, a gentle introduction to the subject is {twocrowsguide}, a guide to the terminology is {twocrowsglossary} (practitioner) and {kohaviandprovost} (academic), and a very readable textbook on the subject is {HK01}. One weakness of data mining is that the analytical techniques can require significant hardware and software technology. One strength of data mining is a reduced need to meet requisite assumptions about the data or more important, about the use of sampling-based distributions. Another strength of data mining is that pattern discovery (and therefore, knowledge discovery) is highly automatable. One good open source software solution for data mining is [ WEKA ] {WEKA}.

Data Mining

Note: The XML Transformations are done by 1), querying out the required rows and columns in the database, exporting that result set to XML, and then 3), using the open source XALAN XSLT command-line utility. Using XSLT might seem a bit odd, but employing this strategy may help to improve related developments elsewhere on the World Wide Web.

Information Visualization

The purpose of Information Visualization (IV) is to help explore qualitative and quantitative data. IV techniques typically require specialized software, such as ggobi, Mage, or Pajek.

Information Visualization

Social Network Analysis

The purpose of Social Network Analysis (SNA) is to help understand the nature and degree of the social "ties" and underlying relationships in any given context. SNA techniques are not necessarily new, but can require substantial comutational and visualization technology. A few SNA calculations, mostly preparing the heterogenous matrices, can be done with SQL queries, but most analyses will require a more specialized software tool, such as 1), the sna package in R or 2), NetDraw.

Social Network Analysis

XML Transformations

A major disadvantage of using XML is that the database data is stored entirely in ASCII. For the same data, the amount of disk space consumed by an ASCII file format (XML) compared a binary storage format (e.g., MySQL) can be as much as 10 times or more. But XML offers several advantages as well, including some web-based applications that remain difficult in non-XML formats, such as data syndication formats (RSS or Atom). Some examples are shown below.

XML Transformations

Combining ULS and non-ULS data

The use of ULS data as part and parcel of a broader research project most likely involves datasets originating from other (non-FCC) sources. In database parlance, this is sometimes referred to as an "overlay". Some examples are shown below.

Combining ULS and non-ULS data

PHP5 and Sqlite Web-based Applications

Once the desired data is in a database format such as SQLite, a number of web-based applications are possible. Of course, a developer would want to augment the current FCC query applications, not replicate them.

Web-based Applications

Structured Reporting Formats

Since the data is in MySQL, one could make a traditional "report"; that is, a report that is intended to be "printed". A developer could use a database program, such as OpenOffice or MS-Access, or one could use a stand-alone reporting application such as open source BIRT, a component of the open source Eclipse project.

Structured Reporting Formats


Some of my earliest introductions to the use of database technology to manage FCC licenses and frequencies came from Bryan Hunnel and John Clark in the early- to mid-1980's. Bryan is a talented RF engineer and John a devoted educator. I am indebted to both of them for their friendship and radio knowledge.

Bibliographic Notes and References

Citation Note
{aar} See, for example, the Railroad frequency range as listed at [ AAR Frequencies ].
{abernathy} Abernathy, K. (2002). "My View from the Doorstep of FCC Change", Federal Communications Law Journal, 54(2), Mar 2002, pp. 199-223. This article is available in ABI/Inform. ABI/Inform is available through many libraries at no charge, including Los Angeles City Library databases. Anyone who lives or works in the City of Los Angeles may obtain a Library card at no charge.
{censuspop} The 2000 Census provides a useful source of population data. A ULS user can compare and contrast the number of relevant ULS records with size of the population at the [ State- ], [ (California) County- ], [ (California) City/Place- ], and [ Greater San Fernando Valley (913xx ZipCodes)- ] levels.
{compressionratios} The compression ratios range from a low of 53% (LA.dat) to a high of 91.6% (MW.dat) [ zipratios.txt ].
{describedatabase} As is the case in most types of database systems, but particularly relational database systems, the dbms-imposed data structure and indexes improve access/query capability at the expense of disk space [ describedatabase.txt ]. The disk space increase is split about evenly between the structure and the indexes.[ describedatabase-noindex.txt ]
{describeJOINedtables} As will be the case in relational database systems, the process of de-normalization improves end-user ease-of-use at the expense of disk space efficiency [ describeJOINtables.txt ].
{fcca-notice} See [ http://www.fcca.info/May4ltr.htm ]
{FNLG02} Frankfort-Nachimias, C., and A. leon-Guerreo, "Social Statistics for a Diverse Society" (3rd ed.), Pine Forge Press, 2002. See the Chapter 5 presentation online at [ Chapter 5 ]
{FoN-LASD} See [ LASD Frequency Configuration ].
{Hea02} Healy, J., Statistics: A Tool for Social Research (6th ed.), Wadsworth, 2002.
{HK01} Han, J., and M. Kamber, Data Mining: Concepts and Techniques, Academic Press (Harcourt), 2001.
{kdnuggets} See [ http://www.kdnuggets.com/ ]
{kohaviandprovost} Kohavi and Provost maintain a nice list of data mining terms. See [ http://robotics.stanford.edu/~ronnyk/glossary.html ]
{martin} Martin, H. (2003) "Frequency Coordination for Broadcast Auxiliaries", Broadcast Engineering, 45(12), Dec 2003, pg. 18. This article is available in ABI/Inform. ABI/Inform is available through many libraries at no charge, including Los Angeles City Library databases. Anyone who lives or works in the City of Los Angeles may obtain a Library card at no charge.
{Neu} See [ http://www.bsos.umd.edu/socy/alan/stats/socy201/handouts/Dispersion-201.pdf ]
{pa_ddef18} See [ http://wireless.fcc.gov/uls/data/documentation/pa_ddef18.txt ]. Note: I believe that the FCC uses Sybase as the database management system, partially because the SQL provided contains the T-SQL DDL construct "GO" and partially because Google searches (e.g., "site:fcc.gov sql") return an occasional reference to Sybase.
{pa_intro9} See [ http://wireless.fcc.gov/uls/data/documentation/pa_intro9.doc ]
{Pas04} Pasternack, B., "Once Upon a California Mountaintop", QST, March, 2004. See [ http://www2.arrl.org/qst/2004/03/pasterna.pdf ]
{perconwebpage} See [ percon-fcc.pdf ]
{schwartz} Schwartz, A., "Who Owns the Weather?", Center for American Progress, July, 24, 2004. See [ Schwartz paper ]
{twocrowsglossary} See [ http://www.twocrows.com/glossary.htm ]
{twocrowsguide} See [ http://www.twocrows.com/intro-dm.pdf ]
{uls} See Biennial Reg. Review; Amendment of Parts 0, 1, 13, 22, 24, 26, 27, 80, 87, 90, 95, 97, and 101 of the Comm'n's Rules, Report and Order, 13 F.C.C.R. 21027, para. 4, 13 Comm. Reg. (P & F) 1207 (1998).
{valleyscan} See [ http://www.n6lhv.net/valleyscan.html ]
{WEKA} Whitten, I., and F. Eibe, "Data Mining: Practical Machine Learning Tools and Techniques", Morgan-Kaufmann, 2000. See [ http://www.cs.waikato.ac.nz/~ml/weka/ ]
{wgetlog} See for example [ wget-LM_priv.log ]

Valid XHTML 1.1!  |  Valid CSS!  |  Level Triple-A conformance icon, W3C-WAI Web Content Accessibility Guidelines 1.0