16.5. Lesson: ジオメトリの構成

このセクションでは単純なジオメトリがSQL内でどのように構成されるかを少し掘り下げます。実際には、複雑なジオメトリをデジタル化ツールを使用して作るには、QGIS のような GIS を使用するでしょう。しかし、それらがどのように形作られているかを知ることは、クエリを書いたりデータベースがどのように作られているかを理解するのに役立ちます。

このレッスンの目的 PostgreSQL/PostGIS で空間要素を直接作成する方法をよく理解する。

16.5.1. ラインストリングの作成

address データベースに戻って、他に一致するstreetテーブルを取得してみましょう。すなわち、ジオメトリ、インデックスとGEOMETRY_COLUMNSテーブル中のエントリに制約を持ちます。

16.5.2. Try Yourself 

タイプST_LineStringのジオメトリ列を持つように street テーブルを修正します。
ジオメトリ列のテーブルに伴う更新を行うことを忘れないでください！
また、ラインストリングでないかnullであるようなジオメトリが追加されることを防ぐための制約を追加します。
新しいジオメトリ列に空間インデックスを作成します。

答え

alter table streets add column the_geom geometry;
alter table streets add constraint streets_geom_point_chk check
     (st_geometrytype(the_geom) = 'ST_LineString'::text OR the_geom IS NULL);
insert into geometry_columns values ('','public','streets','the_geom',2,4326,
     'LINESTRING');
create index streets_geo_idx
  on streets
  using gist
  (the_geom);

次に、streetテーブルにラインストリングを挿入しましょう。この場合、既存のstreetレコードを更新します：

update streets
set the_geom = 'SRID=4326;LINESTRING(20 -33, 21 -34, 24 -33)'
where streets.id=2;

結果をQGISで確認してみます。('レイヤ' パネルのstreetsレイヤを右クリックし、'レイヤの領域にズームする'を選択する必要があるかもしれません。)

いくつかはQGISから、いくつかはコマンドラインからstreetのエントリをもう少し追加します。

16.5.3. ポリゴンの作成

ポリゴンの作成も簡単です。覚えておくべきことの1つは、ポリゴンには少なくとも4つの頂点があり、最後と最初のものが同じ場所にあることです。

insert into cities (name, the_geom)
values ('Tokyo', 'SRID=4326;POLYGON((10 -10, 5 -32, 30 -27, 10 -10))');

注釈

ポリゴンは、その座標リストの周りに二重括弧を必要とします。これにより、複数の連結していない領域を持つ複雑なポリゴンを追加できるようなります。例えば

insert into cities (name, the_geom)
values ('Tokyo Outer Wards',
        'SRID=4326;POLYGON((20 10, 20 20, 35 20, 20 10),
                           (-10 -30, -5 0, -15 -15, -10 -30))'
        );

この手順に従った場合、QGISにcitiesデータセットをロードし、属性テーブルを開き、新しいエントリを選択することで、何をしたか確認できます。二つの新しいポリゴンが1つのポリゴンのように振る舞うかに注意してください。

16.5.4. 練習:CitiesをPeopleにリンクする

この演習では次のことを行う必要があります。

peopleテーブルからすべてのデータを削除します。
citiesテーブルの主キーを参照するpeopleへの外部キー列を追加します。
QGISを使っていくつかのcitiesをキャプチャします。
SQLを使って、それぞれが関連したstreetとcityを持った、新しいpeopleレコードをいくつか挿入してください。

更新されたpeopleのスキーマは、次のようになります:

\d people

Table "public.people"
   Column   |         Type          |                      Modifiers
 -----------+-----------------------+--------------------------------------------
  id        | integer               | not null
            |                       | default nextval('people_id_seq'::regclass)
  name      | character varying(50) |
  house_no  | integer               | not null
  street_id | integer               | not null
  phone_no  | character varying     |
  the_geom  | geometry              |
  city_id   | integer               | not null
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(the_geom) =
                       'ST_Point'::text OR the_geom IS NULL)
Foreign-key constraints:
  "people_city_id_fkey" FOREIGN KEY (city_id) REFERENCES cities(id)
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

答え

delete from people;
alter table people add column city_id int not null references cities(id);

（QGISでcitiesをキャプチャする）

insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
   values ('Faulty Towers',
           34,
           3,
           '072 812 31 28',
           1,
           'SRID=4326;POINT(33 33)');

insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
   values ('IP Knightly',
           32,
           1,
           '071 812 31 28',
           1,F
           'SRID=4326;POINT(32 -34)');

insert into people (name,house_no, street_id, phone_no, city_id, the_geom)
   values ('Rusty Bedsprings',
           39,
           1,
           '071 822 31 28',
           1,
           'SRID=4326;POINT(34 -34)');

次のエラーメッセージが出た場合:

ERROR:  insert or update on table "people" violates foreign key constraint
        "people_city_id_fkey"
DETAIL: Key (city_id)=(1) is not present in table "cities".

これは、citiesテーブルのポリゴンを作成する実験中に、そのうちのいくつかを削除してやり直したことを意味します。citiesテーブルのエントリを確認して、存在する id を使ってみてください。

16.5.5. スキーマに着目する

スキーマはこのように見えるべきです:

16.5.6. Try Yourself 

その都市のすべてのアドレスの最小凸包を計算し、その領域の周りのバッファを計算することにより、都市の境界線を作成します。

16.5.7. サブオブジェクトへのアクセス

SFS-モデルの機能を使用すると、SFSジオメトリのサブオブジェクトにアクセスするためのさまざまなオプションを持っています。テーブルmyPolygonTable内のすべてのポリゴンジオメトリの最初の頂点を選択したいときは、このような方法でこれを実行する必要があります：

ポリゴン境界をラインストリングに変換する：
```
select st_boundary(geometry) from myPolygonTable;
```

結果のラインストリングの最初の頂点を選択します。

select st_startpoint(myGeometry)
from (
    select st_boundary(geometry) as myGeometry
    from myPolygonTable) as foo;

16.5.8. データプロセシング

PostGISでは、すべてのOGC SFS/MM標準準拠関数をサポートしています。これらの関数はすべて ST_ で始まります。

16.5.9. クリッピング

データのサブパートをクリップするには、 ST_INTERSECT() 関数を使用します。空のジオメトリを避けるには、次のようにします。

where not st_isempty(st_intersection(a.the_geom, b.the_geom))

select st_intersection(a.the_geom, b.the_geom), b.*
from clip as a, road_lines as b
where not st_isempty(st_intersection(st_setsrid(a.the_geom,32734),
  b.the_geom));

16.5.10. ジオメトリを他のジオメトリから構築する

与えられたポイントテーブルから、ラインストリングを生成します。ポイントの順序は、その id によって定義されます。別の並べ方は、GPS受信機でウェイポイントをキャプチャするときに得るような、タイムスタンプになりうるかもしれません。

'points' という新しいポイントレイヤからラインストリングを作成するには、次のコマンドを実行します:

select ST_LineFromMultiPoint(st_collect(the_geom)), 1 as id
from (
  select the_geom
  from points
  order by id
) as foo;

どのように機能するかを新しいレイヤを作らずに確認するために、'people' レイヤにこのコマンドを実行することもできます。もちろん、これを行うことに現実世界の意味はほとんどないでしょうが。

16.5.11. ジオメトリクリーニング

このトピックについてはこのブログエントリにもっと情報があります。

16.5.12. テーブル間の差

同じ構造を持つ2つのテーブルの差を検出するには、PostgreSQLキーワード EXCEPT を使用します：

select * from table_a
except
select * from table_b;

結果として、table_aからtable_bに格納されていないすべてのレコードが得られるでしょう。

16.5.13. 表領域

Postgresがディスク上にデータを格納する場所を定義するには、表領域を作成します。

CREATE TABLESPACE homespace LOCATION '/home/pg';

データベースを作成するときは、例えばどの表領域を使用するか指定できます:

createdb --tablespace=homespace t4a

16.5.14. In Conclusion

PostGISの文を使用して、より複雑なジオメトリを作成する方法を学びました。これはGISのフロントエンドを通じて地理対応データベースを操作するときは、暗黙知を向上させることがほとんどであることに留意してください。これらのステートメントを実際に手動で入力する必要は普通はありませんが、その構造について一般的な知識を持っておくと、GISを使用するときに、特にそうでなければ不可解と思うであろうエラーが発生したときに、役に立ちます。