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

5K Views

September 08, 23

スライド概要

動画:
https://youtu.be/yBAOLbnPy6Y

2023年度に伊藤昌毅が東京大学で実施した講義「交通情報学特論」を一部を除いて一般公開します。

この講義は、情報技術との融合によって高度化が進んでいる交通関連技術について概観し、交通データ分析や交通シミュレーション、交通案内サービス構築に必要な技術を身に付けることを目的としています。交通工学や交通計画学など交通を支える技術や学問は、現代の情報技術と融合することで、リアルタイムに大量のデータを分析し、即応的に施策を実施する新しい形へと進化しはじめています。この講義では、交通データの収集、可視化、分析、社会システムへの応用について、最新の事例や研究成果を紹介するとともに、実際の交通データに触れながらプログラミングやデータ分析ツールの利用技術を学びます。交通を学ぶ学生だけでなく、交通に関わる社会人などにも有用であると考え、学生とのディスカッションなどを除き講義内容を広く公開します。

profile-image

伊藤昌毅 東京大学 大学院情報理工学系研究科 附属ソーシャルICT研究センター 准教授。ITによる交通の高度化を研究しています。標準的なバス情報フォーマット広め隊/日本バス情報協会

シェア

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

関連スライド

各ページのテキスト
1.

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

2.

本日: SQL + GIS

3.

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

4.

本日の目標

5.

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

6.

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

7.

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

8.

SQL復習

9.

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

10.

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

11.

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

12.

2次ODデータの集計

13.

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つずつある

14.

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

15.

駅の同一性を保ちながら集計 • 駅名、都道府県名、市区町村名の組み合わせで集計 – それによってデータを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

16.

同様の処理を出発駅に対しても行う 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

17.

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

18.

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

19.

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

20.

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

21.

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

22.

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

23.

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

24.

定期券データ集計

25.

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

26.

まずは 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

27.

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

28.

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

29.

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

30.

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

31.

join

32.

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

33.

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

34.

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

35.

駅データ作成

36.

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

37.

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

38.

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

39.

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

40.

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

41.

駅データ集約 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)→ 図形の中心を計算

42.

うまくうまくいった事例、うまく行かない事例 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 した後に 動作する

43.

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

44.

クラスタリングが出来ているか確認 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 • 注意)同一駅なのに別クラスターになった可能性は確認できていな い

45.

駅名+クラスタ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 ;

46.

だいたいよさそう

47.

重複駅の判別のため県名、市区町村名を追加 • 市町村境界データをうまく組み合わせることで対応可能

48.

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) ;

49.

市区町村名の形式の統一 • 大都市交通センサスと同一形式で県名、都市名を追加 – 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 行政区域コード

50.

ここまでの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)

51.

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

52.

データ結合

53.

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

54.

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 – 対応するものがない ことをしっかり認識 したい ;

55.

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

56.

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

57.

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

58.

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

59.

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

60.

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

61.

QGISでの可視化

62.

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

63.

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

64.

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

65.

円グラフの作成 • x

66.

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

67.

次回の案内

68.

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

69.

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

70.

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

71.

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