mk-mode BLOG

このブログは自作の自宅サーバに構築した Debian GNU/Linux で運用しています。
PC・サーバ構築等の話題を中心に公開しております。(クローンサイト: GitHub Pages

ブログ開設日2009-01-05
サーバ連続稼働時間
Reading...
Page View 合計
Reading...
今日
Reading...
昨日
Reading...

Ruby + MySQL で郵便番号データ取り込み!

[ プログラミング ] [ MySQL, Rails, Ruby ]

こんばんは。

Ruby on Rails 上で郵便番号を検索できるシステムを作成することを思いつき、まずは日本郵便のサイトからダウンロードしたCSVファイルを Ruby + MySQL で取り込むことを考えてみました。

日本郵便のサイトによると、郵便番号データは「郵便事業株式会社は著作権を主張しません。自由に配布していただいて結構です。」となっております。

詳細は説明しませんが、全体の作業の流れは以下のとおり。

  • 予め、こちらからCSVファイルをダウンロード・解凍を手動で行っておきます。

  • CSVファイル名は “KEN_ALL.CSV” 固定。

  • CSVファイルは「読み仮名データの促音・拗音を小書きで表記しないもの」と「読み仮名データの促音・拗音を小書きで表記するもの」のどちらにも対応可能。 (ローマ字データや事業所の個別番号データも存在しますが、現時点では対応しません。)

  • MySQLにデータベース・テーブルを作成。

  • 後述のテーブルスクリプトを参照。

  • Rubyスクリプトを実行する。

  • 後述のRubyスクリプトを参照。

参考までに当方が作成したテーブルのテーブルスクリプト、Rubyスクリプトを掲載しておきます。 当然ながら、Ruby + MySQL が動作する環境が必要です。(文字コード等の調整が必要な場合もあります) 詳細についてはソースの随所にコメントを記述していますので、Rubyソースをご覧ください。そんなに複雑な処理は行っていなませんので、ご理解いただけるかと思います。

テーブルスクリプト

  • CSVファイルの全カラムを取得するようテーブルを作成しています。
  • ストレージエンジンは高速性を重視し “MyISAM” を指定しています。
  • 現時点では、インデックスは未確定です。 ( 検索条件により変更する可能性有り )
  • 環境によっては、MySQL自体の設定のチューニングが必要になるかもしれません。

postal_codes.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE IF NOT EXISTS `postal_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `public_code` char(5) NOT NULL,
  `postal_code_old` char(5) NOT NULL,
  `postal_code` char(7) NOT NULL,
  `pref_name_a` varchar(10) DEFAULT NULL,
  `city_name_a` varchar(30) DEFAULT NULL,
  `town_name_a` varchar(100) DEFAULT NULL,
  `pref_name_n` varchar(10) DEFAULT NULL,
  `city_name_n` varchar(30) DEFAULT NULL,
  `town_name_n` varchar(100) DEFAULT NULL,
  `flg_two_code` int(1) DEFAULT NULL,
  `flg_koaza` int(1) DEFAULT NULL,
  `flg_chome` int(1) DEFAULT NULL,
  `flg_two_town` int(1) DEFAULT NULL,
  `flg_upd` int(1) DEFAULT NULL,
  `flg_change` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_postal_code` (`postal_code`),
  KEY `idx_chimei` (`city_name_a`,`town_name_a`,`city_name_n`,`town_name_n`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Rubyスクリプト

大まかな流れは以下のとおり。

  • CSVファイルを全件読み込み、配列に格納。
  • 既存のテーブルのデータを削除。 ( TRUNCATE )
  • 読み込んだCSVデータ配列を全件書き込み。( INSERT )

get_post.rb

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
require 'csv'
require 'mysql'

#=郵便番号データ取得
class GetPost

  # DB(MySQL)接続情報
  $DB       = 'hoge'
  $HOST     = '127.0.0.1'
  $USER     = 'hogehoge'
  $PASSWORD = 'xxxxxxxx'

  # CSVファイル
  FILENAME_CSV = "KEN_ALL.CSV"

  # [CLASS] チェック
  class Check

    # CSVファイル存在チェック
    def check_csv()

      begin

        # 存在チェック
        if File.exist?( FILENAME_CSV )

          return ""

        else

          return "[ERROR] " + FILENAME_CSV + " is not exist!"

        end

      rescue => e

        # エラーメッセージ
        str_msg = "[例外発生][" + self.class.name + ".check_csv()] " + e.to_s
        STDERR.puts( str_msg )
        exit 1

      end

    end

  end

  # [CLASS] CSVファイル
  class Csv

    # CSVファイル読み込み
    #   0 全国地方公共団体コード --------------------------- 半角数字・5桁
    #   1 (旧)郵便番号(5桁) -------------------------------- 半角数字・5桁
    #   2 郵便番号(7桁) ------------------------------------ 半角数字・7桁
    #   3 都道府県名 --------------------------------------- 半角カナ ( コード順 ) [MAX: 7Byte,  7文字]
    #   4 市区町村名 --------------------------------------- 半角カナ ( コード順 ) [MAX:22Byte, 22文字]
    #   5 町域名 ------------------------------------------- 半角カナ ( 五十音順 ) [MAX:73Byte, 73文字]
    #   6 都道府県名 --------------------------------------- 漢字 ( コード順 )     [MAX: 8Byte,  4文字]
    #   7 市区町村名 --------------------------------------- 漢字 ( コード順 )     [MAX:20Byte, 10文字]
    #   8 町域名 ------------------------------------------- 漢字 ( 五十音順 )     [MAX:74Byte, 37文字]
    #   9 一町域が二以上の郵便番号で表される場合の表示 ----- 半角数字・1桁
    #      (「1」は該当、「0」は該当せず )
    #  10 小字毎に番地が起番されている町域の表示 ----------- 半角数字・1桁
    #      (「1」は該当、「0」は該当せず )
    #  11 丁目を有する町域の場合の表示 --------------------- 半角数字・1桁
    #      (「1」は該当、「0」は該当せず )
    #  12 一つの郵便番号で二以上の町域を表す場合の表示 ----- 半角数字・1桁
    #      (「1」は該当、「0」は該当せず )
    #  13 更新の表示 --------------------------------------- 半角数字・1桁
    #      (「0」は変更なし、「1」は変更あり、「2」廃止(廃止データのみ使用))
    #  14 変更理由 ----------------------------------------- 半角数字・1桁
    #      (「0」は変更なし、「1」市政・区政・町政・分区・政令指定都市施行、「2」住居表示の実施、
    #       「3」区画整理、「4」郵便区調整等、「5」訂正、「6」廃止(廃止データのみ使用) )
    # -------------------------------------------------------------------------------------------
    def read_csv()

      begin

        # 読み込み件数
        cnt_row = 0

        # CSVファイル全件ループ
        print "Reading CSV File "
        ary_data = []
        CSV.foreach( FILENAME_CSV ) do |row|
          ary_row = []
          row.each do |col|
            ary_row << NKF.nkf('-Sw', col.to_s )
          end
          cnt_row += 1
          print "." if cnt_row % 1000 == 0
          ary_data << ary_row
        end
        puts "\nRead Count = #{cnt_row.to_s}"

        return ary_data

      rescue => e

        # エラーメッセージ
        str_msg = "[例外発生][" + self.class.name + ".read_csv()] " + e.to_s
        STDERR.puts( str_msg )
        exit 1

      end

    end

  end

  # [CLASS] postal_codeテーブル
  class PostalCode

    # データ削除
    def delete_data()

      begin

        # MySQL 接続
        my = Mysql::connect( $HOST, $USER, $PASSWORD, $DB )

        # SQL文
        sql =  "TRUNCATE TABLE postal_codes "
        my.query( sql )

        puts "Deleted TABLE postal_codes ..."

      rescue => e

        # エラーメッセージ
        str_msg = "[例外発生][" + self.class.name + ".delete_data()] " + e.to_s
        STDERR.puts( str_msg )
        exit 1

      end

    end

    # データ書き込み
    def write_data( ary_data )

      begin

        # MySQL 接続
        my = Mysql::connect( $HOST, $USER, $PASSWORD, $DB )
        my.query("SET NAMES UTF8") # 環境によっては不要

        # Prepare
        sql =  "INSERT INTO postal_codes "
        sql << "          ( public_code, "
        sql << "            postal_code_old, "
        sql << "            postal_code, "
        sql << "            pref_name_a, "
        sql << "            city_name_a, "
        sql << "            town_name_a, "
        sql << "            pref_name_n, "
        sql << "            city_name_n, "
        sql << "            town_name_n, "
        sql << "            flg_two_code, "
        sql << "            flg_koaza, "
        sql << "            flg_chome, "
        sql << "            flg_two_town, "
        sql << "            flg_upd, "
        sql << "            flg_change ) "
        sql << " VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) "
        st = my.prepare( sql )

        # 書き込み件数
        cnt_ins = 0

        # INSERT
        print "Writing TABLE postal_codes "
        ary_data.each do |row|

          cnt_ins += 1

          # Execute
          st.execute( row[ 0].to_s,
                      row[ 1].to_s,
                      row[ 2].to_s,
                      row[ 3].to_s,
                      row[ 4].to_s,
                      row[ 5].to_s,
                      row[ 6].to_s,
                      row[ 7].to_s,
                      row[ 8].to_s,
                      row[ 9].to_s,
                      row[10].to_s,
                      row[11].to_s,
                      row[12].to_s,
                      row[13].to_s,
                      row[14].to_s )

          print "." if cnt_ins % 1000 == 0

        end

        puts "\nWrite Count = #{cnt_ins}"

      rescue => e

        # エラーメッセージ
        str_msg = "[例外発生][" + self.class.name + ".write_data()] " + e.to_s
        STDERR.puts( str_msg )
        exit 1

      end

    end

  end

  #################
  #### メイン処理 ####
  #################
  begin

    puts( "STARTED!!" )
    puts( "-" * 40 )

    # CLASS(チェック)インスタンス化
    obj_chk = Check.new()

    # CSVファイル存在チェック
    str_err = obj_chk.check_csv()
    if str_err != ""
      # エラーの場合、終了
      puts( str_err )
      exit
    end

    # CLASS(CSV)インスタンス化
    obj_csv = Csv.new()

    # CSVファイル読み込み
    ary_data = obj_csv.read_csv()

    # postal_codeテーブル インスタンス化
    tbl_postal_code = PostalCode.new()

    # postal_codeテーブル レコード削除
    tbl_postal_code.delete_data()

    # postal_codeテーブル レコード挿入
    tbl_postal_code.write_data( ary_data )

    puts( "-" * 40 )
    puts( "FINISHED!")

  rescue => e

    # エラーメッセージ
    str_msg = "[例外発生] " + e.to_s
    STDERR.puts( str_msg )
    exit 1

  end

end

2011年7月26日付けのデータだと、123,006件のデータが登録されます。 ある程度のマシンなら1分かかりません。非力なマシンでもそんなにかからないと思います。

郵便番号データの取り込みについては以上です。次は Ruby on Rails で検索できるようにしてみます。

以上。

Comments