16.4. Lesson: 空間検索

地理空間情報のクエリは、その他のデータベースのクエリと変わりなく、同じように利用できます。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)

ノート

上記_geom valueは当サイトのスペースを残すため削除されました。human-readable coordinatesを確認するためには、”View a point as WKT”セクションと類似した操作で確認することができます。

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

結果をご確認ください

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. Try Yourself moderate

Modify the cities table so its geometry column is spatially indexed.

結果の確認

16.4.4. PostGIS 空間関数デモ

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

開始のため、新しいデータベース (第一にpsql シェルが存在する)を作成します:

createdb postgis_demo

Remember to install the postgis extensions:

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

Next, import the data provided in the exercise_data/postgis/ directory. Refer back to the previous lesson for instructions, but remember that you’ll need to create a new PostGIS connection to the new database. You can import from the terminal or via SPIT. Import the files into the following database tables:

  • points.shp into building
  • lines.shp into road
  • polygons.shp into region

Load these three database layers into QGIS via the Add PostGIS Layers dialog, as usual. When you open their attribute tables, you’ll note that they have both an id field and a gid field created by the PostGIS import.

Now that the tables are imported, we can use PostGIS to query the data. Go back to your terminal (command line) and enter the psql prompt by running:

psql postgis_demo

We’ll demo some of these select statements by creating views from them, so that you can open them in QGIS and see the results.

16.4.4.1. 場所による選択

Get all the buildings in the KwaZulu region:

SELECT a.id, a.name, st_astext(a.the_geom) as point
  FROM building a, region b
    WHERE st_within(a.the_geom, b.the_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)

Or, if we create a view from it:

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

Add the view as a layer and view it in QGIS:

../../../_images/kwazulu_view_result.png

16.4.4.2. 近傍の選択

北海道地区に隣接するエリアの名称すべてのリストを表示します。

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

結果:

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

見た目として:

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

QGISでは:

../../../_images/adjoining_result.png

Note the missing region (Queensland). This may be due to a topology error. Artifacts such as this can alert us to potential problems in the data. To solve this enigma without getting caught up in the anomalies the data may have, we could use a buffer intersect instead:

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

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

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

../../../_images/hokkaido_buffer.png

Select using the buffer:

CREATE VIEW vw_hokkaido_buffer_select AS
  SELECT b.gid, b.name, b.the_geom
    FROM
    (
      SELECT * FROM
        vw_hokkaido_buffer
    ) a,
    region b
    WHERE ST_INTERSECTS(a.the_geom, b.the_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.the_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では:

../../../_images/hokkaido_buffer_select.png

It is also possible to select all objects within a given distance, without the extra step of creating a buffer:

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

This achieves the same result, without need for the interim buffer step:

../../../_images/hokkaido_distance_select.png

16.4.4.3. Select unique values

クイーンズランド州に所在する建物の所在地の村の名前をリストにします。

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

結果:

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

16.4.4.4. その他の事例

CREATE VIEW vw_shortestline AS
  SELECT b.gid AS gid, ST_ASTEXT(ST_SHORTESTLINE(a.the_geom, b.the_geom)) as
    text, ST_SHORTESTLINE(a.the_geom, b.the_geom) AS the_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.the_geom, b.the_geom)) as
    text, ST_LONGESTLINE(a.the_geom, b.the_geom) AS the_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.the_geom) as the_geom
    FROM road a
      WHERE a.id = 1;

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

SELECT ST_AREA(a.the_geom)
  FROM region a
    WHERE a.name='Queensland';
CREATE VIEW vw_simplify AS
  SELECT gid, ST_Simplify(the_geom, 20) AS the_geom
    FROM road;

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(the_geom, 50) AS the_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.the_geom)) AS the_geom
    FROM building a
    GROUP BY a.name;

16.4.5. In Conclusion

You have seen how to query spatial objects using the new database functions from PostGIS.

16.4.6. What’s Next?

Next we’re going to investigate the structures of more complex geometries and how to create them using PostGIS.