Symfoware

Symfowareについての考察blog

Postgresql 9.1のCOPYでファイルの文字コードを指定する

地味にハマったのでメモ。


PostgresqlでCSVファイルをテーブルに取り込むには、COPYを使うのが便利だとわかりました。
Postgresql 9.1でCOPYを使用し、csvファイルからデータをインポートする


今回、お題として日本郵便で公開されている郵便番号データを
プログラムを使用せず、Postgresqlのテーブルに取り込んでみようと思います。
ただ、この郵便番号ファイルはShift-JISで、データベースはUTF8なんですよ。




unzipのインストール



最近知ったのですが、郵便番号データはlzhだけでなくzipでも公開されています。
今回は、Arch LinuxにインストールしたPostgresqlで試してみるので、
pacmanでunzipをインストールしておきました。


# pacman -S unzip








郵便番号データのダウンロードと展開



zip形式の郵便番号データはこちらで公開されています。
http://www.post.japanpost.jp/zipcode/dl/kogaki-zip.html

/tmpにダウンロードして、展開しておきます。
なお、ここからの作業はpostgresユーザーで行なっています。


$ cd /tmp
$ wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
$ unzip ken_all.zip



これで、/tmp/KEN_ALL.CSVというファイルができたかと思います。






作戦



ダウンロードしたファイルにはいろいろな情報が含まれています。
また、都道府県や市町村名、番地が別々のフィールドに入っています。

ファイルの形式は以下を参照。
http://www.post.japanpost.jp/zipcode/dl/readme.html


ここで考えた作戦が一旦テンポラリテーブルにCSVファイルをそのまま取り込み。
その後、ほしい形にSQLで整形しつつ一括インサート。

まず、CSVのレイアウトと同じ形式のテンポラリテーブルを作成します。


CREATE LOCAL TEMPORARY TABLE post_tmp (
全国地方公共団体コード int,
旧郵便番号 char(5),
郵便番号 char(7),
都道府県名カナ varchar(20),
市区町村名カナ varchar(100),
町域名カナ varchar(100),
都道府県名 varchar(20),
市区町村名 varchar(100),
町域名 varchar(100),
一町域が二以上の郵便番号 int,
小字毎に番地が起番 int,
丁目を有する町域 int,
一つの郵便番号で二以上の町域 int,
更新の表示 int,
変更理由 int
);



日本語名フィールドがそのまま通るので、あまり悩まずわかりやすいフィールド名が付けられますね。


次にこのテンポラリテーブルにcsvファイルからcopy。


COPY post_tmp FROM '/tmp/KEN_ALL.CSV' WITH encoding 'SJIS' CSV;



encodingオプションで'SJIS'をしていしてやります。
ポイントはCSVを指定するのですが、「FORMAT CSV」としないこと。

こんなエラーになります。

# COPY post_tmp FROM '/tmp/KEN_ALL.CSV' WITH encoding 'SJIS' FORMAT CSV;
ERROR: syntax error at or near "FORMAT"
LINE 1: ..._tmp FROM '/tmp/KEN_ALL.CSV' WITH encoding 'SJIS' FORMAT CSV...





これで、郵便番号の生データをPostgresqlに取り込むことが出来ました。


最終的にほしいテーブルの形式はこちら。

CREATE TABLE post (
郵便番号 char(7),
住所カナ varchar(500),
住所 varchar(500)
);



「郵便番号」
都道府県名カナと市区町村名カナと町域名カナを結合した「住所カナ」
都道府県名と市区町村名と町域名を結合した「住所」

post_tmpにいい感じでselectし、結果をそのままpostにinsertしてやります。


INSERT INTO post SELECT 郵便番号,都道府県名カナ || 市区町村名カナ || 町域名カナ, 都道府県名 || 市区町村名 || 町域名 FROM post_tmp;





Javaで適当にサンプルを書いて実行してみます。


  1. package com.fc2.blog68.symfoware.pg;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.Statement;
  6. public class Sample {
  7.     public static void main(String[] args) {
  8.         try {
  9.             String url = "jdbc:postgresql://192.168.1.12:5432/test";
  10.             String user = "pguser";
  11.             String pass = "password";
  12.             Connection con = DriverManager.getConnection(url, user, pass);
  13.             
  14.             Statement stmt = con.createStatement();
  15.             
  16.             ResultSet rs = stmt.executeQuery("select * from post where 住所 like '%銀座%'");
  17.             
  18.             while(rs.next()) {
  19.                 System.out.print(rs.getString("郵便番号"));
  20.                 System.out.print("\t");
  21.                 System.out.print(rs.getString("住所カナ"));
  22.                 System.out.print("\t");
  23.                 System.out.println(rs.getString("住所"));
  24.             }
  25.             
  26.             stmt.close();
  27.             con.close();
  28.         } catch (Exception e) {
  29.             e.printStackTrace();
  30.         }
  31.     }
  32. }





実行結果は狙い通り。


0691331    ホッカイドウユウバリグンナガヌマチョウギンザ    北海道夕張郡長沼町銀座
3220052    トチギケンカヌマシギンザ    栃木県鹿沼市銀座
3600032    サイタマケンクマガヤシギンザ    埼玉県熊谷市銀座
3670052    サイタマケンホンジョウシギンザ    埼玉県本庄市銀座
1040061    トウキョウトチュウオウクギンザ    東京都中央区銀座
9300991    トヤマケントヤマシシンジョウギンザ    富山県富山市新庄銀座
3940022    ナガノケンオカヤシギンザ    長野県岡谷市銀座
3940023    ナガノケンオカヤシヒガシギンザ    長野県岡谷市東銀座
3950031    ナガノケンイイダシギンザ    長野県飯田市銀座
4240817    シズオカケンシズオカシシミズクギンザ    静岡県静岡市清水区銀座
4130013    シズオカケンアタミシギンザチョウ    静岡県熱海市銀座町
4140028    シズオカケンイトウシギンザモトマチ    静岡県伊東市銀座元町
4750874    アイチケンハンダシギンザホンマチ    愛知県半田市銀座本町
4480845    アイチケンカリヤシギンザ    愛知県刈谷市銀座
5220088    シガケンヒコネシギンザチョウ    滋賀県彦根市銀座町
6128089    キョウトフキョウトシフシミクギンザチョウ    京都府京都市伏見区銀座町
7450032    ヤマグチケンシュウナンシギンザ    山口県周南市銀座
7450033    ヤマグチケンシュウナンシミナミギンザ    山口県周南市みなみ銀座
7700916    トクシマケントクシマシギンザ    徳島県徳島市銀座
8040076    フクオカケンキタキュウシュウシトバタクギンザ    福岡県北九州市戸畑区銀座












【参考URL】

PostgreSQL CSVファイルからのインポート

http://www.postgresql.jp/document/current/html/sql-copy.html

PostgreSQL の COPY コマンドと SQL だけで様々なデータをインポートする

関連記事

テーマ:データベース - ジャンル:コンピュータ

  1. 2012/05/06(日) 19:41:18|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<Arch Linux + nginx + php-fpm + Postgresqlな環境を作成する | ホーム | Postgresql 9.1でCOPYを使用し、csvファイルからデータをインポートする>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
https://symfoware.blog.fc2.com/tb.php/942-af2404ac
この記事にトラックバックする(FC2ブログユーザー)