Class: FreeZipcodeData::ZipcodeTable

Inherits:
DbTable
  • Object
show all
Defined in:
lib/free_zipcode_data/zipcode_table.rb

Constant Summary

Constants inherited from DbTable

DbTable::ISSUE_URL

Instance Attribute Summary

Attributes inherited from DbTable

#database, #tablename

Instance Method Summary collapse

Methods inherited from DbTable

#initialize, #update_progress

Constructor Details

This class inherits a constructor from FreeZipcodeData::DbTable

Instance Method Details

#build ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
# File 'lib/free_zipcode_data/zipcode_table.rb', line 7

def build
  schema = <<-SQL
    create table #{tablename} (
      id integer not null primary key,
      code varchar(10) not null,
      state_id integer,
      city varchar(255),
      area_code varchar(3),
      lat float,
      lon float,
      accuracy varchar(8)
    )
  SQL
  database.execute_batch(schema)

  ndx = <<-SQL
    CREATE UNIQUE INDEX "main"."unique_zipcode"
    ON #{tablename} (state_id, code, city COLLATE NOCASE ASC);
  SQL
  database.execute_batch(ndx)
end

#write(row) ⇒ Object 



29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/free_zipcode_data/zipcode_table.rb', line 29

def write(row)
  return nil unless row[:postal_code]

  state_id = get_state_id(row[:country], row[:short_state], row[:state])
  unless state_id
    logger.verbose(
      "Skipping zipcode '#{row[:postal_code]}': no state found for " \
      "abbr='#{row[:short_state]}', country='#{row[:country]}'"
    )
    return nil
  end

  city_name = escape_single_quotes(row[:city])

  sql = <<-SQL
    INSERT INTO zipcodes (code, state_id, city, lat, lon, accuracy)
    VALUES ('#{row[:postal_code]}',
      '#{state_id}',
      '#{city_name}',
      '#{row[:latitude]}',
      '#{row[:longitude]}',
      '#{row[:accuracy]}'
    )
  SQL

  begin
    database.execute(sql)
  rescue SQLite3::ConstraintException => e
    unless e.message.include?('UNIQUE')
      raise "Please file an issue at #{ISSUE_URL}: [#{e}] -> SQL: [#{sql}]"
    end
  rescue StandardError => e
    raise "Please file an issue at #{ISSUE_URL}: [#{e}] -> SQL: [#{sql}]"
  end

  update_progress
end