AddGeometryColumns 使わんでよくなったので感涙にむせんでるところ

ツイッターで http://blog.opengeo.org/2012/03/06/postgis-2-0-new-features-typmod/ が流れてきてました。

この文自体は読んでないのですが、PostGIS 2.0 から GEOMETRY型の Typmod になってるぜ、というものです。

AddGeometryColumns 使わなくていいようになった

Typmod というのは、つまるところ…

CREATE TABLE foo (
  gid SERIAL PRIMARY KEY,
  geom GEOMETRY(Point, 4326)
);

…でジオメトリカラムの生成が可能になるというもの。

以前は…

CREATE TABLE foo (
  gid SEIRAL PRIMARY KEY
);
SELECT AddGeometryColumn('foo','geom',4326,'POINT',2);

…と、後で追加を実行しなければならなかったのです。

AddGeometryColumn の引数の順序を覚えられなかった、アタマの悪い私にとっては、この時点で感激。

1.5 でも GEOGRAPHYの方は Typmod になってたりします。いやこのときもありがたい、GEOMETRYでもこうなってほしい、と思ったものです。

ALTER COLUMN でも活躍

さらに、ブログは続く。ALTER COLUMN が使える、と。

たとえば「空間参照系を EPSG:4326 にしてたけど EPSG:4612 にしたい」という場合などに役に立つ、と。

とりあえず1行入れてみます。

INSERT INTO foo (geom) SELECT 'SRID=4326;POINT(135 35)';

EWKTで見ると、空間参照系IDも出てくるので、空間参照系の確認に便利ですね。

db=# SELECT ST_AsEWKT(geom) FROM foo;

        st_asewkt        















































-























 SRID=4326;POINT(135 35)
(1 row)

4326を4612にするのが「便利」かどうかは人によってとらえ方が違うと思います。

EPSG:4326 のフィーチャーを EPSG:4612 にして、geom にセットしようとすると、カラムの制限に引っかかります。

db=# UPDATE foo SET geom=ST_Transform(geom,4612);
ERROR:  Geometry SRID (4612) does not match column SRID (4326)

本来なら、カラムの制限を変更して、GEOMETRY_COLUMNSを変更して、geomの変換を行った結果をセットする、という3つの手続きを踏まないといけませんが、これがクエリひとつで可能です。

ALTER TABLE foo
  ALTER COLUMN geom TYPE GEOMETRY(point,4612)
    USING ST_Transform(geom,4612);

USINGの後に更新に使う関数を用意してやると、あわせて変換してくれます。ただし、PostgreSQL 8 以降でないと、USING句に対応していません。

db=# SELECT ST_AsEWKT(geom) FROM foo;

        st_asewkt        















































-























 SRID=4612;POINT(135 35)
(1 row)

余談: AddGeometryColumns と GEOMETRY_COLUMNS

PostGIS 1.5 では、インストール時点で GEOMETRY_COLUMNS というテーブルが生成されました。これはカラムのメタデータを集めていて、PostGIS のクライアントは、このテーブルからカラムのジオメトリタイプなどを取得しています。QGIS もそう。

PostGIS内部では、PostgreSQLの機能を活用して、カラムに対して、SRIDの制限とかジオメトリタイプの制限とかを付けます。メタデータの方を見に行くよりPostgreSQLの機能使った方が安全性が上がるからだろうと思います。

1.5以前での AddGeometryColumns は、クライアントのための作業と内部のための作業を実行していました。つまり、カラムを生成して、カラムに制限を付け、GEOMETRY_COLUMNS にメタデータを入れる、という作業を実行していました。

PostGIS 2.0 では GEOMETRY_COLUMNS は、ビューに変更されました。このビューは、カラムの制限情報を読んで、これまでの GEOMETRY_COLUMNS テーブルと同じになるように加工したうえで返します。これで常にクライアント向けのメタ情報提供 (GEOMETRY_COLUMNS) と内部でのふるまい (カラムの制限) とで矛盾が生じないようになります。

ずっと前に、ジオメトリカラムを持つテーブルを削除する前に DropGeometryColumn でジオメトリカラムを削除しないと GEOMETRY_COLUMNS にゴミが残ってしまった、という経験を持っている人にとっては、「これからは DROP TABLE でばっさり切って問題ないんだぜ、いぇーい」となると思います。

MULTI- を取るのは完全には無理

shp2pgsqlで「ポリゴン」のシェープをSQLに変換した際、オプションで "-S" を指定しないと、MULTIPOLYGON になってしまう場合があります。これを POLYGON に変換できないか、というもの。

結論から言うと、完全には無理です。

CREATE TABLE mfoo (
  gid SERIAL PRIMARY KEY,
  geom GEOMETRY(MULTIPOINT, 4326)
);
INSERT INTO mfoo (geom) SELECT 'SRID=4326;MULTIPOINT(135 35, 134 35)';

で、MULTIPOINTを持つテーブルができました。

MULTI- から MULTI を取るのは ST_Dump です。これは、path と geom からなる複合型の集合で返ります。複数行になるというわけです。

db=# SELECT ST_AsEWKT( (ST_Dump(geom)).geom ) FROM mfoo;
        st_asewkt        















































-























 SRID=4326;POINT(135 35)
 SRID=4326;POINT(134 35)
(2 rows)

こんなかんじ。

これを ALTER COLUMN TYPE で使えるかどうか、というと…

db=# ALTER TABLE mfoo ALTER geom TYPE geometry(point, 4326) USING ST_Dump(geom);
ERROR:  transform expression must not return a set

… PostgreSQL側で「変換式は集合を返したらいかん」と怒られました。

MULTI- を外すというのは、単純に外すのではなくて、要素ごとに分解して、それぞれで1行にしないといけないので、他のカラムの値を推測せよというのも無理な話ですから、あきらめざるを得ないです。
本当に MULTI- を外したいなら、別テーブルを作って INSERT でデータを作らないといけないでしょう。

ただし、全ての要素を取らなくてもいい、という場合には、ST_GeometryN を使うことで可能です。MULTI- の1つ目の要素を返させて、EWKT で出力する例を示します。

db=# SELECT ST_AsEWKT(ST_GeometryN(geom,1)) FROM mfoo;
        st_asewkt        















































-























 SRID=4326;POINT(135 35)
(1 row)

これを ALTER COLUMN TYPE で使うことはできます。

ALTER TABLE mfoo ALTER geom TYPE geometry(point, 4326) USING ST_GeometryN(geom,1);

なお、2つ目以降はなくなります。

db=# SELECT gid, ST_AsEWKT(geom) FROM mfoo;
 gid |        st_asewkt        



























































-





























   1 | SRID=4326;POINT(135 35)
(1 row)