Import terrestrial transmitter data into an SQLite database for use in Antenna Mate (http://antennamate.com).

transmitter_importer.rb

#!/usr/bin/env ruby

# Import terrestrial transmitter data into an SQLite database for use in 
# Antenna Mate (http://antennamate.com).
#
# Supported Regions:
#   Australia     - http://www.acma.gov.au/scripts/nc.dll?WEB/STANDARD/1001/pc=PC_9150
#   United States - http://www.fcc.gov/mb/video/tvq.html
#
# Usage:
#   transmitter_importer DB_FILE --REGION=INPUT_FILE ...
#
# Example:
#   $ ruby transmitter_importer 'db.sqlite' --australia_digital=/path/to/file.csv
#
# The MIT License
# 
# Copyright (c) 2011 Tate Johnson <[email protected]>
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.

require 'conformist'
require 'dm-core'
require 'dm-sqlite-adapter'
require 'dm-migrations'

class String
  # Converts degrees, minutes and seconds to decimal degrees.
  #
  # "52°12′17.0″N".to_degrees # => 52.2047222
  # "27 28 05S".to_degrees    # => -27.4680555555556
  # "Invalid".to_degrees      # => 0.0
  #
  # Returns Float
  def to_degrees
    self =~ /^(\d+)[^\d]+(\d+)[^\d]+([\d\.]+)[^NESW]*([NESW])/i
    deg = $1.to_f + ($2.to_f / 60) + ($3.to_f / 3600)
    $4 =~ /[SW]/i ? -deg : +deg
  end
  
  def titlecase
    self.downcase.gsub(/\b([a-z])/) { $1.capitalize }
  end
end

class Australia
  extend Conformist
  
  column :name, 11 do |value| 
    value.match(/[A-Z\s\-]+$/)[0].strip.titlecase rescue value
  end
  column :latitude, 15 do |value| 
    value.to_degrees 
  end
  column :longitude, 16 do |value| 
    value.to_degrees 
  end
  column :area_served, 0
  column :callsign, 1
  column :frequency, 2
  column :polarisation, 4 do |value|
    case value
    when 'H' then 'horizontal'
    when 'V' then 'vertical'
    when 'D' then 'dual'
    when 'M' then 'mixed'
    end
  end
  column :antenna_height, 5 
  column :maximum_erp, 7
  column :channel, 18
end

class Australia::Analogue < Australia
  column :signal_type do
    'analogue'
  end
end

class Australia::Digital < Australia
  column :signal_type do
    'digital'
  end
end  

class UnitedStates
  extend Conformist
  
  option :col_sep => '|'
  
  column :name, 10, 11 do |values|
    "#{values[0].titlecase}, #{values[-1]}"
  end
  column :latitude, 20, 21, 22, 19 do |values|
    values.join(' ').to_degrees
  end
  column :longitude, 24, 25, 26, 23 do |values|
    values.join(' ').to_degrees
  end
  # TODO: column :area_served 
  column :callsign, 1
  # TODO: column :frequency
  column :polarisation, 32 do |value|
    case value
    when 'H' then 'horizontal'
    when 'C' then 'circular'
    when 'E' then 'elliptical'
    end
  end
  column :antenna_height, 36
  column :maximum_erp, 14
  column :channel, 4
end

class Transmitter
  include DataMapper::Resource

  property :id,             Serial
  property :name,           String
  property :latitude,       Float,  :index => :latitude_longitude
  property :longitude,      Float,  :index => :latitude_longitude
  property :area_served,    String, :index => true
  property :callsign,       String, :index => true
  property :frequency,      Float
  property :polarisation,   String
  property :antenna_height, Integer
  property :channel,        Integer
  property :maximum_erp,    Integer
  property :signal_type,    String, :index => true
end

class Importer
  attr_reader :regions
  
  def initialize path
    DataMapper.setup :default, :adapter => :sqlite, :path => "#{path}"
    DataMapper.finalize
    DataMapper.auto_migrate!
  end
  
  def regions
    @regions ||= []
  end
    
  def push region, path
    regions << case region
    when 'australia_analogue' then Australia::Analogue
    when 'australia_digital'  then Australia::Digital
    when 'united_states'      then UnitedStates
    else
      raise ArgumentError, 'Unknown region'
    end.load(path)
  end
    
  def import!
    Conformist.foreach *regions do |attributes|
      Transmitter.create! attributes
    end
  end
end

Importer.new(ARGV.delete_at(0)).tap do |importer|
  ARGV.map do |arg|
    arg =~ /--(\w+)=([\w\/\.]+)/
    importer.push $1, $2
  end
end.import!