Hatena::Grouppostgresql

PostgreSQL 雑記 このページをアンテナに追加 RSSフィード

2011-12-03SELECT * FROM Web-API : PostgreSQL Advent Calendar #3 このエントリーを含むブックマーク このエントリーのブックマークコメント

PostgreSQL Advent Calendar 12/3 です。

PostgreSQL 9.1ではSQL/MED規格の一部である 外部データラッパ (FDW: Foreign Data Wrapper) がサポートされました。これを使うと、Postgresの普通のテーブル以外の外部リソースもテーブルの形で検索、参照ができます。その拡張機能の一つに www_fdw があります。これは、Web APIの結果をテーブルとして取得するための、汎用フレームワークです。今回は、この www_fdw を使って、Google API のジオコーディング (地名 → 緯度・経度 変換) をしてみます。

環境構築

PostgreSQL 9.2dev で試しましたが、もちろん 9.1 でも動作すると思います。なお、XML機能を使うので、PostgreSQL本体を野良ビルドしている方は configure 時に --with-libxml をお忘れなく。

www_fdw を PGXN から取得するため、pgxn client を先にインストールしています。その後、www_fdw のダウンロード & インストールをし、テスト用に www_db データベースにログインします。

$ sudo easy_install pgxnclient
$ sudo yum install curl-devel
$ pgxn install www_fdw
$ pg_ctl start
$ createdb www_db
$ psql -d www_db

もし pgxn install の際に pg_config が見つからない旨のエラーが出る場合には、PATHが初期値にリセットされてるのかもしれません。自分は一時的にデフォルトパスに pg_config を置いてごまかしました。

$ sudo ln -s $PGDIR/9.2/bin/pg_config /bin/pg_config

Google Geocoding FDW の作成

www_fdw は Web API を使うための汎用フレームワークで、以下の2つをやってくれます。個別のAPIに対応させるには若干のカスタマイズが必要で、特に変換関数を用意するケースは多いと思われます。

  • WHERE句の条件をURLに変換し、HTTPリクエストを投げる。
  • HTTPレスポンスをテーブルにマッピングするために、変換関数を通す。

まずはEXTENSIONをインストールし、外部サーバを定義します。Google ジオコーディングAPIでは結果の形式が XMLJSON を選べますが、今回は、後で値を取り出せるよう xml を指定しておきます。なお、plv8jsを使えば、JSONからでも手軽に値を取り出せるかもしれません。

=# CREATE EXTENSION www_fdw;
=# CREATE SERVER google_geo_server FOREIGN DATA WRAPPER www_fdw OPTIONS (
     uri 'http://maps.google.com/maps/geo?output=xml',
     response_type 'xml',
     response_deserialize_callback 'google_geo_deserialize'
   );
=# CREATE USER MAPPING FOR current_user SERVER google_geo_server;
=# CREATE FOREIGN TABLE google_geo (
     q        text,
     address  text,
     north    float8,
     south    float8,
     east     float8,
     west     float8
   ) SERVER google_geo_server;

さて、マッピングの中心になるのが google_geo_deserialize 関数です。今回はXML出力を想定しているので、(xml型) ⇒ (テーブル型) の変換を行います。若干ごちゃごちゃしていますが、実際には、愚直にXMLからフィールドを取り出しているだけです。

=# CREATE FUNCTION google_geo_deserialize(options WWWFdwOptions, response xml)
    RETURNS SETOF google_geo AS $$
SELECT
  (
    (xpath('/ns:kml/ns:Response/ns:name/text()', $2,
       ARRAY[ARRAY['ns', 'http://earth.google.com/kml/2.0']]))[1]::text,
    (xpath('/Placemark/address/text()', x))[1]::text,
    (xpath('/Placemark/ExtendedData/LatLonBox/@north', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@south', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@east', x))[1]::text::float8,
    (xpath('/Placemark/ExtendedData/LatLonBox/@west', x))[1]::text::float8
  )::google_geo
FROM unnest(
  xpath('/ns:kml/ns:Response/ns:Placemark', $2,
    ARRAY[ARRAY['ns', 'http://earth.google.com/kml/2.0']])
) AS x
$$ LANGUAGE sql IMMUTABLE STRICT;

ジオコーディングを使ってみる

それでは実際に google_geo 外部表にアクセスしてみます。列 q に地名がずらりと並んでいるような仮想的なテーブルを想像してください。その中の地名の1つを取り出すイメージです。

=# \x
=# SELECT * FROM google_geo WHERE q = '東京タワー';
-[ RECORD 1 ]---------------------------------------
q       | 東京タワー
address | Tokyo Tower, Minato, Tokyo 105-0011, Japan
north   | 35.6676749
south   | 35.6495431
east    | 139.7614544
west    | 139.7294396

無事、緯度経度が取り出せました。さらに座標で検索を行う場合には point や box 型として扱ってもよいですね。

まとめ

www_fdw を使うと、PostgreSQL から直接 Web API を使うことができます。「入力はURLパラメータ、出力はXML」というタイプのAPIならば手間無く扱えます。また、それら以外の形式でも入出力のマッピング関数をカスタマイズできます。汎用フレームワークのため、ぎりぎりのチューニングは難しいかもしれませんが、HTTPを話せる多くのサービスと繋げられるのは夢が膨らみますね。twitter_fdw on www_fdw や、HTTPサーバモードのあるNoSQL DB (groonga 等) への簡易的なアクセス方法としても使えそうです。

明日は

PostgreSQL Advent Calendar 明日の担当は KaiGai.Kohei さんです。よろしく~

umitanukiumitanuki2011/12/03 15:11どうも最近のpgxn_clientにはloadってコマンドがinstallと分離して二段階になったみたいですよ。あとpg_configは--pg_configオプションがあるみたいです。
https://github.com/dvarrazzo/pgxnclient/blob/devel/pgxnclient/commands/__init__.py#L430
ヘルプ貧弱すぎ