SQLite を使って郵便番号から住所を検索するプログラム
第一回目は、 SQLite を使った実用的なプログラムとして、 郵便番号から住所を検索するソフトウェア postal を実装します。 Unix ではおなじみのコマンドラインで動作するソフトウェアとなります。 ソフトウェアが提供する機能は、 「郵便番号を引数に渡すと住所を表示する」だけとします。 完成版は、以下のような実行と結果を出力するプログラムとなる予定です。
% postal 1006090 1006090: 東京都千代田区霞が関霞が関ビル（地階・階層不明）
SQLite は、FreeBSD の ports の databases/sqlite3 で導入できます。
- 要求
- 指定した郵便番号に該当する住所を返す
- 設計
-
- 郵便番号と住所のデータをデータベースで管理する
- コマンドラインで動作するプログラムとする
- 引数には 7桁の郵便番号を取る
- 検索に成功した場合のみ、郵便番号に対応した住所を返す
郵便番号／住所データベースの作成
郵便番号のデータは、 日本郵政公社の郵便番号等のダウンロードのページの読み仮名データの促音・拗音を小書きで表記するものページの全国一括を利用します。 また、同ダウンロードページの事業所の個別郵便のダウンロードページの「最新データのダウンロード」も利用します。
全国版は ken_all.lzh、事業所版は jigyosyo.lzh という名前で保存されます。 ファイルは LZH 形式ですので、lha コマンドを実行して展開します。 ファイルを展開すると、それぞれ ken_all.csv, jigyosyo.csv というファイルが作成されます。 これらのファイルは、文字コードが Shift-JIS で改行コードが CR+LF で、 しかも JIS X 201-1976（半角カナ）が含まれ、数値が 2バイトコード（全角）です。
lha コマンドは、FreeBSD の ports の archives/lha-ac で導入できます。
FreeBSD でプログラミングを行うには、上記のままの漢字コードでは扱いにくいので、 wkf：漢字コード変換フィルタを使って漢字コードを EUC-JP（日本語 EUC コード）に変換します。 この際、数値も 1バイトに変換します。
% wkf -AezU ken_all.csv % wkf -AezU jigyosyo.csv
なお、SQLite は UTF-8 と UTF-16 の文字コードしかサポートしていませんが、 実用上 EUC-JP でも問題ないようです。
次に、データベースのテーブル構造を考えます。 全国版の郵便番号データファイルのデータ形式は、 以下の順に記述されています。
- 全国地方公共団体コード（JIS X0401, X0402）
- （旧）郵便番号（5桁）
- 郵便番号（7桁）
- 都道府県名（カナ）
- 市区町村名（カナ）
- 町域名（カナ）
- 都道府県名
- 市区町村名
- 町域名
- 一町域が二以上の郵便番号で表される場合の表示
- 「1」は該当
- 「0」は該当せず
- 小字毎に番地が起番されている町域の表示
- 「1」は該当
- 「0」は該当せず
- 丁目を有する町域の場合の表示
- 「1」は該当
- 「0」は該当せず
- 一つの郵便番号で二以上の町域を表す場合の表示
- 「1」は該当
- 「0」は該当せず
- 更新の表示
- 「0」は変更なし
- 「1」は変更あり
- 「2」廃止（廃止データのみ使用）
- 変更理由
- 「0」は変更なし
- 「1」市政・区政・町政・分区・政令指定都市施行
- 「2」住居表示の実施
- 「3」区画整理
- 「4」郵便区調整、集配局新設
- 「5」訂正
- 「6」廃止（廃止データのみ使用）
郵便番号から住所を検索するのに必要なデータは、 「3. 郵便番号（7桁）」と「7. 都道府県名」、「8. 市区町村名」、 「9. 町域名」です。将来使うことも考えて、今回は、 「4. 都道府県名（カナ）」と「5. 市区町村名（カナ）」、 「6. 町域名（カナ）」もデータベースに記録する対象とします。
同様に、事業所版の郵便番号データファイルのデータ形式は、 以下の順で記述されています。
- 大口事業所等の所在地のJISコード（5バイト）
- 大口事業所等名（カナ）（100バイト）
- 大口事業所等名（漢字）（160バイト）
- 都道府県名（漢字）（8バイト）
- 市区町村名（漢字）（24バイト）
- 町域名（漢字）（24バイト）
- 小字名、丁目、番地等（漢字）（124バイト）
- 大口事業所等個別番号（7バイト）
- 旧郵便番号（5バイト）
- 取扱郵便局名（漢字）（40バイト）
- 個別番号の種別の表示（1バイト）
- 「0」大口事業所
- 「1」私書箱
- 複数番号の有無（1バイト）
一つの事業所が同一種別の個別番号を複数持つ場合に複数番号を設定しているものとします。 従って、一つの事業所で大口事業所、私書箱の個別番号をそれぞれ一つづつ設定している場合は 12)は「0」となります。
- 「0」複数番号無し
- 「1」複数番号を設定している場合の個別番号の1
- 「2」複数番号を設定している場合の個別番号の2
- 「3」複数番号を設定している場合の個別番号の3
- 修正コード（1バイト）
- 「0」修正なし
- 「1」新規追加
- 「5」廃止
事業所用は、 「2. 大口事業所等名（カナ）」、 「3. 大口事業所等名（漢字）」、 「4. 都道府県名」、 「5. 市区町村名」、 「6. 町域名」、 「7. 小字名、丁目、番地等」、 「8. 大口事業所等個別番号」を利用します。
今回作成するソフトウェアは、郵便番号から住所を検索する機能が必要なので、 データベースは次のように設計します。 postcode.sql というファイル名で作成します。
CREATE TABLE japan (
postcode INTEGER,
prefecture TEXT,
city TEXT,
address TEXT,
rubi_pref TEXT,
rubi_city TEXT,
rubi_addr TEXT,
PRIMARY KEY (postcode, prefecture, city, address, rubi_addr)
);
CREATE INDEX japan_idx ON japan(postcode);
CREATE TABLE office (
postcode INTEGER,
name TEXT,
rubi TEXT,
prefecture TEXT,
city TEXT,
address TEXT,
PRIMARY KEY (postcode, name)
);
CREATE INDEX office_idx ON office(postcode);
データベースの設計ができたので、郵便番号データファイルから必要なデータを SQL の INSERT 文で出力するスクリプトを作成します。 CSV 形式のデータなので、FreeBSD に標準で導入されている awk を使ってプログラムを作成します。 全国版と事業所版では、データの項目が違いますので、 別々のプログラムを実装することになります。
全国版用の awk スクリプト postal-japan.awk は、次の通りです。
#!/usr/bin/awk -f
BEGIN {
FS = ","
print "BEGIN;";
}
{
printf("INSERT INTO japan VALUES(%s,%s,%s,%s,%s,%s,%s);\n",
$3, $7, $8, $9, $4, $5, $6);
}
END {
print "COMMIT;";
}
事業所版用の awk スクリプト postal-office.awk は、 次のようになります。 事業所版のスクリプトは、全国版と違って「6. 町域名」と「7. 小字名、 丁目、番地等」を連結して一つの項目にする小細工を行っています。 rest 変数がその役割を担っています。
#!/usr/bin/awk -f
BEGIN {
FS = ","
print "BEGIN;";
}
{
rest = $6 $7;
gsub("\"", "", rest);
printf("INSERT INTO office VALUES(%s,%s,%s,%s,%s,'%s');\n",
$8, $3, $2, $4, $5, rest);
}
END {
print "COMMIT;";
}
それでは、以下の手順でデータベースを作成します。
-
awk スクリプトを使って全国版および事業所版データファイルから
SQL 文を作成します
% ./postal-japan.awk ken_all.csv > ken_all.sql % ./postal-office.awk jigyosyo.csv > jigyosyo.sql
-
sqlite3 コマンドを使ってデータベースを作成します
% sqlite3 postcode.db < postcode.sql
-
データベースに全国版と事業所版のデータを読み込みます
% sqlite3 postcode.db < ken_all.sql % sqlite3 postcode.db < jigyosyo.sql
-
データベースにデータが登録されていることを確認します。
先のコマンド実行時にエラーが発生していなければ、問題ないはずです。
次の手順で確認できます。
% sqlite3 postcode.db SQLite version 3.4.1 Enter ".help" for instructions sqlite> select postcode,prefecture,city,address from japan limit 5; 600000|北海道|札幌市中央区|以下に掲載がない場合 640941|北海道|札幌市中央区|旭ケ丘 600041|北海道|札幌市中央区|大通東 600042|北海道|札幌市中央区|大通西（1〜19丁目） 640820|北海道|札幌市中央区|大通西（20〜28丁目） sqlite> select postcode,name,prefecture,city from office limit 5; 608640|（株） 北海道二十一世紀総合研究所|北海道|札幌市中央区 608602|朝日新聞社 北海道支社|北海道|札幌市中央区 608554|伊藤組土建 （株）|北海道|札幌市中央区 608547|伊藤忠商事 （株） 北海道支社|北海道|札幌市中央区 608570|医療法人 医仁会 中村記念病院|北海道|札幌市中央区 sqlite> .quit
検索プログラムの作成
作成するプログラムは、7桁の郵便番号を引数に取ります。 そこで最初に、7桁の郵便番号をチェックするコードを考えます。 これは strlen() でチェックします。 次に、郵便番号が数値のみで構成されているかチェックします。 これには、strspn() を利用します。 strspn() は、構成する文字集合が分かっている場合には、 非常に便利に使える関数です。
#define POSTCODE_LEN (7)
static const char digits[] = "0123456789";
size_t len;
size_t pos;
/* 引数 postcode が 7桁かをチェックする */
len = strlen(postcode);
if (len != POSTCODE_LEN) {
fprintf(stderr, "The length of %s is not 7.\n", postcode);
return -1;
}
/* 引数 postcode が 0〜9 の数値のみで構成されているかチェックする */
pos = strspn(postcode, digits);
if (pos != POSTCODE_LEN) {
fprintf(stderr, "%s has invalid character.\n", postcode);
return -1;
}
次に、データベースを検索する SQL 文を作成します。 全国版と事業所版では、項目が違うので 2種類作成することになります。 SQL 文の記述は、SQLite の文法に従っています。 $POSTCODE には、データベースの検索実行時に郵便番号が設定されます。
#define SQL_JAPAN \
"SELECT prefecture,city,address FROM japan" \
" WHERE postcode=$POSTCODE;"
事業所版は、事業所の名前も取得するようにします。 全国版との違いは、name の項目が追加されただけです。 name の追加場所が最後なのは、検索コードを実装する際に、 全国版と事業所版でコードを共通化できるようにするためです。
#define SQL_OFFICE \
"SELECT prefecture,city,address,name FROM office" \
" WHERE postcode=$POSTCODE;"
最後に、データベースから検索するコードを考えます。 全国版と事業所版を共通で利用したいので、 それぞれの SQL 文を引数に取れる関数を考えます。 また、引数で渡した変数に戻り値（住所と事業所名）を設定して返す関数を考えます。 関数の状態は、status 変数によって呼び出し元の関数に返されます。
static int
lookup(conn, sql, postcode, address, name)
sqlite3 * conn;
const char * sql; /* 検索用 SQL */
const char * postcode; /* 7桁の郵便番号 */
char ** address; /* 住所が設定される */
char ** name; /* 事業所名が設定される */
{
sqlite3_stmt * res;
int status;
status = sqlite3_prepare(conn, sql, (int)strlen(sql), &res, NULL);
if (status != SQLITE_OK) {
return -1;
}
status = sqlite3_bind_text(res, 1, postcode, POSTCODE_LEN,
SQLITE_TRANSIENT);
if (status != SQLITE_OK) {
status = -1;
goto done;
}
switch (sqlite3_step(res)) {
case SQLITE_ROW: {
int n;
n = sqlite3_column_count(res);
if (n >= 3) {
/* 住所を作成／設定 */
asprintf(address, "%s%s%s",
sqlite3_column_text(res, 0), /* prefecture */
sqlite3_column_text(res, 1), /* city */
sqlite3_column_text(res, 2)); /* address */
if (*address != NULL) {
status = 0;
}
/* 事業所名を設定 */
if (n == 4 && name != NULL) {
*name = strdup(sqlite3_column_text(res, 3));
}
}
}
break;
case SQLITE_DONE: /* 該当結果なし */
status = 1;
break;
default: /* エラー */
status = -1;
break;
}
done:
sqlite3_finalize(res);
return status;
}
オプションチェックやエラーチェックなどを含めた動作可能なソースコード postal.c は、次のようになります。 基本的なプログラムの流れは、main() 関数でオプション処理を行い、 postal 関数で引数チェックやデータベースのオープンを行っています。 postal 関数からデータベースの検索を行う lookup 関数が呼び出されます。 lookup 関数は郵便番号と SQL 文を引数に取り、 引数で与えられた変数に住所を設定して postal 関数に返します。 最終的なプログラムの状態は、postal 関数から返された値となります。
HTML 用にコメントなどを一部削除しています。
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <string.h>
#include <errno.h>
#include <sqlite3.h>
/* Program and Copyright */
const char Program[] = "postal";
const char Copyright[] =
"Copyright (c) 2007 Kouichi ABE (WALL), All rights reserved.";
/* Macros and structures definition */
/*
* Numeric release version identifier:
* MNNFFPPS: major minor fix patch status
* The status nibble has one of the values 0 for development,
* 1 to e for betas 1 to 14, and f for release.
* The patch level is exactly that.
*/
#define POSTAL_VERSION_NUMBER 0x10000000L
#define POSTAL_VERSION "Postal/1.0"
#define POSTAL_VERSION_TEXT POSTAL_VERSION " (2007/08/28)"
#define POSTAL_VERSION_TEXT_LONG "Postal 1.0, Tue, Aug 28 2007"
/*****************************************************************************/
#define POSTCODE_DB "/usr/local/share/postal/postcode.db"
#define POSTCODE_LEN (7)
#define SQL_JAPAN \
"SELECT prefecture,city,address FROM japan"
" WHERE postcode=$POSTCODE;"
#define SQL_OFFICE \
"SELECT prefecture,city,address,name FROM office"
" WHERE postcode=$POSTCODE;"
/* Local functions declaration */
static void version(void);
static void usage(void);
static int postal(const char * dbfile, const char * postcode);
static int lookup(sqlite3 * conn, const char * sql, const char * postcode,
char ** address, char ** name);
/* Functions definition */
static void
version(void)
{
fprintf(stderr, "%s\n%s\n\n", POSTAL_VERSION_TEXT, Copyright);
exit(64);
}
static void
usage(void)
{
fprintf(stderr, "usage: %s [-d <database>] <postcode>\n", Program);
fprintf(stderr, "\n [ example ]\n");
fprintf(stderr, " %s 1006090\n", Program);
fprintf(stderr, " %s -d postcode.db 1008798\n\n", Program);
exit(64);
}
static int
postal(dbfile, postcode)
const char * dbfile;
const char * postcode;
{
static const char digits[] = "0123456789";
size_t len;
size_t pos;
sqlite3 * conn;
char * address;
int status = -1;
len = strlen(postcode);
if (len != POSTCODE_LEN) {
fprintf(stderr, "The length of %s is not 7.\n", postcode);
return -1;
}
pos = strspn(postcode, digits);
if (pos != POSTCODE_LEN) {
fprintf(stderr, "%s has invalid character.\n", postcode);
return -1;
}
status = sqlite3_open(dbfile, &conn);
if (status != SQLITE_OK) {
fprintf(stderr, "%s: %s\n", dbfile, sqlite3_errmsg(conn));
return -1;
}
status = lookup(conn, SQL_JAPAN, postcode, &address, NULL);
if (status == 0) {
fprintf(stdout, "%s: %s\n", postcode, address);
free(address);
}
else {
char * name;
status = lookup(conn, SQL_OFFICE, postcode, &address, &name);
if (status == 0) {
fprintf(stdout, "%s: %s %s\n", postcode, address, name);
free(address);
free(name);
}
}
sqlite3_close(conn);
return status;
}
static int
lookup(conn, sql, postcode, address, name)
sqlite3 * conn;
const char * sql;
const char * postcode;
char ** address;
char ** name;
{
sqlite3_stmt * res;
int status;
status = sqlite3_prepare(conn, sql, (int)strlen(sql), &res, NULL);
if (status != SQLITE_OK) {
return -1;
}
status = sqlite3_bind_text(res, 1, postcode, POSTCODE_LEN,
SQLITE_TRANSIENT);
if (status != SQLITE_OK) {
status = -1;
goto done;
}
switch (sqlite3_step(res)) {
case SQLITE_ROW: { /* 検索結果あり */
int n;
n = sqlite3_column_count(res);
if (n >= 3) {
asprintf(address, "%s%s%s",
sqlite3_column_text(res, 0),
sqlite3_column_text(res, 1),
sqlite3_column_text(res, 2));
if (*address != NULL) {
status = 0;
}
if (n == 4 && name != NULL) {
*name = strdup(sqlite3_column_text(res, 3));
}
}
}
break;
case SQLITE_DONE: /* 該当住所なし */
status = 1;
break;
default: /* エラー */
status = -1;
break;
}
done:
sqlite3_finalize(res);
return status;
}
int
main(int argc, char * argv[])
{
register int ch;
const char * dbfile = POSTCODE_DB;
int status;
/* parse options */
while ((ch = getopt(argc, argv, "d:hv?")) != -1) {
switch (ch) {
case 'd': dbfile = optarg; break;
case 'v': version();
case 'h':
default:
usage();
}
}
argc -= optind;
argv += optind;
if (argc == 0) { usage(); }
/*
* status の値
* 0: 住所あり
* 1: 該当なし
* -1: エラー
*/
status = postal(dbfile, argv[0]);
return status;
}
コンパイルは、次のように行います。
% gcc -I/usr/local/include -L/usr/local/lib -o postal postal.c -lsqlite3
では、プログラムの動作確認を行ってみましょう。 まずは、全国版の郵便番号検索です。
% ./postal -d postcode.db 1006001 1006001: 東京都千代田区霞が関霞が関ビル（1階）
次に、事業所版の郵便番号検索です。 全国版の出力と違い、事業所名も表示されます。
% ./postal -d postcode.db 1008798 1008798: 東京都千代田区霞が関1丁目3−2 日本郵政公社
なお、指定した郵便番号に該当する住所がない場合は、何も表示しません。 他のプログラムから呼び出す場合は、postal の終了状態で判断してください。 住所があれば「0」、該当なしで「1」、エラーで「-1」を返します。 次のような実行で、確認できます。
% ./postal -d postcode.db 1234567 ; echo $? 1
SQLite は小さくてシンプルでかつ実用的なリレーショナル・データベースです。 アプリケーションでデータベースを利用する必要がある場合は、 まず最初に SQLite を使うことを検討してみてはどうでしょうか。 実は、SQLite で十分と言う場合がたくさんあるのに気づくでしょう。