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

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

本日： SQL + GIS

本日の目標 • PostgreSQLとQGISを連動してデータ処理・データ分析 • 役割分担: PostgreSQL – データの保存・管理 – 基礎的データ処理 • 前回行った抽出や集計だけでなく、複数データの結合なども行い複雑な処理を実施 • 役割分担： QGIS – データの表示・装飾 – 分析結果の伝達、理解、議論

本日の目標

SQL補足 • 宣言型プログラミング言語 • 大文字と小文字 • 最後のセミコロン

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

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

SQL復習

分析SQL: 表から表に変換する穴埋め型言語 select 出力する列（コラム）に関する記述 どの列をどのような表現で表示するか ＊は全部出力 from 入力する表（テーブル）の指定 where 出力する行に関する記述 どのような条件で絞り込むか

集約関数 • テーブル複数行にわたってデータを処理し、ひとつの結果を出 力 • 主な集約関数 – – – – – count: 個数 Sum: 合計 avg: 平均値 min : 最小値 max : 最大値

group by節: 特定の項目単位で集計 • group by 節に指定したコラムで票を集計 – 集約関数と同時に用いることで、特定の条件でのテーブルの集計が可能に select 出力する列（コラム）に関する記述 どの列をどのような表現で表示するか ＊は全部出力 4番目 from 入力する表（テーブル）の指定 1番目 where 出力する行に関する記述 どのような条件で絞り込むか 2番目 どの項目で集計するか 3番目 group by 5番目に集約関数の実行

2次ODデータの集計

2次ODデータの集計 • ODの組み合わせで集計されたデータをOだけ、Dだけで集計すれ ば、駅毎の切符による乗車降車人数が分かる select destination_station as station, sum(passenger_count) as arrival_count from census13.od_level2 group by destination_station • 到着駅で集計 • 本当にこれでいい？同名の駅が複数あった場合は？ – 首都圏の範囲でも、入谷駅、小川町駅は2つずつある

参考： 大都市交通センサス（首都圏）における 同名の駅 select • 小川町、入谷駅が同名の駅が二つ存在 * from census13.passenger_count where station in ('小川町', '入谷') order by station

駅の同一性を保ちながら集計 • 駅名、都道府県名、市区町村名の組み合わせで集計 – それによってデータを1つ確定できるキーを Primary keyと呼ぶ select destination_station as station, destination_prefecture as prefecture, destination_city as city, sum(passenger_count) as arrival_count from census13.od_level2 group by destination_station, destination_prefecture, destination_city order by station

同様の処理を出発駅に対しても行う select origin_station as station, origin_prefecture as prefecture, origin_city as city, sum(passenger_count) as departure_count from census13.od_level2 group by origin_station, origin_prefecture, origin_city

2つの集計結果を統合したい • 横一列に、 駅名、乗車 人数、降車 人数が並ぶ イメージ

union all, union: 検索結果の結合 • 複数の検索結果を union all で繋げることで、1つのセットに なる – コラムの数やデータ型が一致している必要 • union は重複する行を削除する。 union all は重複したらその まま

出発、到着を別に集計できるようにするには？ • select 節を工夫して いったんこの形に

結果を更に集計したい • これまでは、 from 節にはデータベースに保存されたテーブル 名を入力していた • データベースの出力結果を入力として扱い、さらにSQLで処理

サブクエリ： 出力されたテーブルを更に入力に • from 節の中の () に、SQLを入れればいい – as データ名 を付けること • サブクエリは、from 節以外でも使えること に注意

select station, prefecture, city, sum(departure_count) as departure, sum(arrival_count) as arrival, sum(departure_count) + sum(arrival_count) as total ここまでのまとめ from( select destination_station as station, destination_prefecture as prefecture, destination_city as city, 0 as departure_count, sum(passenger_count) as arrival_count from census13.od_level2 group by destination_station, destination_prefecture, destination_city union all select origin_station as station, origin_prefecture as prefecture, origin_city as city, sum(passenger_count) as departure_count, 0 as arrival_count from census13.od_level2 group by origin_station, origin_prefecture, origin_city )as data1 group by station, prefecture, city

集計結果を新しいテーブルとして保存 • create table as に続けて検索SQLを書くことで、検索結果を テーブル化できる • 分析作業用に新しいスキーマを作成してそこに保存 create schema analysis; create table analysis.census13_summary as ・・・・ （前ページのSQL）

定期券データ集計

同様の考え方で定期券データも集計 • 定期券データは、便宜的にODがあるものの区別に意味はない • ODの駅を定めていない以下のデータは集計から除く • data_type=‘b1’ については数値を3倍する

まずは origin_station に注目し集約 select • x origin_station as station, origin_prefecture as prefecture, origin_city as city, data_type, sum(commuting_tickets) as commuting, sum(school_tickets) as school, sum(total_tickets) as total from census13.transit_pass where not origin_station='不明' group by origin_station, origin_prefecture, origin_city, data_type

case when による条件分岐 • select 節の中で case when を用いてdata_type=‘b1’の場合 を処理する case when data_type='b1' then sum(commuting_tickets)*3 else sum(commuting_tickets) end as commuting, case when data_type='b1' then sum(school_tickets) * 3 else sum(school_tickets) end as school, case when data_type='b1' then sum(total_tickets)*3 else sum(total_tickets) end as total

select origin_station as station, origin_prefecture as prefecture, origin_city as city, data_type, case when data_type='b1' then sum(commuting_tickets)*3 else sum(commuting_tickets) end as commuting, case when data_type='b1' then sum(school_tickets) * 3 else sum(school_tickets) end as school, case when data_type='b1' then sum(total_tickets)*3 else sum(total_tickets) end as total ここまでの まとめ from census13.transit_pass where not origin_station='不明' group by origin_station, origin_prefecture, origin_city, data_type

ODデータの場合と同様に進める • destination_station の場合の SQLを同様に記述 • union all で結合 • その結果をサブクエリとして、 更に集約 • create table で集計結果を テーブルに保存 create table analysis.transit_pass_count as select station, prefecture, city, sum(commuting) as commuting_pass, sum(school) as school_pass, sum(total) as total_pass from( -- ここにサブクエリを入力 ) as data1 group by station, prefecture, city order by total_pass desc

ここまでやったこと • 2次ODデータ、定期券デー タそれぞれを駅単位で集計 • ビッグデータをビッグデー タのまま集計 – 当初SQLはデータ抽出から始めた が、むしろ全量を対象に集計する 方がデータ全体の特徴の理解につ ながる – 数百万件のデータが十分高速に処 理が可能 • さらに駅の位置情報が必要 目標とするデータの形

join

複数のテーブルを結合（join）できる • 通常、リレーショナルデータベースはjoinを前提に複数のテー ブルに分割してデータを保存する – 1つのデータを複数箇所に置かないように – 複数のテーブルに分割することを「データベースの正規化」と呼ぶ

Inner join • 双方に要素を含む行だけが残る

Left outer join • 左側（joinされる側）のデータは全て残し、合 致するデータが結合される – 見つからない部分はnullとなる

駅データ作成

第2回スライド 国土数値情報 • 国土交通省が整備している基礎的なGISデータ集

第2回スライド 国土数値情報 鉄道データ

第2回スライド 国土数値情報 行政区域データ • 市区町村の境界を入手可能 • https://nlftp.mlit.go.jp/k sj/gml/datalist/KsjTmplt -N03-v3_1.html

• https://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03-v3_1.html

PostGISの活用 • PostgreSQLのデータ型として、geometry 型を追加 • 図形的、地理的な演算が可能に • 例 – 距離・面積計算、中心の算出、包含関係の判定、図形の切り出し – 空間的なクラスタリング • PostGISが提供する関数はマニュアルを参照（翻訳あり）

駅データをまとめる（集約関数の応用） • 例：東京駅は事業者や路線など毎に12個存在 – ホームの数というわけではないので解釈は難しいが…

駅データ集約 select n02_005 as name, ST_Centroid(ST_Union(wkb_geometry)) as center from base.station group by n02_005 ; • 方針 – 事業者名を問わず、同じ名前の駅をひとつにまとめる – 座標を中心点1点とする • PostGISの集約関数を利用 – count(*), sum(value), avg(value) などの一種として – ST_Union(geom) → 複数の要素をひとつの図形に足し合わせる – ST_Centroid(geom)→ 図形の中心を計算

うまくうまくいった事例、うまく行かない事例 create table base.tmp_station_maps as select n02_005 as name, ST_Centroid(ST_Union(wkb_geometry)) as geom from base.station group by n02_005 having n02_005 in('東京','新宿','赤坂', '青海','我孫子','浦安') ; • havingは group by した後に 動作する

クラスタリング（k-means法）と併用 • PostGISには複数のクラスタリングアルゴリズムが実装されている が、ここではk-means法を利用 – 個人的に、k=500で試した select ST_ClusterKMeans(wkb_geometry, 500) over() as cluster_id, base.station.* from base.station • 正確に行うためには、緯度経度座標ではなく、メートル座標などに 変換してからの方がいいだろう – 緯度経度では縦横のスケールが違うため、距離計算の精度が低い k=500の場合

クラスタリングが出来ているか確認 select * from( select ST_ClusterKMeans(wkb_geometry, 500) over() as cluster_id, base.station.* from base.station ) as data1 where n02_005 in('赤坂', '青海','我孫子','浦安','大久保','大手町') order by n02_005, cluster_id • 注意）同一駅なのに別クラスターになった可能性は確認できていな い

駅名+クラスタIDでグループ化 select over (order by cluster_id) as id, • xrow_number() cluster_id, n02_005 as name, 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, base.station.* 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 create table analysis.station_master as select data2.id, data2.cluster_id, data2.name, 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 row_number() over (order by cluster_id) as id, cluster_id, n02_005 as name, 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, base.station.* 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, data2.center)

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

データ結合

仕上げ： データの結合 • 以下の3データを結合する – 2次ODデータの集約 – 定期券データの集約 – 駅データの集約

create table analysis.station_statistics as ここまでのSQL select p.station, p.prefecture, p.city, p.departure, p.arrival, t.commuting_pass * 2 as commuting_pass, t.school_pass * 2 as school_pass, p.total as ticket_total, t.total_pass * 2 as pass_total, p.departure + p.arrival + t.commuting_pass *2 + t.school_pass * 2 as total, sm.id, sm.center, sm.geom • 大都市交通センサ スのデータ同士 は、inner join – どちらかが無くなっ ても困る • station_masterは left outer join from census13.passenger_count as p inner join census13.transit_pass_count as t on p.station = t.station and p.prefecture = t.prefecture and p.city = t.city left outer join base.station_master as sm on p.station = sm.name and p.prefecture = sm.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つ以上のデータで表現） 国土数値情報

select * from( select • count(*) over (partition by station, prefecture), * 二つ以上の データと紐 付いたもの を探すクエ リー from( select p.*, s.id, s.name, s.prefecture as base_prefecture, s.city as base_city, s.center, s.geom from census13.passenger_count as p left outer join base.station_master as s on p.station = s.name and p.prefecture = s.prefecture ) as data1 ) as data2 where count > 1

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

鉄道会社によるデータ公開の例 • 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/

QGISでの可視化

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

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

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

円グラフの作成 • x

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

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

次回の案内

講義予定 （全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. 交通情報学の未来（ディスカッション）

次回： ゲスト講義 藤垣洋平氏（小田急電鉄株式会社） • MaaSの専門家であり、MaaS研究で博士号を取得 • 小田急電鉄という実践の場で次世代モビリティに関わる • 経歴 – – – – – 東京大学 工学部 都市工学科 東京大学 大学院工学系研究科 都市工学専攻 修士課程 株式会社構造計画研究所 東京大学 大学院工学系研究科 都市工学専攻 博士課程 2019年より小田急電鉄株式会社 • 積極的な質問を！是非教室にお越しください（オンラインも可）

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

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