重要

翻訳は あなたが参加できる コミュニティの取り組みです。このページは現在 98.18% 翻訳されています。

16.4. レッスン: 空間クエリ

空間クエリは、その他のデータベースのクエリと変わりありません。ジオメトリ列を他のデータベースの列と同じように使うことができます。PostGISをデータベースにインストールすることでデータベースのクエリの機能が追加されます。

このレッスンの目的： 空間関数が、空間関数でない一般の関数と同様に導入できることを明らかにすること。

16.4.1. 空間演算子

ある地点(X,Y)から距離が2度内の地点を特定したい場合以下の操作ができます

select *
from people
where st_distance(the_geom,'SRID=4326;POINT(33 -34)') < 2;

結果:

 id |     name     | house_no | street_id |   phone_no    |   the_geom
----+--------------+----------+-----------+---------------+---------------
  6 | Fault Towers |       34 |         3 | 072 812 31 28 | 01010008040C0
(1 row)

注釈

上記の the_geom 値はこのページ上では当サイトのスペースを残すため削除されました。人が読める座標を確認したい場合、上記の「WKTとしてポイントを表示」セクションと類似の操作で確認できます。

上述のクエリが2 度 という空間内にある地点をすべて返すということはどうやって判るでしょうか？なぜ 2 メートル、あるいはその他の単位ではないのでしょうか？

答え

レイヤが使用しているCRSはWGS 84であるため、このクエリで使用されている単位は度です。これは地理CRSで、単位は度であることを意味します。UTM図法のような投影CRSは、単位がメートルです。

クエリを書くときには、レイヤのCRSがどの単位にあるのかを知る必要があることを忘れないでください。そうすることで、期待通りの結果を返すクエリを書くことができるようになります。

16.4.2. 空間索引

空間索引も定義できます。空間索引を使用すると、空間クエリをより迅速に作成できます。ジオメトリ列に空間索引を作成するには、次のようにします。

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (the_geom);

\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              |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_geo_idx" gist (the_geom)  <-- new spatial key added
  "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_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

16.4.3. やってみよう: ★★☆ （中級レベル）

都市のテーブルを、そのジオメトリ列が空間索引付けされるように変更します。

答え

CREATE INDEX cities_geo_idx
  ON cities
  USING gist (the_geom);

16.4.4. PostGIS 空間関数デモ

PostGIS の空間関数のデモを行うため、いくつかの（架空の）データを含む新しいデータベースを作成します。

まず、新しいデータベースを作成します（まずpsqlシェルを終了します）。

createdb postgis_demo

Postgis拡張機能をインストールすることを忘れないでください：

psql -d postgis_demo -c "CREATE EXTENSION postgis;"

次に、 exercise_data/postgis/ ディレクトリにあるデータをインポートします。手順については前のレッスンを参照してください。ただし、新しいデータベースへの新しいPostGIS接続を作成する必要があることに注意してください。端末またはDBマネージャからインポートできます。ファイルを次のデータベーステーブルにインポートします。

• points.shp を building に

• lines.shp を road に

• polygons.shp を region に

いつものように、 PostGISのレイヤを追加 ダイアログでこれらの3つのデータベースレイヤをQGISにロードします。それらの属性テーブルを開くと、 id フィールドとPostGISのインポートによって作成された gid フィールドの両方があることに注意。

テーブルはインポートされていますので、データを照会するためのPostGISを使用できます。端末（コマンドライン）に戻って以下を実行することによりpsqlのプロンプトを入力してください:

psql postgis_demo

QGISでそれらを開き、結果を見ることができるように、それらからのビューを作成することによってこれらのselect文の一部をデモします。

場所による選択

クワズール地域のすべての建物を手に入れよう：

SELECT a.id, a.name, st_astext(a.geom) as point
  FROM building a, region b
    WHERE st_within(a.geom, b.geom)
    AND b.name = 'KwaZulu';

結果:

 id | name |                  point
----+------+------------------------------------------
 30 | York | POINT(1622345.23785063 6940490.65844485)
 33 | York | POINT(1622495.65620524 6940403.87862489)
 35 | York | POINT(1622403.09106394 6940212.96302097)
 36 | York | POINT(1622287.38463732 6940357.59605424)
 40 | York | POINT(1621888.19746548 6940508.01440885)
(5 rows)

または、そこからビューを作成する場合は、次のようにします。

CREATE VIEW vw_select_location AS
  SELECT a.gid, a.name, a.geom
    FROM building a, region b
      WHERE st_within(a.geom, b.geom)
      AND b.name = 'KwaZulu';

レイヤとしてビューを追加し、QGISで表示：

近傍の選択

北海道地域に隣接する地域のすべての名前のリストを表示する：

SELECT b.name
  FROM region a, region b
    WHERE st_touches(a.geom, b.geom)
    AND a.name = 'Hokkaido';

結果:

    name
--------------
 Missouri
 Saskatchewan
 Wales
(3 rows)

ビューとして：

CREATE VIEW vw_regions_adjoining_hokkaido AS
  SELECT b.gid, b.name, b.geom
    FROM region a, region b
      WHERE st_touches(a.geom, b.geom)
      AND a.name = 'Hokkaido';

QGISでは:

不足している地域（クイーンズランド州）に注意してください。これはトポロジエラーが原因である可能性があります。このようなアーティファクトによって、データの潜在的な問題を警告できます。データが持つ異常に巻き込まれることなくこの謎を解決するために、代わりにバッファ交差を使用できます：

CREATE VIEW vw_hokkaido_buffer AS
  SELECT gid, ST_BUFFER(geom, 100) as geom
    FROM region
      WHERE name = 'Hokkaido';

北海道の周囲に100mのバッファを作成します。

暗いエリアがバッファです:

バッファを使用して選択：

CREATE VIEW vw_hokkaido_buffer_select AS
  SELECT b.gid, b.name, b.geom
    FROM
    (
      SELECT * FROM
        vw_hokkaido_buffer
    ) a,
    region b
    WHERE ST_INTERSECTS(a.geom, b.geom)
    AND b.name != 'Hokkaido';

In this query, the original buffer view is used as any other table would be. It is given the alias a, and its geometry field, a.geom, is used to select any polygon in the region table (alias b) that intersects it. However, Hokkaido itself is excluded from this select statement, because we don't want it; we only want the regions adjoining it.

QGISでは:

バッファを作成する追加のステップを行うことなしに、指定された距離内のすべてのオブジェクトを選択することもできます：

CREATE VIEW vw_hokkaido_distance_select AS
  SELECT b.gid, b.name, b.geom
    FROM region a, region b
      WHERE ST_DISTANCE (a.geom, b.geom) < 100
      AND a.name = 'Hokkaido'
      AND b.name != 'Hokkaido';

これは、中間バッファステップを必要とせずに、同じ結果を達成します：

一意の値を選択する

クイーンズランド州のすべての建物に固有の町名のリストを表示します：

SELECT DISTINCT a.name
  FROM building a, region b
    WHERE st_within(a.geom, b.geom)
    AND b.name = 'Queensland';

結果:

  name
---------
 Beijing
 Berlin
 Atlanta
(3 rows)

その他の事例

CREATE VIEW vw_shortestline AS
  SELECT b.gid AS gid,
        ST_ASTEXT(ST_SHORTESTLINE(a.geom, b.geom)) as text,
        ST_SHORTESTLINE(a.geom, b.geom) AS geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_longestline AS
  SELECT b.gid AS gid,
         ST_ASTEXT(ST_LONGESTLINE(a.geom, b.geom)) as text,
         ST_LONGESTLINE(a.geom, b.geom) AS geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_road_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
    FROM road a
      WHERE a.id = 1;

CREATE VIEW vw_region_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
    FROM region a
      WHERE a.name = 'Saskatchewan';

SELECT ST_PERIMETER(a.geom)
  FROM region a
    WHERE a.name='Queensland';

SELECT ST_AREA(a.geom)
  FROM region a
    WHERE a.name='Queensland';

CREATE VIEW vw_simplify AS
  SELECT gid, ST_Simplify(geom, 20) AS geom
    FROM road;

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(geom, 50) AS geom
    FROM road;

CREATE VIEW vw_convex_hull AS
  SELECT
    ROW_NUMBER() over (order by a.name) as id,
    a.name as town,
    ST_CONVEXHULL(ST_COLLECT(a.geom)) AS geom
    FROM building a
    GROUP BY a.name;

16.4.5. 結論

PostGISからの新しいデータベース機能を使用して、空間オブジェクトをクエリする方法を見てきました。

16.4.6. 次は?

次は、より複雑な幾何形状の構造と、それらをPostGISを使用して作成する方法を調査するつもりです。