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

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

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

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

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

16.5.2. Try Yourself 

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

Answer

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);

次に、街路テーブルに線ストリングを挿入しましょう。この場合、既存の道路記録を更新します：

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

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

いくつかはQGISから、いくつかはコマンドラインから道路の要素をもう少し追加します。

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に都市データセットをロードし、属性テーブルを開き、新しいエントリを選択することで、何をしたか確認できます。二つの新しいポリゴンが1つのポリゴンのように振る舞うかに注意してください。

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

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

peopleテーブルからすべてのデータを削除します。
都市テーブルの主キーを参照する人々への外部キー列を追加します。
QGISを使用していくつかの都市をキャプチャします。
それぞれが関連付けられている通り、街を持っていることを確認して、いくつかの新しい人々のレコードを挿入するSQLを使用してください。

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

\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)

Answer

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

(capture cities in QGIS)

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)');

If you're getting the following error message:

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".

then it means that while experimenting with creating polygons for the cities table, you must have deleted some of them and started over. Just check the entries in your cities table and use any id which exists.

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;

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

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

You can get more information for this topic in this blog entry.

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を使用するときに、特にそうでなければ不可解と思うであろうエラーが発生したときに、役に立ちます。