PHPのテクメモ

データベースの作り方で一工夫必要だったわけだが、テーブルに位置情報を格納するカラムを作るのはさらに一工夫必要だ。

ID番号、地点名、位置情報の3つを格納するテーブルを作成することにする。テーブルを作成する際は、まず最初に、位置情報を格納するカラム以外のカラムだけでテーブルを作成する。つまりID番号、地点名だけのテーブルを作成する。例えばこんな具合に。

CREATE TABLE positions (
id int4 SERIAL PRIMARY KEY,
name varchar(128)
);

そして、PostGISをインストールした際にたくさん設置された関数群のうちの一つを使用して、位置情報を格納するカラムを追加する。ここではlatlngというカラム名を使用することにする。

SELECT AddGeometryColumn(‘public’,’positions’,’latlng’,4326,’POINT’,2);

publicはスキーマ名
positionsはテーブル名
latlngはカラム名
4326は測地系の番号（この数字はWGS84）
POINTは型（他にLINESTRINGとかPOLYGONとか）
2は2次元

つまりSELECT文を使って、AddGeometryColumn関数を実行しているということ（この関数でカラムを追加）。この作業でようやくPostGISなテーブルが作成される。ここまできて、ようやく今までやってきたことの意味が理解できるようになる。

4326ってどこから出てきたのか。実はこの数字はPostGISなデータベースに自動的に作られるテーブル「spatial_ref_sys」に全て格納されている。このテーブルには約3000行のデータが格納されているのだが、これらは全て座標系だ。4326の値は外部キーのようになっているということ。

そして位置情報格納カラム「latlng」を作成した関数AddGeometryColumnはカラムを追加するだけでなくて、そのカラムの情報を「geometry_coumns」に格納している。ためしにこのテーブルを開くと、先ほどの関数の引数と似たような内容でデータが格納されていることが一目瞭然だ。

もうここまでくればPostGISに関する理解はほぼできたといっても過言ではない（と思いたい）。「なんで最初からわけのわからないテーブルが二つも作られているの」「どうやって使うの」という問題は通り過ぎてしまったわけで、後は希望通りの処理を返してくれるPostGISな関数がどれなのか、ということを探すことになる。

ただしもう一つ知っておかなければいけないことがある。それは測量に関する知識だ。当たり前だが地球は丸い。というか球だ（実際は球ではなくてもっと歪んでいる）。球を無理やり平面に押し込めたのが地図だ。軟式のテニスボールをカッターで切り刻んでみればわかるが、本来球はどうやっても平面にはならない。でも、それでも人間が理解しやすいようにPostGISは答えを返してくれる（らしい）。そんなPostGISをこれから徐々に使っていこうと思う。

当面PostGISを使うので、思ったことや気づいたこと、わかったことをメモしていくことにする。

お試しインストールだけしたPostGISだったが、やっぱり気になるので少しだけ試すことにした。何をどうすればよいのかわからないし、書籍も皆無な状態なので、いつものように検索しつつ・・・の確認作業。

データベースの作成からはじめないといけない。PostgreSQLのデータベース作成で、以前はデータベースのテンプレートなんて「なんのこっちゃ」ということで気にも留めていなかったけれど、ここから勉強だ。というか「こういう用途だったのね」と再認識した。PostGISインストール時に自動的に作成されたtemplate_postgisというデータベーステンプレートを元にデータベースの作成をしなくてはいけない。要はコピーして新規作成。

createdb -T template_postgis dbname

これでdbnameという名前のデータベースが作成される。大量の関数群と二つのテーブルがあらかじめ作成された状態になっている。でもまだまだ序の口。だいたいなんで最初からテーブルが作成されているのかわからない。どうやって位置情報を格納するのかもわからない。わからないことだらけだ。

とりあえず、小さいことからコツコツと始めることにする。

PostgreSQLを長期間vacuumせず放置してディスクを浪費した件で、対処方法を調べたのでメモ。

一番簡単なのはvacuumをかけることだが、vacuumをかけるにはディスク容量を必要とする。例えばデータベース容量が膨れ上がって2GBになってしまったが実際は1GB程度のデータベースだったと仮定する。このデータベースにvacuumをかける際、ディスクの空き容量として1GB程度が必要になる（らしい）。しかもvacuumには時間がかかりそう。多分、いったんデータベースがダンプされ、データベースを削除し、ダンプされたデータベースから復旧する、というイメージなんだと思う。

とりあえず実際のデータベース容量がわからず、かつ膨れ上がったデータベース（テーブル）がテンポラリ用途で使用していたので、不要な行をすべて削除してからダンプした。ほとんどファイル構造だけのダンプなので数十キロバイトですんだ。その後dropdbでデータベースを削除した。2GB強のデータベースだったが、数秒で削除された。その後、データベースを再作成して、ダンプデータから復旧。これで復旧できた。空き容量もしっかり確保できた。

データベースは適当に運用しちゃだめだね。

最近PostGIS関連の話題に触れる機会があった。前々から手をつけなくてはいけない、と思いつつ、データベースのこみいったこととか得意ではないので放置していたが少しがんばることにした。まずはローカルのWindowsマシンに新ストールするところからはじめた。

以前WindowsマシンにPostgreSQLをインストールしようとしたときに、pgAdminから接続できなかったりいろいろてこずった記憶があった（Windowsマシンには残骸が残っていた）。しかし今回はスムーズにインストールできた。まず最初に残骸を一式削除し、PostgreSQLのサイトから8.3系をダウンロードした。最新はPostgreSQL8.4だけど（公開ほやほや）、最新である必要もないので今回は8.3にしておいた。ダウンロードは以下のページから。

http://www.postgresql.jp/

すごく久々にPostgreSQLのサイトを見たけど、リニューアルされた感じ。わかりやすいかどうかといわれると、それはなんともいえないけれど、ダウンロードページへの導線がわかりやすくてよかったかも。

インストーラを起動し、適宜必要な項目を入力。メインのインストールが完了したら、アプリケーションスタックビルダなるユーティリティが起動した。ここからPostGISをインストールできるらしい。なんとも簡単になったものだ。しかしPostGISを選択してインストールを続行していると、「要求された操作には管理者特権が必要です」なんてメッセージが表示されてどうしてもインストールできない。

とりあえず悩むのも面倒だったので、メッセージ中に書かれているPostGISインストーラ（上記操作の中でダウンロードだけは完了している）を直接起動したらうまくインストールできた。この後pgAdminを起動し、PostgreSQLサーバにうまく接続できることを確認した。pgAdminから確認して、データベース「postgis」とか、PostGISのテンプレートとかができていることは確認できたからたぶん大丈夫なんだろう。興味本位でいろいろ見てみたら、関数がたくさん用意されているようだ（674個）。これがPostGISの本体に相当するものだろうか・・・。

さて、作業はここまででいったん終了。これからどうしようか・・・。途方にくれる。

お客さんに貸してるサーバのディスク容量が残りわずかになってしまった。原因はわかっている。PostgreSQLのメンテナンスが不十分だったからだ。サーバの中にはいくつかのPostgreSQLデータベースが稼動しているが、果たしてどれが原因なのか。どのデータベースがどれだけの容量を使用しているのか調べたのでメモ。

まずPostgreSQLのデータがあるディレクトリに移動する。RedHat系OSでは以下の場所に存在する。

/var/lib/pgsql/

おなじみ、設定ファイル等が設置されている場所だ。この中の奥の階層にデータは格納されている。

/var/lib/pgsql/data/base/

上記の中に入ると、数字だけで構成されたディレクトリが多数存在する。しかも多段の階層で。これだけでは意味がわからない。まずここで、直下の階層についてのみ容量をチェックする。

du –max-depth=1 -h

階層を一つ下って、人間が理解しやすい形式でデータを出力させる。これでひとまず、どのディレクトリがディスクを浪費しているのか把握できる。しかしこのディレクトリ名は何を意味しているのか。

どうやらこれはoidを意味しているらしい。そこでoidとデータベース名を関連付けることさえできれば、どのデータベースがどれだけ容量を使用しているか把握できることになる。それを把握するためのSQL文がコレ。

select datid,datname from pg_stat_database;

これでoidとデータベース名の関連付けができる。
さて、どのデータベースに問題があるのかはすぐにわかったが、どうやって処理しようか。それを現在考え中。頭の痛い課題だ。