14. 空間結合の演習

これまでに見た全ての関数のリマインダです。演習に役立つはずです!

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

  • count(expression) はレコードの集合のサイズを返す集約関数です

  • ST_Area(geometry) ポリゴンの面積を返します

  • ST_AsText(geometry) は WKT text を返します

  • ST_Contains(geometry A, geometry B) ジオメトリAがジオメトリBを含む場合には TRUE を返します

  • ST_Distance(geometry A, geometry B) ジオメトリAとジオメトリBの距離の最小値を返します

  • ST_DWithin(geometry A, geometry B, radius) ジオメトリAがジオメトリBからの距離が radius 未満の場合には TRUE を返します

  • ST_GeomFromText(text)geometry を返します

  • ST_Intersects(geometry A, geometry B) ジオメトリAがジオメトリBとインタセクトする場合には TRUE を返します

  • ST_Length(linestring) はラインストリングの長さを返します

  • ST_Touches(geometry A, geometry B) ジオメトリAがジオメトリBと接触する場合には TRUE を返します

  • ST_Within(geometry A, geometry B) ジオメトリAがジオメトリBの内側にある場合には TRUE を返します

次の利用可能なテーブルも覚えておいてください:

  • nyc_census_blocks

    • name, popn_total, boroname, geom

  • nyc_streets

    • name, type, geom

  • nyc_subway_stations

    • name, routes, geom

  • nyc_neighborhoods

    • name, boroname, geom

14.1. 演習

  • 'Little Italy'内にある地下鉄駅はどれでしょう? どの地下鉄路線にありますか?

    SELECT s.name, s.routes
    FROM nyc_subway_stations AS s
    JOIN nyc_neighborhoods AS n
    ON ST_Contains(n.geom, s.geom)
    WHERE n.name = 'Little Italy';
    
       name    | routes
    -----------+--------
     Spring St | 6
    
  • 6列車が通る地区を全てを挙げるとどうなるでしょう? (ヒント: nyc_subway_stations``テーブルの ``routes カラムは 'B,D,6,V' や 'C,6' のような値を持ちます)

    SELECT DISTINCT n.name, n.boroname
    FROM nyc_subway_stations AS s
    JOIN nyc_neighborhoods AS n
    ON ST_Contains(n.geom, s.geom)
    WHERE strpos(s.routes,'6') > 0;
    
            name        | boroname
    --------------------+-----------
     Midtown            | Manhattan
     Hunts Point        | The Bronx
     Gramercy           | Manhattan
     Little Italy       | Manhattan
     Financial District | Manhattan
     South Bronx        | The Bronx
     Yorkville          | Manhattan
     Murray Hill        | Manhattan
     Mott Haven         | The Bronx
     Upper East Side    | Manhattan
     Chinatown          | Manhattan
     East Harlem        | Manhattan
     Greenwich Village  | Manhattan
     Parkchester        | The Bronx
     Soundview          | The Bronx
    

    注釈

    一つの地区に複数の地下鉄駅がある時に、結果集合から重複値を削除するには DISTINCT キーワードを使います。

  • 9/11後、'Battery Park' 地区は数日間立ち入り禁止でした。避難しなけれならなかった人の数はどれぐらいでしょうか?

    SELECT Sum(popn_total)
    FROM nyc_neighborhoods AS n
    JOIN nyc_census_blocks AS c
    ON ST_Intersects(n.geom, c.geom)
    WHERE n.name = 'Battery Park';
    
    17153
    
  • 最も人口密度 (人/km2) が高い地区はどこですか?

    SELECT
      n.name,
      Sum(c.popn_total) / (ST_Area(n.geom) / 1000000.0) AS popn_per_sqkm
    FROM nyc_census_blocks AS c
    JOIN nyc_neighborhoods AS n
    ON ST_Intersects(c.geom, n.geom)
    GROUP BY n.name, n.geom
    ORDER BY popn_per_sqkm DESC LIMIT 2;
    
          name       |  popn_per_sqkm
    -------------------+------------------
     North Sutton Area | 68435.13283772678
     East Village      | 50404.48341332535