交通情報学特論 第6回「PostgreSQL + PostGIS + QGIS による公共交通データ分析 2」講師：伊藤昌毅

東京大学 大学院情報理工学系研究科 創造情報学専攻 「交通情報学特論」 第6回 2023年5月24日 弥生キャンパス I-REF棟 Hilobby PostgreSQL+PostGIS+QGISによる 公共交通データ分析 東京大学 大学院情報理工学系研究科 附属ソーシャルICT研究センター 創造情報学専攻兼担 伊藤昌毅

感想・コメントありがとうございました • 出席確認を兼ねているので、履修者は必ず提出してください – 次回の水曜日23:59:59を締切としますが、締切後も提出できるはず

交通データ分析

中間レポート： 大都市交通センサスの分析 • 大都市交通センサスから興味深いと思える交通現象を見つけ出し、地図やグラフを 使って説明せよ。 • 分量： 2000字程度 1000字以上 ＋図表を2点以上 – English: more than 500 words • 提出： 6月714日（水）23:59:59まで LMSにてPDFファイルを提出 • SQLを利用した場合は、どのようなSQLを利用したかレポートに含めること（末尾 に付録 appendix として載せてもいい） 。SQLの利用は加点要素である。 • ※ ChatGPTなど生成AIをレポート作成に利用した場合は、何をどのように利用し たかをレポートに含めること（末尾に付録 appendix として載せてもいい）。どの ような使い方をしても減点はしない。 • 課題に対する要望は本日のコメントとしてください

PostgreSQL+PostGIS +QGIS

これまでのSQL • 第3回 – 入門（select … from）、whereによる絞り込み、group by による集計 • 第4回 – union all による表の結合、サブクエリ（入れ子） – create table による出力から新しいテーブル作成 – PostGISによる空間データ処理 • クラスタリング、包含関係判定 – join – QGISとの連携

join

Join: 複数のテーブルをある条件で横に結合 Line 開業年 一日利用者数 駅数 総延長(km) 丸ノ内線 1954 約130万 28 27.4 千代田線 1969 約60万 20 18.8 1978 約30万 17 42.6 Line 山手線 1909 約400万 29 34.5 丸ノ内線 1954 約130万 28 27.4 東京メトロ 2004 常磐線 1895 約100万 69 209.4 千代田線 1969 約60万 20 18.8 東京メトロ 2004 東横線 1926 約70万 21 24.2 京葉線 1978 約30万 17 42.6 JR東日本 1987 井の頭線 1933 約30万 18 12.7 山手線 1909 約400万 29 34.5 JR東日本 1987 京王線 1913 約50万 29 37.9 常磐線 1895 約100万 69 209.4 JR東日本 1987 東横線 1926 約70万 21 24.2 東急電鉄 1922 井の頭線 1933 約30万 18 12.7 京王電鉄 1948 京王線 1913 約50万 29 37.9 京王電鉄 1948 Operator 設立年 従業員数 総売上 東京メトロ 2004 約9,000 約4,000億 JR東日本 1987 約70,000 約3兆 東急電鉄 1922 約5,000 約1,000億 京王電鉄 1948 約3,500 約500億 開業年 一日利用者 駅数 数 京葉線 路線Tableの右に事業者Tableを結合 総延長(km) Operator 設立年

そもそもなぜテーブルを分割する？ • 1つのデータは一箇所で管理することで不整合を防 ぐ • データベースの正規化 – 通常、リレーショナルデータベースはjoinを前提に複数のテー ブルに分割してデータを保存する – 複数のテーブルに分割することを「データベースの正規化」と 呼ぶ • 良くある分割 – マスターテーブル： 人・モノなどの一覧（大きく変化しない） – トランザクションテーブル： 何かの処理の度に追加 Line Operator 丸ノ内線 東京メトロ 千代田線 東京メトロ 京葉線 JR東日本 山手線 JR東日本 常磐線 JR東日本 東横線 東急電鉄 井の頭線 京王電鉄 京王線 京王電鉄

Joinの条件: IDで指定するのが基本 Line 開業年 一日利用者数 駅数 総延長(km) Operator 丸ノ内線 1954 約130万 28 27.4 10 千代田線 1969 約60万 20 18.8 10 京葉線 1978 約30万 17 42.6 20 山手線 1909 約400万 29 34.5 20 常磐線 1895 約100万 69 209.4 20 東横線 1926 約70万 21 24.2 30 井の頭線 1933 約30万 18 12.7 40 京王線 1913 約50万 29 37.9 40 ID Operator 設立年 従業員数 総売上 10 東京メトロ 2004 約9,000 約4,000億 20 JR東日本 1987 約70,000 約3兆 30 東急電鉄 1922 約5,000 約1,000億 40 京王電鉄 1948 約3,500 約500億 • データ各行を区別するIDを付与 – 1行を区別できるIDをプライマリーキー （primary key）と呼ぶ – この例では、OperatorテーブルのIDが相当 – 実際には、Lineを区別するIDもあった方がい い • 外部キー（foreign key） – 他のテーブルを参照するためのキー • 考察: Operator Table に foreign key としてLine IDを持てないか？ – 1行に複数のkeyを設定できない – 一対多の関係では、多の側に外部キーを設定 する

Primary Key について • DBを構築する際は、1行を区別できるID（primary key）を持 たせることが一般的 – Primary key は1つのコラムに限らない。複数のコラムの複合で区別できる場合 も多い • 現実のデータ分析ではIDの存在しないデータも多い – 例： 国土数値情報の駅データ。同名の駅を区別する方法がない – 第4回では、駅名＋都道府県名を組み合わせて primary keyとした（それで区別 が出来ていたかは未検証）

Joinの書き方 • from節の中に2つのテーブル を指定 – 左テーブル join 右テーブル – as で別名を付ける • on のあとに条件を指定 select * from lines as l join operators as o on l.operator = o.id – テーブル別名.コラム名 • 基本的なjoinはIDが一致する 行を結合 – より複雑な条件指定も可能 ;

Joinの種類: どのjoinかを明記する • inner join – 結合条件を満たした行だけを残す • outer join – – – – 結合条件を満たさない行も残す left outer join: 左テーブルは全て残す right outer join full outer join: 左右テーブルを全て残す • cross join – 直積 • 主に使うのは、inner joinと left outer join

Joinの種類による違い Inner join 元データ Line 丸ノ内線 千代田線 京葉線 山手線 常磐線 浅草線 東横線 小田原線 井の頭線 京王線 Operator 東京メトロ JR東日本 京成電鉄 東急電鉄 西武鉄道 京王電鉄 Line Operator 丸ノ内線 東京メトロ 千代田線 東京メトロ 京葉線 JR東日本 山手線 JR東日本 常磐線 JR東日本 東横線 東急電鉄 井の頭線 京王電鉄 京王線 京王電鉄 Left outer join Line Operator 丸ノ内線 東京メトロ 千代田線 東京メトロ 京葉線 JR東日本 山手線 JR東日本 常磐線 JR東日本 浅草線 東横線 東急電鉄 小田原線 井の頭線 京王電鉄 京王線 京王電鉄 • Outer joinは結合条件を満たさない行も残す Full outer join Line Operator 丸ノ内線 東京メトロ 千代田線 東京メトロ 京葉線 JR東日本 山手線 JR東日本 常磐線 JR東日本 京成電鉄 浅草線 西武鉄道 東横線 東急電鉄 小田原線 井の頭線 京王電鉄 京王線 京王電鉄

第4回からの目標

SQLを用いて 必要なデータを作成

必要なデータ • 必要なデータをSQL の処理で生成 • まずはどのような データを作る必要が あるかイメージする – 2次ODデータを利用 – 今回は鉄道会社が違って も同名の駅はまとめる • 駅名、定期券人数、 切符人数、位置情報 が必要

前回作成したデータ（1） 2次ODデータより analysis.transit_pass_summary 1499件

前回作成したデータ（2） 定期券データより analysis.od_level2_summary 1453件

前回作成したデータ（3） 国土数値情報 鉄道より • 前回：k-means によるクラスタリングを行い、駅名とクラス ターIDを用いて集計 – 鉄道会社、路線が異なっても、同じ駅を1つにまとめたい – 駅名だけでは全国に存在する同一駅名を区別できないため

駅名+クラスタIDでグループ化するSQL select • xn02_005 as name, cluster_id, ST_Centroid(ST_Union(wkb_geometry)) as center, ST_Union(wkb_geometry) as geom from( select ST_ClusterKmeans(wkb_geometry, 500) over () as cluster_id, * from base.station )as data1 group by n02_005, cluster_id ;

重複駅の判別のため県名、市区町村名を追加 • 国土数値情報 市町村境界データを組み合わせることで可能

ST_Contains()を条件として join • 1番目の引数のGeometryのエリアに、2番目のGeometryが含 まれることを判定 • 対応しない駅もあるため、 left outer join とする – 関東地方の市町村境界データを持っているため select * from( --ここにデータを抽出するSQLをサブクエリとして入れる ) as data2 left outer join base.municipal_boundaries as b on ST_Contains(b.wkb_geometry, data2.center) ;

市区町村名の形式の統一 • 大都市交通センサスと同一形式で県名、都市名を追加 – 23区： city として区名を入れる – 政令指定都市： ○○市○○区 – 町村： ○○町/○○村 （郡名は入れない） • Select節の中で場合分け – n03_003が○○市の時だけ、結合 – PostgreSQLにて、文字列結合は || case when n03_003 like '%市' then n03_003||n03_004 else n03_004 end as city, コラム名 内容 n03_001 都道府県名 n03_002 支庁名 n03_003 郡名・政令指定都市名 n03_004 区名、市町村名 n03_007 行政区域コード

ここまでのSQL drop table if exists analysis.station_master; create table analysis.station_master as select data2.name, data2.cluster_id, b.n03_001 as prefecture, case when n03_003 like '%市' then n03_003||n03_004 else n03_004 end as city, data2.center, data2.geom from( select n02_005 as name, cluster_id, ST_Centroid(ST_Union(wkb_geometry)) as center, ST_Union(wkb_geometry) as geom from( select ST_ClusterKmeans(wkb_geometry, 500) over () as cluster_id, * from base.station )as data1 group by n02_005, cluster_id ) as data2 left outer join base.municipal_boundaries as b on ST_Contains(b.wkb_geometry, center) ;

緯度経度から市区町村自動判別の難しさ • 国土数値情報の市町村境界の精度が1/25000地図相当であり、正確ではない 気がする…

国土数値情報 鉄道＋市町村境界を用いて作成した 駅マスター analysis.station_master 9008件 授業データは関東地方の市町村境界しか含まないため、県名や市町村名がない駅データも多数

データ結合

仕上げ： データの結合 • 以下の3データを結合する – 2次ODデータの集約 – 定期券データの集約 – 駅データの集約 • 駅を特定するprimary key – 駅名+県名 – 市町村名は誤判定もあるため結合には用いない

2次ODデータ＋定期券データのjoin • 2次ODデータ: 1499件 • 定期券データ: 1453件 • inner joinの結 果： 1446件 select od.station, od.prefecture, od.city, od.ticket_departure, od.ticket_arrival, od.ticket_total, pass.commuting * 2 as commuting, pass.school * 2 as school, pass.teiki_total * 2 as teiki_total, od.ticket_total + pass.teiki_total*2 as total from analysis.od_level2_summary as od inner join analysis.transit_pass_summary as pass on od.station = pass.station and od.prefecture = pass.prefecture ;

さらに駅マスターを結合しテーブル作成 drop table if exists analysis.station_statistics; create table analysis.station_statistics as select od.station, od.prefecture, od.city, od.ticket_departure, od.ticket_arrival, od.ticket_total, pass.commuting * 2 as commuting, pass.school * 2 as school, pass.teiki_total * 2 as teiki_total, od.ticket_total + pass.teiki_total*2 as total, st.center, st.geom from analysis.od_level2_summary as od inner join analysis.transit_pass_summary as pass on od.station = pass.station and od.prefecture = pass.prefecture inner join analysis.station_master as st on od.station = st.name and od.prefecture=st.prefecture ;

join 結果の確認 • 大都市交通センサスの駅に対し、必ず一つの駅データが見つか るのが望ましい – 実際には様々な問題でうまく行かない ひとつも見つからない場合 大都市交通センサス → 駅名の表記のブレ → 県名・都市名推定の誤り → データ作成年の違い （駅の新設や廃止、名称変更） 国土数値情報

17駅が対応する駅がひとつも見つからず • ほとんどが駅名表記のブレが原因 select * from census13.passenger_count as p left outer join base.station_master as s on p.station = s.name and p.prefecture = s.prefecture where s.name is null

join 結果の確認 • 大都市交通センサスの駅に対し、必ず一つの駅データが見つか るのが望ましい – 実際には様々な問題でうまく行かない 2つ以上対応付く場合 大都市交通センサス → 同一条件の駅が複数存在する → マスターデータ作成過程のミス （ひとつの駅に2つ以上のデータで表現） 国土数値情報

小田急公表値との比較 • 多くの駅で近い値 • 代々木上原： – 大都市交通センサスが極端に 少ない。これは、千代田線へ 直通する乗客も乗降客数に加 算しているから • 複数社乗り入れ駅で注意 が必要 – 新宿、代々木上原、下北沢、 町田、藤沢など

鉄道会社によるデータ公開の例 • JR東日本： 各駅の乗車人員 • 小田急：1日平均駅別乗降人員 https://www.jreast.co.jp/passenger/index.html https://www.odakyu.jp/company/railroad/users/

• https://www.jreast.co.jp/passenger/index.html
• https://www.odakyu.jp/company/railroad/users/

小田急線のデータのみ抽出 • 本来これを比較するには一次ODデータの方がふさわしい select st.* from base.station as s inner join analysis.station_statistics as st on ST_Contains(st.geom, s.wkb_geometry) where n02_004='小田急電鉄' order by station ;

QGISでの可視化

QGISからPostgreSQLの接続 • 名前、ホス ト名、デー タベース名 を入力して 「接続テス ト」 • ID, Passが 必要

登録に成功するとデータを選べるように • ブラウザからレイヤにドラッグアン ドドロップでデータを追加出来る • その後は通常のQGISと同様にスタ イルなどを設定

データを重ねて可視化 • base.municipal_boundaries – 市区町村の境界：データの情報が多いのであえて地理院地図を下敷きに使わな かった • base.railroad_section – 線路をうっすらと表示 • analysis.station_master – 駅形状や名称 • analysis.station_statistics – 定期と切符の乗降人数

円グラフの作成 • x

PostGISとjoinの応用

QGIS上のデータをPostgreSQLに持っていく • 第2回授業で実施した国勢 調査人口データを利用 • Qiita記事の後半「PostGIS に読み込ませるためのデー タ書きだし」でも解説 – https://qiita.com/niyalist/item s/d70f471c259211aa1554

• https://qiita.com/niyalist/items/d70f471c259211aa1554

国勢調査データをPostgreSQL dump形式 で保存 • Schema を base, create schema をNO に • ファイル名がtable名になる

コマンドラインからデータインポート • それぞれ書き出したファイルを置いたフォルダをカレントディ レクトリに設定したうえで • Windows C:¥census-data¥population>"C:¥Program Files¥PostgreSQL¥15¥bin¥psql.exe" -U postgres -d TranspoCensus -f population.sql • Mac $ psql -d TranspoCensus -f population.sql – 必要に応じてユーザ名などを指定

駅勢圏人口を算出 • 駅から半径800mの円を求め、その中の人口を算出 – 駅勢圏の距離については都市か地方かなどによって異なる値が用いられる • GISにおけるBuffering – ある地物に対して、指定の距離の領域の図形を作成 – 点ならば円、線ならば幅のある線など

800mをどう指定するか？ • GISは一般的に緯度経度データ を扱う – →縦横方向で1の長さが異なり、緯度 によっても異なるのでこの座標系のま までは正円は扱いづらい • メートル座標系を使いたい場合 は、UTM図法で投影してデー タ処理するのがよい – WGS84 UTM 54N： EPSG:32654

PostGISによるBuffering処理 • ST_Transformで一度 EPSG:32654に変換し、ST_Bufferで 800mの円を作成、更にそれをST_TransformでWGS84に戻す create table analysis.station_center as select ST_Transform(ST_Buffer(ST_Transform(center, 32654), 800), 4326) as buffer, * from analysis.station_master where city is not null ;

人口を面積で按分 • 5次メッシュ（250mメッシュ）の人口を面積で按分 駅勢圏の形状 重複部分を持つ5次メッシュ 重複部分を切り出したメッシュ

• x

drop table if exists analysis.station_population; create table analysis.station_population as select name, prefecture, city, sum(partial_population) as population, center, buffer from( select name, prefecture, city, center, buffer, wkb_geometry, ST_Intersection(wkb_geometry, buffer), population, ST_Area(wkb_geometry::geography) as total_area, ST_Area(ST_Intersection(wkb_geometry, buffer)::geography) as partial_area, ST_Area(ST_Intersection(wkb_geometry, buffer)::geography)/ST_Area(wkb_geometry::geography) as rate, ST_Area(ST_Intersection(wkb_geometry, buffer)::geography)/ST_Area(wkb_geometry::geography) * population as partial_population from( select ST_Transform(ST_Buffer(ST_Transform(center, 32654), 800), 4326) as buffer, * from analysis.station_master ) as data1 inner join base.population as pop on ST_Intersects(data1.buffer, pop.wkb_geometry) )as data1 group by name, prefecture, city, center, buffer ;

次回の案内

講義予定 （全13回） • • • • • • • • • • • • • 1. 交通情報学入門 2. 地理情報システム(GIS)と時空間データベース 1 3. 地理情報システム(GIS)と時空間データベース 2 4. 交通データと計測 1: 基盤データ編 5. ゲスト講義1: 交通事業者とMaaS（藤垣洋平・小田急電鉄株式会社） 6. 交通データと計測 2: 動的データ編 7. 経路検索アルゴリズムと応用 8. 交通シミュレーションの技術と演習 1 9. ゲスト講義2: 交通ビッグデータ分析と活用の実務（太田恒平・株式会社トラフィックブレイン） 10. 交通シミュレーションの技術と演習 2 11. 高度化する交通サービス: ITS（Intelligent Transport Systems）・MaaS (Mobility as a Service) ・自動運転 12. データに基づいた交通政策の可能性 13. 交通情報学の未来（ディスカッション）

本日の課題 • 授業の感想、質問、要望などをコメントしてください • LMSで提出 〜5月31日（水） 23:59まで