13. 空間結合¶

空間結合は空間データベースの基本です。空間関係を結合キーにによって使用することで、異なるテーブルからの情報を結びつけることができます。「標準GIS解析」と考えるものの多くは空間結合として表現できます。

前の節では、空間関係を2ステップで調べました。すなわち、最初に地下鉄 'Broad St' 駅のポイントを抽出し、その後、そのポイントを「'Broad St' のある地区は何ですか?」といった、より大きな問題の質問に使いました。

空間結合を使うと、この地下鉄駅に関する情報の検索と、地下鉄駅を含む地区の検索とを1ステップで実行できます:

SELECT
  subways.name AS subway_name,
  neighborhoods.name AS neighborhood_name,
  neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.geom, subways.geom)
WHERE subways.name = 'Broad St';

 subway_name | neighborhood_name  |  borough
-------------+--------------------+-----------
 Broad St    | Financial District | Manhattan

全ての地下鉄駅と、その駅の属する地区とを結合することができますが、この場合は一つだけ情報が必要でした。二つのテーブルの間の TRUE / FALSE 関係を返す関数は全て、空間結合で使うことができます。最も一般的には ST_Intersects, ST_Contains, ST_DWithin が使われます。

13.1. 結合と要約¶

GROUP BY と JOIN の組合せによって、GIS システムで通常行われるような解析が可能です。

例: 「マンハッタンの地区ごとの人口と人口構成比率は?」 国勢調査から人口情報と、地区の境界データとの組み合わせた質問があり、マンハッタンの一つの行政区のみに制限された質問となります。

SELECT
  neighborhoods.name AS neighborhood_name,
  Sum(census.popn_total) AS population,
  100.0 * Sum(census.popn_white) / Sum(census.popn_total) AS white_pct,
  100.0 * Sum(census.popn_black) / Sum(census.popn_total) AS black_pct
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_census_blocks AS census
ON ST_Intersects(neighborhoods.geom, census.geom)
WHERE neighborhoods.boroname = 'Manhattan'
GROUP BY neighborhoods.name
ORDER BY white_pct DESC;

  neighborhood_name  | population | white_pct | black_pct
---------------------+------------+-----------+-----------
 Carnegie Hill       |      18763 |      90.1 |       1.4
 North Sutton Area   |      22460 |      87.6 |       1.6
 West Village        |      26718 |      87.6 |       2.2
 Upper East Side     |     203741 |      85.0 |       2.7
 Soho                |      15436 |      84.6 |       2.2
 Greenwich Village   |      57224 |      82.0 |       2.4
 Central Park        |      46600 |      79.5 |       8.0
 Tribeca             |      20908 |      79.1 |       3.5
 Gramercy            |     104876 |      75.5 |       4.7
 Murray Hill         |      29655 |      75.0 |       2.5
 Chelsea             |      61340 |      74.8 |       6.4
 Upper West Side     |     214761 |      74.6 |       9.2
 Midtown             |      76840 |      72.6 |       5.2
 Battery Park        |      17153 |      71.8 |       3.4
 Financial District  |      34807 |      69.9 |       3.8
 Clinton             |      32201 |      65.3 |       7.9
 East Village        |      82266 |      63.3 |       8.8
 Garment District    |      10539 |      55.2 |       7.1
 Morningside Heights |      42844 |      52.7 |      19.4
 Little Italy        |      12568 |      49.0 |       1.8
 Yorkville           |      58450 |      35.6 |      29.7
 Inwood              |      50047 |      35.2 |      16.8
 Washington Heights  |     169013 |      34.9 |      16.8
 Lower East Side     |      96156 |      33.5 |       9.1
 East Harlem         |      60576 |      26.4 |      40.4
 Hamilton Heights    |      67432 |      23.9 |      35.8
 Chinatown           |      16209 |      15.2 |       3.8
 Harlem              |     134955 |      15.1 |      67.1

ここで何がおこっているのでしょう? 概念上 (実際の評価順はデータベース内部で最適化されています) は次のことが発生しています:

"JOIN" 節で、地区とセンサスの両方のテーブルからのコラムを持つ、仮想的なテーブルが発生します。
"WHERE" 節で、仮想テーブルをマンハッタン内の行にフィルタリングします。
残る行は地区名でグループ化され、人口値を Sum() する集約関数に渡されます。
最後の数に関する計算とフォーマット調整 (例: GROUP BY, ORDER BY) の後に、クエリはパーセンテージを吐き出します。

注釈

JOIN 節は、二つの FROM の項目を結合します。デフォルトでは INNER JOIN を使いますが、4種の結合のタイプがあります。詳細については、PostgreSQL ドキュメントで定義されている join_type をご覧下さい。

距離テストも結合キーとして使うことができます。「半径内の全ての地物」クエリを生成する場合に使います。距離クエリを使ってニューヨークの人種地理を見ていきましょう。

最初に、市の人種構成の基準を得ましょう。

SELECT
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks;

    white_pct     |    black_pct     | popn_total
------------------+------------------+------------
 44.0039500762811 | 25.5465789002416 |    8175032

ニューヨーク市には 800万人がいて、44% が「白人」で、26% が「黒人」と記録されています。

デューク・エリントンは、かつて「あなたは / A列車に乗らないと / だって / ハーレムのシュガーヒルにいくのでしょう」と歌いました。先に見た通り、ハーレムはアフリカ系アメリカ人の人口がはるかに多くなっています (80.5%)。デューク・エリントンの A列車もそう言えるでしょうか?

まず、nyc_subway_stations テーブルの routes フィールドの内容は、A列車を見つけるのに興味を持っていることに注意して下さい。そこの値は少し複雑です。

SELECT DISTINCT routes FROM nyc_subway_stations;

A,C,G
4,5
D,F,N,Q
5
E,F
E,J,Z
R,W

注釈

DISTINCT キーワードは結果から重複する行を削除します。DISTINCT を使わない場合には、クエリの結果は 73件でなく 491件となります。

よって A列車を見つけるには、routes 内の 'A' を含む全ての行が必要です。これは様々な方法がありますが、今回は routes フィールド内に 'A' がある場合にだけ strpos(routes,'A') が 0 以外の値を返すのを使います。

SELECT DISTINCT routes
FROM nyc_subway_stations AS subways
WHERE strpos(subways.routes,'A') > 0;

A,B,C
A,C
A
A,C,G
A,C,E,L
A,S
A,C,F
A,B,C,D
A,C,E

A系統の200メートル内の人種構成を要約しましょう。

SELECT
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
WHERE strpos(subways.routes,'A') > 0;

    white_pct     |    black_pct     | popn_total
------------------+------------------+------------
 45.5901255900202 | 22.0936235670937 |     189824

A系統に沿った人種構成はニューヨーク市全体の人種構成と根本的に違いはありません。

13.2. 高度な結合¶

最後のセクションでは、A列車が市の残りの領域の人種構成から大きく異なって人口に影響を与えるようなことがなかったことが分かりました。平均的でない人種構成を持つ列車はあるでしょうか?

その問に答えるには、これまでのクエリに他の結合を追加して、多数の地下鉄路線の構成を一括して同時に計算できるようにします。これを行うには、要約したい路線の全てを列挙する新しいテーブルを生成する必要があります。

CREATE TABLE subway_lines ( route char(1) );
INSERT INTO subway_lines (route) VALUES
  ('A'),('B'),('C'),('D'),('E'),('F'),('G'),
  ('J'),('L'),('M'),('N'),('Q'),('R'),('S'),
  ('Z'),('1'),('2'),('3'),('4'),('5'),('6'),
  ('7');

これで地下鉄路線のテーブルを元のクエリに結合できます。

SELECT
  lines.route,
  100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
  100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
  Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
JOIN subway_lines AS lines
ON strpos(subways.routes, lines.route) > 0
GROUP BY lines.route
ORDER BY black_pct DESC;

 route | white_pct | black_pct | popn_total
-------+-----------+-----------+------------
 S     |      39.8 |      46.5 |      33301
 3     |      42.7 |      42.1 |     223047
 5     |      33.8 |      41.4 |     218919
 2     |      39.3 |      38.4 |     291661
 C     |      46.9 |      30.6 |     224411
 4     |      37.6 |      27.4 |     174998
 B     |      40.0 |      26.9 |     256583
 A     |      45.6 |      22.1 |     189824
 J     |      37.6 |      21.6 |     132861
 Q     |      56.9 |      20.6 |     127112
 Z     |      38.4 |      20.2 |      87131
 D     |      39.5 |      19.4 |     234931
 L     |      57.6 |      16.8 |     110118
 G     |      49.6 |      16.1 |     135012
 6     |      52.3 |      15.7 |     260240
 1     |      59.1 |      11.3 |     327742
 F     |      60.9 |       7.5 |     229439
 M     |      56.5 |       6.4 |     174196
 E     |      66.8 |       4.7 |      90958
 R     |      58.5 |       4.0 |     196999
 N     |      59.7 |       3.5 |     147792
 7     |      35.7 |       3.5 |     102401

前のように、結合で、"JOIN ON" 制約に合致した全てのありえる組合せの仮想テーブルが生成され、これらの行は "GROUP" による集約に渡されます。ST_DWithin 関数が空間処理のミソで、適切な地下鉄駅に近い統計区だけを計算に取り込むようにします。

13.3. 関数リスト¶

ST_Contains(geometry A, geometry B): Bの点がAの外部に全く存在せず、Bの内部の少なくとも一つの点がAの内部に存在する場合にのみ TRUE を返します。

ST_DWithin(geometry A, geometry B, radius): ジオメトリと他のジオメトリとの距離が指定した距離以内である場合に TRUE を返します。

ST_Intersects(geometry_a, geometry_b): ジオメトリ/ジオグラフィが「空間的にインタセクトする」 - (空間の任意部分を共有する)場合にはTRUEを返し、そうでない(接続されていない、Disjoint)場合にはFALSEを返します。

round(v numeric, s integer): 小数点以下の桁数を s 桁に丸めるPostgreSQL 数学関数

strpos(string, substring): 指定した部分文字列の位置を示す整数を返す PostgreSQL 文字列関数。

sum(expression): レコード集合のレコードの合計を返すPostgreSQL 集約関数です。

Introduction to PostGIS

13. 空間結合¶

13.1. 結合と要約¶

13.2. 高度な結合¶

13.3. 関数リスト¶

ナビゲーション