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 = "    create table \#{tablename} (\n      id integer not null primary key,\n      code varchar(10) not null,\n      state_id integer,\n      city varchar(255),\n      area_code varchar(3),\n      lat float,\n      lon float,\n      accuracy varchar(8)\n    )\n  SQL\n  database.execute_batch(schema)\n\n  ndx = <<-SQL\n    CREATE UNIQUE INDEX \"main\".\"unique_zipcode\"\n    ON \#{tablename} (state_id, code, city COLLATE NOCASE ASC);\n  SQL\n  database.execute_batch(ndx)\nend\n"

#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 = "    INSERT INTO zipcodes (code, state_id, city, lat, lon, accuracy)\n    VALUES ('\#{row[:postal_code]}',\n      '\#{state_id}',\n      '\#{city_name}',\n      '\#{row[:latitude]}',\n      '\#{row[:longitude]}',\n      '\#{row[:accuracy]}'\n    )\n  SQL\n\n  begin\n    database.execute(sql)\n  rescue SQLite3::ConstraintException => e\n    unless e.message.include?('UNIQUE')\n      raise \"Please file an issue at \#{ISSUE_URL}: [\#{e}] -> SQL: [\#{sql}]\"\n    end\n  rescue StandardError => e\n    raise \"Please file an issue at \#{ISSUE_URL}: [\#{e}] -> SQL: [\#{sql}]\"\n  end\n\n  update_progress\nend\n"