Tag: spectrum

Categories
GIS Uncategorized

TAFL — as a proper geodatabase

Update, 2017: TAFL now seems to be completely dead, and Spectrum Management System has replaced it. None of the records appear to be open data, and the search environment seems — if this is actually possible — slower and less feature-filled than in 2013.

Update, 2013-08-13: Looks like most of the summary pages for these data sets have been pulled from data.gc.ca; they’re 404ing. The data, current at the beginning of this month, can still be found at these URLs:

I build wind farms. You knew that, right? One of the things you have to take into account in planning a wind farm is existing radio infrastructure: cell towers, microwave links, the (now-increasingly-rare) terrestrial television reception.

I’ve previously written on how to make the oddly blobby shape files to avoid microwave links.  But finding the locations of radio transmitters in Canada is tricky, despite there being two ways of doing it:

  1. Wrestle with the Spectrum Direct website, which can’t handle the large search radii needed for comprehensive wind farm design. At best, it spits out weird fixed-width text data, which takes some effort to parse.
  2. Download the Technical and Administrative Frequency Lists (TAFL; see update above for URLs), and try to parse those (layout, fields). Unless you’re really patient, or have mad OpenRefine skillz, this is going to be unrewarding, as the files occasionally drop format bombs like
    never do this, okay?
    Yes, you just saw conditional different fixed-width fields in a fixed-width text file. In my best Malcolm Tucker (caution, swearies) voice I exhort you to never do this.

So searching for links is far from obvious, and it’s not like wireless operators do anything conventional like register their links on the title of the properties they cross … so these databases are it, and we must work with them.

The good things is that TAFL is now Open Data, defined by a reasonable Open Government Licence, and available on the data.gc.ca website. Unfortunately, the official Industry Canada tool to process and query these files, is a little, uh, behind the times:tafl2dbfYes, it’s an MS-DOS exe. It spits out DBase III Files. It won’t run on Windows 7 or 8. It will run on DOSBox, but it’s rather slow, and fails on bigger files.

That’s why I wrote taflmunge. It currently does one thing properly, and another kinda-sorta:

  1. For all TAFL records fed to it, generates a SpatiaLite database containing these points and all their data; certainly all the fields that the old EXE produced. This process seems to work for all the data I’ve fed to it.
  2. Tries to calculate point-to-point links for microwave communications. This it does less well, but I can see where the SQL is going wrong, and will fix it soon.

taflmunge runs anywhere SpatiaLite does. I’ve tested it on Linux and Windows 7. It’s just a SQL script, so no additional glue language required. The database can be queried on anything that supports SQLite, but for real spatial cleverness, needs SpatiaLite loaded. Full instructions are in the taflmunge / README.md.

TAFL is clearly maintained by licensees, as the data can be a bit “vernacular”. Take, for example, a tower near me:

pharm_eg

The tower is near the top of the image, but the database entries are spread out by several hundred meters. It’s the best we’ve got to work with.

Ultimately, I’d like to keep this maintained (the Open Data TAFL files are updated monthly), and host it in a nice WebGIS that would allow querying by location, frequency, call sign, operator, … But that’s for later. For now, I’ll stick with refining it locally, and I hope that someone will find it useful.

Categories
GIS

Got a receiver inside my head – writing Spectrum Direct data as CSV

Industry Canada publishes the locations of all licensed radio spectrum users on Spectrum Direct. You can find all the transmitters/receivers near you by using its Geographical Area Search. And there are a lot near me:

While Spectrum Direct’s a great service, it has three major usability strikes against it:

  1. You can’t search by address or postal code; you need to know your latitude and longitude. Not just that, it expects your coordinates as a integer of the format DDMMSS.
  2. It’s very easy to overwhelm the system. Where I live, I can pretty much search for only 5km around me before the system times out.
  3. The output formats aren’t very useful. You can either get massively verbose XML, or very long line undelimited text, and neither of these are very easy to work with.

Never fear, Perl is here! I wrote a tiny script that glues together Dave O’Neill‘s Parse::SpectrumDirect::RadioFrequency module (which I wonder if you can guess what it does?) to Robbie Bow‘s  Text::CSV::Slurp module. The latter is used to blort out the former’s results to a CSV file that you can load into any GIS/mapping system.

Here’s the code:

#!/usr/bin/perl -w
# spectest.pl - generate CSV from Industry Canada Spectrum Direct data
# created by scruss on 02010/10/29 - for https://glaikit.org/

# usage: spectest.pl geographical_area.txt > outfile.csv

use strict;
use Parse::SpectrumDirect::RadioFrequency;
use Text::CSV::Slurp;
use constant MINLAT => 40.0;    # all of Canada is >40 deg N, for checking

my $prefetched_output = '';

# get the whole file as a string
while (<>) {
 $prefetched_output .= $_;
}

my $parser = Parse::SpectrumDirect::RadioFrequency->new();

# magically parse Spectrum Direct file
$parser->parse($prefetched_output) or die "$!\n";
my $legend_hash = $parser->get_legend();    # get column descriptions
my @keys        = ();
foreach (@$legend_hash) {

 # retrieve column keys in order so the output will resemble input
 push @keys, $_->{key};
}

# get the data in a ref to an array of hashes
my $stations = $parser->get_stations();

my @good_stations = ();

# clean out bad values
foreach (@$stations) {
 next if ( $_->{Latitude} < MINLAT );
 push @good_stations, $_;
}

# create csv file in memory then print it
my $csv = Text::CSV::Slurp->create(
 input       => \@good_stations,
 field_order => \@keys
);
print $csv;
exit;

The results aren’t perfect; QGis boaked on a file it made where one of the records appeared to have line breaks in it. It could filter out multiple pieces of equipment at the same call sign location. But it works, mostly, which is good enough for me.