トップページ -> 研究道具箱と入門演習 -> リレーショナルデータベースの基礎 -> 郵便番号データをリレーショナルデータベースの1つのテーブル WORK に格納
[サイトマップへ]   [全文検索へ]   [統計情報へ]   [掲示板へ]  

郵便番号データをリレーショナルデータベース管理システム SQLite の1つのテーブル WORK に格納

サイト構成 連絡先,業績など 実践知識 データの扱い コンピュータ 教材 サポートページ

SQLite では日本語を含む CSV ファイルを読み込むような機能がない(みたい)なので, 自前のプログラムで CSV を SQL に変換します. この Web ページでは,日本郵政公社「ゆうびんホームページ」で公開されている 2つの郵便番号データのCSV(カンマ区切り値)形式ファイルを使います.

  1. 住所の郵便番号(CSV形式)(ken_all.csv)
  2. 事業所の個別郵便番号(CSV形式)(JIGYOSYO.CSV)

このファイルを,リレーショナルデータベース管理システム SQLIte の1つのテーブル(テーブル名は WORK)に格納します.次の手順を踏みます.

  1. 日本郵政公社「ゆうびんホームページ」で公開されている2つの郵便番号データのCSV(カンマ区切り値)形式ファイルを, 「郵便番号CSVデータ作成プログラム」を使って自動編集し,非辞書CSVデータファイルを作ります.

  2. 文字コードを UTF8 に変換します.

  3. リレーショナルデータベース管理システム SQLite で,テーブル定義を行う(テーブル名は WORK),

  4. 定義した WORK テーブルに,「郵便番号CSVデータ作成プログラム」の出力である 非辞書CSVデータファイルインポートします.

必要となるソフトウエア

あらかじめ決めておく事項

この Web ページでは,データベースの作成を行うので, 作成するデータベースのデータベース論理名と漢字コード(エンコーディング)を決めておくこと. この Web ページでは,次のように書きます.


CSV ファイルを SQL に変換するプログラム(Ruby プログラム) を使う.1箇所書き換える.

プログラムの実行

UTF8 に変換

SQLite3 コマンドライン・クライアントを使って読み込み

SQLite3 コマンドライン・クライアントの終了

確認


作業用のテーブル WORK の定義

SQL の「CREATE TABLE」を使って,作業用のテーブル WORK を定義します.

【テーブル定義の方針】

  1. 半角数字は INTEGER
  2. 半角カタカナと漢字は varchar
CREATE TABLE WORK (
    jiscode             INTEGER NOT NULL, 
    zip_old             INTEGER NOT NULL, 
    zipcode             INTEGER NOT NULL, 
    ken_kana            TEXT NOT NULL, 
    shichoson_kana      TEXT NOT NULL, 
    choiki_kana         TEXT NOT NULL, 
    ken_kanji           TEXT NOT NULL, 
    shichoson_kanji     TEXT NOT NULL, 
    choiki_kanji        TEXT NOT NULL, 

    num_ken_kana        INTEGER NOT NULL, 
    num_shichoson_kana  INTEGER NOT NULL, 
    num_choiki_kana     INTEGER NOT NULL, 
    num_ken_kanji       INTEGER NOT NULL, 
    num_shichoson_kanji INTEGER NOT NULL, 
    num_choiki_kanji    INTEGER NOT NULL, 

    flag10              TEXT NOT NULL, 
    flag11              INTEGER NOT NULL CHECK ( flag11 >= 0 AND flag11 <= 1 ), 
    flag12              INTEGER NOT NULL CHECK ( flag12 >= 0 AND flag12 <= 3 ),
    flag13              INTEGER NOT NULL CHECK ( flag13 >= 0 AND flag13 <= 1 ), 
    info14              INTEGER CHECK ( info14 >= 0 AND info14 <= 1 ), 
    info15              INTEGER CHECK ( info15 >= 0 AND info15 <= 5 ),

    org_choiki_kana     TEXT NOT NULL,
    org_choiki_kanji    TEXT NOT NULL, 
    comment_kanji       TEXT,
    comment_kana        TEXT, 
    kaisyamei_kana      TEXT,
    kaisyamei_kanji     TEXT
);

  • 「\q」で psql を終了

    (参考) 郵便番号データテーブル WORK の各列の説明

    jiscode    全国地方公共団体コード(JIS X0401、X0402)……… 半角数字 
    zip_old    (旧)郵便番号(5桁)……………………………………… 半角数字 
    zipcode    郵便番号(7桁)……………………………………… 半角数字 
    ken_kana    都道府県名カタカナ ………… 半角カタカナ(コード順に掲載) (注1) 
    shichoson_kana    市区町村名カタカナ ………… 半角カタカナ(コード順に掲載) (注1) 
    choiki_kana    町域名カタカナ ……………… 半角カタカナ(五十音順に掲載) (注1) 
                   ※「郵便番号CSVデータ作成プログラム」による自動編集のもの
    ken_kanji    都道府県名漢字 ………… 漢字(コード順に掲載) (注1,2) 
    shichoson_kanji    市区町村名漢字 ………… 漢字(コード順に掲載) (注1,2) 
    choiki_kanji    町域名漢字 ……………… 漢字(五十音順に掲載) (注1,2) 
                   ※「郵便番号CSVデータ作成プログラム」による自動編集のもの
    num_ken_kana    都道府県名カタカナ文字数
                   ※「郵便番号CSVデータ作成プログラム」により追加された属性
    num_shichoson_kana    市区町村名カタカナ文字数
                   ※「郵便番号CSVデータ作成プログラム」により追加された属性
    num_choiki_kana    町域名カタカナ文字数
                   ※「郵便番号CSVデータ作成プログラム」により追加された属性
    num_ken_kanji    都道府県名漢字文字数
                   ※「郵便番号CSVデータ作成プログラム」により追加された属性
    num_shichoson_kanji    市区町村名漢字文字数
                   ※「郵便番号CSVデータ作成プログラム」により追加された属性
    num_choiki_kanji    町域名漢字文字数
                   ※「郵便番号CSVデータ作成プログラム」により追加された属性
    flag10    一町域が二以上の郵便番号で表される場合の表示 (「1」は該当、「0」は該当せず) 
                   ※ 事業所の個別郵便番号では「北海道」のような文字列が入る
    flag11    小字毎に番地が起番されている町域の表示  (「1」は該当、「0」は該当せず) 
    flag12    丁目を有する町域の場合の表示 (「1」は該当、「0」は該当せず) 
    flag13    一つの郵便番号で二以上の町域を表す場合の表示 (「1」は該当、「0」は該当せず) 
    info14    更新の表示 (「0」は変更なし、「1」は変更あり、「2」廃止(廃止データのみ使用)) 
                   ※ 事業所の個別郵便番号では空値(NULL)になることがある.
    info15    変更理由 (「0」は変更なし、「1」市政・区政・町政・分区・政令指定都市施行、「2」住居表示の実施、「3」区画整理、「4」郵便区調整、集配局新設、「5」訂正、「6」廃止(廃止データのみ使用))  
                   ※ 事業所の個別郵便番号では空値(NULL)になることがある.
    org_choiki_kana      org町域名カタカナ
                   ※「郵便番号CSVデータ作成プログラム」による自動編集のもの
    org_choiki_kanji      org町域名漢字
                   ※「郵便番号CSVデータ作成プログラム」による自動編集のもの
    comment_kanji      コメント漢字
    comment_kana      コメントカタカナ
    kaisyamei_kana      会社名カタカナ
    kaisyamei_kanji      会社名漢字
    

    (参考)Linux の場合

    コマンドライン・クライアント psql を使って,テーブル定義を行う。

    su - postgres
    pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile -o "-i" start
    psql testdb
    上記に記述した CREATE TABLE を実行
    \dt
    \d WORK
    \?
    

    (参考)「郵便番号CSVデータ作成プログラム」を使わない場合

    日本郵政公社「ゆうびんホームページ」で公開されている郵便番号データについて, もし

    という場合には,下記のテーブル定義を使ってください.そのこと以外は,ほぼ、この Web ページの手順が使えます.

    CREATE TABLE WORK (
        jiscode INTEGER NOT NULL, 
        zip_old INTEGER NOT NULL, 
        zipcode INTEGER NOT NULL, 
        ken_kana TEXT NOT NULL, 
        shichoson_kana TEXT NOT NULL, 
        choiki_kana TEXT NOT NULL, 
        ken_kanji TEXT NOT NULL, 
        shichoson_kanji TEXT NOT NULL, 
        choiki_kanji TEXT NOT NULL, 
    
        flag10 TEXT NOT NULL, 
        flag11 INTEGER NOT NULL, 
        flag12 INTEGER NOT NULL, 
        flag13 INTEGER NOT NULL, 
        info14 INTEGER, 
        info15 INTEGER
    	);
    

    非辞書CSVデータ(ZIPDATA.CSV)の,郵便番号データテーブル WORK へのインポート

    SQLite の機能を用いて, 非辞書CSVデータ(ZIPDATA.CSV)のファイルを, 郵便番号データテーブル WORK にインポートします.

    1. 【前準備】この Web ページの上部に説明している,2つの郵便番号データのCSV形式ファイルを,非辞書CSVデータ(ZIPDATA.CSV)へ変換する作業が終わっていること. そのファイル名は,d:\ZIPDATA_UTF8.CSV であるとして説明している.


      後処理

      出来上がった郵便番号データ WORK に,少し加工を行う.

      「都道府県名カタカタ」,「市区町村名カタカナ」の空文字列

      説明

      下の図から分かる通り,郵便番号データテーブル WORK では, 「都道府県名カタカタ」,「市区町村名カタカナ」が空文字列になっているレコードがあります.

      ※ 空文字列と NULL は別物(混同しないように)

      「都道府県名カタカタ」,「市区町村名カタカナ」が空文字列になっているレコードは,他のレコードを使って転記を行うことができます. そのことを,郵便番号データテーブル WORK を使って説明しましょう. 下記の例で分かる通り,「都道府県名漢字」,「市区町村名漢字」の値が同じ値になっているような別のレコードに, 「都道府県名カタカタ」,「市区町村名カタカナ」が書いてあります. これを転記するというのが方針です.

      上で言っている「転記」を,SQL でどう行うか,以下,説明する. 説明のため,郵便番号データテーブル WORK でなく,簡略化したテーブルを使います. 例えば,あるテーブルがあって,「漢字」,「カタカナ」,「文字数」が次のようになっているとしましょう.空欄は空文字列です(NULL とは違うので混同しないこと).


      説明用のテーブル (テーブル名は元の表)

      上記のテーブルを,下記のように変更したいという問題です.「漢字」が「北」,「福」になっていて,かつ, 「カタカナ」が空文字列になっているレコードには,「カタカナ」,「文字数」が転記される. 一方で,「漢字」が「青」のレコードは,転記のしようがないので,手を付けずに残します.

      漢字  カタカナ  文字数
      ======================
      北    キタ      2
      北    キタ      2
      北    キタ      2キタ      2キタ      2
      青          0
      青          0
      福    フク      2
      福    フク      2
      福    フク      2フク      2
      ----------------------
      
      転記後のテーブルの例

      さて,元のテーブルから,変更後のテーブルを得る SQL を段階的に考えましょう. 「SELECT DISTINCT 漢字, カタカナ, 文字数 FROM 元の表;」を実行して,同一値を持つ例レコードの重複を取り除くと,次のようになる.


      図.「SELECT DISTINCT 漢字, カタカナ, 文字数 FROM 元の表;」の実行結果

      「青」のように,レコードが1つしかないもの「福」,「北」のように,レコードが2個あるものが見て取れます. では,「青」のように,レコードが1つしかないものを選ぶ SQL はどう書けるかというと, これは,副問い合わせを使うのが簡単です.

      1. まずは,「漢字」で集約して,個々の「漢字」ごとに,「カタカナ」の異なり数がいくつあるかを調べ,異なり数が1のレコードだけを選びます. これは,「『カタカナ』が1種類しかないレコードを選ぶ. ある『漢字』に対して,『カタカナ』として,空文字列とそうでないレコードが混ざっている場合には,異なり数は2以上とみなす」という意味になる. そのために, 「HAVING COUNT(DISTINCT(カタカナ));」という条件を SQL 内に含めることになる. なお,属性「文字数」は,「カタカナ」に依存する,つまり,関数従属なので, HAVING のところに「文字数」を指定する必要はありません.
      2. 副問い合わせの結果,{「青」} のような,漢字の集合が得られますので,それを使って,元の表からレコードを選び出す.

      SQL とその実行結果は,次のようになる.

      同様に, 「福」,「北」のように,レコードが2個あるものを選ぶ(3つ以上は除外します.) SQL も書けます.SQL とその実行結果は,次のようになる. 先ほどの SQL の「= 1」を「= 2」に変えただけです.

      この SQL に少し手を加えて,「カタカタ」が空文字列でないものだけを出力するようにします.(WHERE に,「NOT カタカナ = ''」を追加している).

      以上をまとめて,次のような UNION (和)を考えます.

      出力される表は,次のことをあらわしている.

      1. 「青」のレコードには,もともと「カタカナ」の情報が無いので,転記しない.
      2. 「福」のレコードには,「フク」,「2」を転記する
      3. 「北」のレコードには,「アオ」,「2」を転記する

      こうして出来た表を,元の表における転記用に使いたいので, 「CREATE TABLE ... AS ...」を使って,「作業用」という名前のテーブルに保存します.そして,「作業用」と「元の表」の2つのテーブルを使い,「元の表」における転記を行う.

      SQL とその実行結果は,次のようになる.期待通りの結果です.

      作業手順

      1. 属性「都道府県名カタカナ」について,空文字列の部分に転記

        下記の SQL を実行.pgAdmin III などでカットアンドペーストしてください.

        ※ psql を使うときは,日本語のテーブル名を変えて,日本語を使わないようにしてください.

        CREATE TABLE 作業用都道府県名リスト AS 
            SELECT DISTINCT ken_kanji, ken_kana, num_ken_kana
            FROM WORK
            WHERE ken_kanji
                IN ( SELECT ken_kanji
                     FROM WORK
                     GROUP BY ken_kanji
                     HAVING COUNT(DISTINCT(ken_kana)) = 1 )         
            UNION
            SELECT DISTINCT ken_kanji, ken_kana, num_ken_kana
            FROM WORK
            WHERE NOT ken_kana = ''
              AND ken_kanji
                IN ( SELECT ken_kanji
                     FROM WORK
                     GROUP BY ken_kanji
                     HAVING COUNT(DISTINCT(ken_kana)) = 2 );
        UPDATE WORK SET
        	num_ken_kana = (SELECT 作業用都道府県名リスト.num_ken_kana FROM 作業用都道府県名リスト WHERE WORK.ken_kanji = 作業用都道府県名リスト.ken_kanji)
        WHERE ken_kana = '';
        UPDATE WORK SET
        	ken_kana = (SELECT 作業用都道府県名リスト.ken_kana FROM 作業用都道府県名リスト WHERE WORK.ken_kanji = 作業用都道府県名リスト.ken_kanji)
        WHERE ken_kana = '';
        SELECT * FROM WORK;
        

      2. 念のため,「都道府県名カタカナ」が空文字列になっているレコードが存在しないことを確認しておく.

        SELECT * 
        FROM WORK
        WHERE ken_kana = '';
        

      3. 属性「市区町村名カタカナ」について,空文字列の部分に転記

        下記の SQL を実行.pgAdmin III などでカットアンドペーストしてください.

        ※ psql を使うときは,日本語のテーブル名を変えて,日本語を使わないようにしてください.

        CREATE TABLE 作業用市区町村名リスト AS 
            SELECT DISTINCT shichoson_kanji, shichoson_kana, num_shichoson_kana
            FROM WORK
            WHERE shichoson_kanji
                IN ( SELECT shichoson_kanji
                     FROM WORK
                     GROUP BY shichoson_kanji
                     HAVING COUNT(DISTINCT(shichoson_kana)) = 1 )         
            UNION
            SELECT DISTINCT shichoson_kanji, shichoson_kana, num_shichoson_kana
            FROM WORK
            WHERE NOT shichoson_kana = ''
              AND shichoson_kanji
                IN ( SELECT shichoson_kanji
                     FROM WORK
                     GROUP BY shichoson_kanji
                     HAVING COUNT(DISTINCT(shichoson_kana)) = 2 );
        UPDATE WORK SET
        	num_shichoson_kana = (SELECT 作業用市区町村名リスト.num_shichoson_kana FROM 作業用市区町村名リスト WHERE WORK.shichoson_kanji = 作業用市区町村名リスト.shichoson_kanji)
        WHERE shichoson_kana = '';
        UPDATE WORK SET
        	shichoson_kana = (SELECT 作業用市区町村名リスト.shichoson_kana FROM 作業用市区町村名リスト WHERE WORK.shichoson_kanji = 作業用市区町村名リスト.shichoson_kanji)
        WHERE shichoson_kana = '';
        SELECT * FROM WORK;
        

        ※「市区町村名カタカナ」が空文字列になっているレコードは残る.

        SELECT * 
        FROM WORK
        WHERE shichoson_kana = '';
        

      ※ (注意)町域については、「小谷」を「コヤト」,「オオタニ」などいろんな読み方をすることがある.都道府県や市区町村と同じというわけにはいかない.