PHPのテクメモ

PostGISなカラムに位置データを格納するとき、こんな具合のSQLを記述する。

INSERT INTO table (position) VALUES (GeomFromText(‘POINT(135 35)’,4326));

まずいつも間違えてしまうのは、座標を指定する方法。最初に経度で次に緯度。そして次に間違えるのが経度と緯度の区切り文字。これはカンマではなくてスペースなのだ。この2点はいつもよく間違える。

そしてようやく気づいたことがある。「今までこんなことも知らなくて、よくやってこれたね」って感じ。GeomFromTextというのは関数名だ（これはもちろん知っていた）。その引数は最初の引数が位置座標を表すテキストで、二つ目が座標系を表す数字だ（これも知っていた）。知らなかったのはGeomFromText関数の最初の引数についてだ。

この引数（シングルクォーテーションで囲まれたPOINT(135 35)の部分）はそのまま文字列なのだ。カッコでくくられているが紛れもない文字列だ。この場合は13文字の文字列。実は全然分かっていなくてPOINTという関数を使っているものだとばかり思っていた。すごく恥ずかしい。しかし、自分自身で気づけてよかった（でも確証がないのが悲しい）。

ちなみにPOINTという関数は実際に存在する。幾何型変換関数でpoint型の戻り値がある。いろいろ難しいね。

MySQLからデータをダンプする便利なコマンドmysqldump。テーブルがMyISAMなら問題ないけれど、innoDBなどトランザクション処理が絡む場合、単にmysqldump処理するとエラーメッセージが表示されてdumpできない。

Got error: 1044: Access denied for user ‘user’@’localhost’ to database ‘mydb’ when using LOCK TABLES

テーブルのロックがどうこうというエラーだ。中途半端にデータがinsert等された状態でdumpされたデータだと、復元した際にデータに矛盾が生じるかもしれないから事前にロックしようとしたけどできなかったよ、ということだろう。ネットを探しているとオプションをつけてしのぐ方法が書かれている。

mysqldump –skip-lock-tables mydb > mydb.sql

上記の「–skip-lock-tables」というオプションを紹介しているブログがやたらと出てきた。しかしこれ、そもそも根本的じゃないでしょうという感じ。ロックしないんだからデータに矛盾が生じそう。つまり使えるかどうかもわからないバックアップをしていることになってしまう（個人ユースならそれでもいいんだろうけど）。

mysqldump –single-transaction mydb > mydb.sql

マニュアルにはこういう記述があった。「–single-transaction」というオプションだ。このオプションだとテーブルをロックせず、トランザクションの範囲でバックアップしてくれるとのことだった。マニュアルにも「–lock-tablesより全然良い」と書いてある。これを使うことにしよう。

諸般の事情で再びPostGISに触れる機会があった（触れるどころでは済まないのだけれど）。まずはインストールから。しかし以前一度苦労しているので悩まない。今回はCentOSにインストールするのだが、あらかじめRedHat用にコンパイルされたPostGISのrpmを入手してあるのだ。今回使用したパッケージはこちら。

geos-3.0.0-1.x86_64.rpm
postgis-1.3.2-2.x86_64.rpm
proj-4.6.0-1.x86_64.rpm

で、まずはこれらをrpmで普通にインストールする。依存関係があるので3つ同時にインストールするのがよい。

それからPostgreSQLの設定。OSはインストールホヤホヤなので初期化も完了していないので作業としてはデータベースの初期化から始めなくてはいけない。作業したコマンドを全て列挙する。

initdb –no-locale –encoding=UTF8
createdb template_postgis
createlang plpgsql template_postgis
psql -d template_postgis -f lwpostgis.sql
psql -d template_postgis -f lwpostgis_upgrade.sql
psql -d template_postgis -f spatial_ref_sys.sql

データベースを初期化し、PostGIS用のテンプレートを作成、PL/pgSQLを使用できる状態にして、必要なSQL文を流し込む（SQL文はインストールしたパッケージに添付されている）。

この時点で出来上がったデータベース（template_postgis）は、通常のデータベースであってテンプレートではない。このデータベースを元にしてPostGISなデータベースを作るためには、テンプレート化しておかなくてはいけない。そのためにpsqlしてから以下のコマンドを入力した。

update pg_database set datistemplate=true where datname=’template_postgis’

これでPostGISのインストールが完了した。今後はtemplate_postgisをテンプレートとしてデータベースを作成すれば良いことになる。とりあえずここまでトラブルなく進めてよかった。

CentOS5.5の環境でMySQLを使っているとき、phpMyAdminを使ってテーブルをinnoDBにしようと思ったら・・・。あれ、できない、なんで。

たしかinnoDBはMySQL5から使えるようになったから大丈夫なはずなのに。いちおうMySQLのバージョンを確認してみると5.0.77。大丈夫なはずだよね。

とりあえず調べてみた。しかし情報が意外と少ない。そもそもこんなことで誰も悩まないのか、それともみんなあきらめがいいのか。

こちらのブログにひとつの方法が書いてあった。CentOS5.4でMySQL5.1ということでうちとは少し違うけど。my.cnfに2行ほど書き加えて、innoDBを使えるようにするというものだった。どうもその記述方法をみるとinnoDBがプラグインで動作しているような・・・。そんなんだったかなぁ。って調べてみたら5.1からプラグインがどうこうという情報がたくさんある。どうやら自分のところの5.0とは関係の無い話らしい。

しょうがないのでmy.cnfを確認。そして現時点でinnoDBを使えているサーバのmy.cnfも確認。そこに決定的な違いがあった。

skip-innodb

innoDBが使えない方のサーバの設定ファイルにはこんな記述が。なんじゃそりゃ。これは意図的にサーバ会社がこんなふうに設定していたのだろうか。よくわからない。

とりあえず使えている方のサーバには記述がないので、いったんmysqlを停止し、上記の行をコメントアウトしてmysqlを再起動。これで無事innoDBが使えるようになった（ように見えるだけかもしれないけれど）。

よかった。

もはやシステム開発とデータベースは切り離せないほど密接な関係があるといっても過言ではない。それ故、中途半端な知識でデータベースを使うと、あとで苦労することが多い。たかだか1000件とかその程度のデータであれば、問題ないのだが、データ件数が数万件とか超えてくると飛躍的にクエリの実行速度が遅くなってくる。

自分でも数年前までは、大量のデータを扱うような経験がなかったので、結果としてひどいアプリを作ってしまったことがある（幸か不幸か修正開発の機会を与えていただき、全て再開発した経緯がある）。だから他の人の事をとやかく言えないのであるが・・・。数百人も社員がいる会社でそういうことされると結構困ってしまう。そんな会社ダメでしょう、って感じ。

今担当している案件がまさにそんな感じ。データベースの設計がとにかくひどくて、そもそも正規化が出来ていない。相手はPostgreSQL。さぁ、どうしようかな、と悩める日々だ。そこでいろいろ思案中だったのだが、インデックスの使われ方ということで、古い記事ではあるが有意義な情報を見つけたのでメモしておく。

http://ml.postgresql.jp/pipermail/pgsql-jp/2003-February/004169.html

PostgreSQLでのインデックスの使われ方等で非常に勉強になる記事だ。知っている人なら知っているそうそうたる顔ぶれがディスカッションしている。これ見て少し勉強してみることにする。