交通情報学特論 第3回「地理情報システム(GIS)と時空間データベース2」講師：伊藤昌毅

東京大学 大学院情報理工学系研究科 創造情報学専攻 「交通情報学特論」 第3回 2023年4月26日 弥生キャンパス I-REF棟 Hilobby 地理情報システム(GIS)と時空間データベース 2 東京大学 大学院情報理工学系研究科 附属ソーシャルICT研究センター 創造情報学専攻兼担 伊藤昌毅

SQLによるデータ分析

先週：地理情報システム(GIS)と時空間データ ベース 1 • 交通の理解、分析に地図は欠かせない • 空間情報を扱う手法を身に付ける – 可視化 • 伝えたい情報をどう表現するか – 空間的な演算処理 • 例えば2点の緯度経度から距離を計算できる？ • QGIS演習 – オープンソースのGIS • 空間データの所在地 – 国勢調査、メッシュデータなど

今週：地理情報システム(GIS)と時空間データ ベース 2 • 空間情報の管理や分析のためのSQLを学ぶ – 大量のデータをファイルやExcelで管理、処理するのは非効率 – 手元のPCだけでなく、業務用DB、ビッグデータ処理などにも使える汎用スキル • SQL: リレーショナルデータベースを操作するためのプログラ ミング言語 – PostgreSQL, Oracle Database, MySQL, Google Cloud BigQuery などを操作 する言語 • PostgreSQL + PostGIS – オープンソースのRDBMS – 空間データの独特な処理が可能

SQLとは？ • リレーショナルデータベース（Relational Database Management System, RDBMS）を操作するためのプログラミング言語 – データの定義、検索、追加、更新、削除など • RDBMS – 商用： Oracle Database, Micfosoft Access など – オープンソース： PostgreSQL, MySQL, SQLiteなど – クラウド： Google BigQuery, AWS Redshift など • 歴史ある言語でありながら、今も広く使われる – 1970年代にIBMによって開発 – 1986年から標準化されるが、各ベンダーが拡張

RDBMSの選択肢 • マネージドサービス – Google、Amazonなどのクラウド事業者が提供するRDBMSサービス – インストール、ソフトウェア更新などシステム運用を完全にお任せ • オンプレミス – 自分で管理するPC、サーバにインストールし管理 – 柔軟性は高いがデータ容量の配慮、セキュリティの配慮など管理コストは高い • 個人的お勧め – 超大量のデータをとにかく保存、動的に追加: Google BigQuery – 自分の管理するPCでデータ分析： PostgreSQL

企業によるデータウェアハウスの例 • 多様な企業（IT企業に限らない）がBigQueryなどを用いてク ラウド上にデータ基盤を構築 https://techblog.zozo.com/entry/datainfrastructure-replacement https://hack.nikkei.com/blog/advent20211215/ https://cloud.google.com/blog/ja/topics/customers/freeeadopting-bigquery-to-build-a-data-warehouse/

• https://cloud.google.com/blog/ja/topics/customers/freee-adopting-bigquery-to-build-a-data-warehouse/
• https://hack.nikkei.com/blog/advent20211215/
• https://techblog.zozo.com/entry/data-infrastructure-replacement

営業担当社員でもSQLを扱う企業も • x 社内のデータがBigQueryに集約されていて、マーケター やコールセンターの担当者もSQLを叩いていたり… https://note.com/monotaro_note/n/n7621ef7a10cd https://diamond.jp/articles/-/313666

• https://note.com/monotaro_note/n/n7621ef7a10cd
• https://diamond.jp/articles/-/313666

BI（Business Intelligence）ツールも活況 • SQL不要で様々なデータを取得、分析、可視化

SQL： 2つのアプローチ • システムSQL – – – – Webシステムなどを構築する際のストレージとしてRDBMSを利用 比較的シンプルなクエリを大量、高速に実行 データの追加、更新、取得、削除が全て発生 主にプログラマ、エンジニアの領域 • 分析SQL – – – – 既に存在するデータから知見を得る 営業担当、分析担当などの仕事 データの取得がほとんど（更新、削除はしない） SQLが複雑になりがち（数十行から数百行）

SQLの書籍もどちらのアプローチか見極める必要 • x

R, Python (Pandas)との比較 • データをテーブルに載せて操作するという考え方は類似 • RやPandasは取り扱うデータを一旦メモリに格納する必要 • SQLはデータがディスクに存在することを前提 – ビッグデータに対応 • 長期保存するデータをRDBMS（データウェアハウス）に格納 することが多く、まずはSQLが必要になる

Excel、機械学習などとの連携 • データの大量、長期の蓄積を前提とし、そこからデータを取得 する際に用いるのがSQL • SQL単体でもかなりの分析が出来るが、可視化においては ExcelやQGISなどとの連携、モデル化や予測などについては Pythonなどとの連携が一般的

大都市交通センサスを用いた SQL演習

今週・来週の目標 • 交通ビッグデータ をSQLとGISで可 視化・分析 – 今週：データ整形・分 析 – 来週：複数データの統 合・GISとの統合 https://twitter.com/kasobus/status/1650806981043814401 https://twitter.com/ShinagawaJP/sta tus/1649382482947739648

• https://twitter.com/kasobus/status/1650806981043814401
• https://twitter.com/ShinagawaJP/status/1649382482947739648

大都市交通センサス • 国土交通省が5年毎に首都圏、中京圏、近畿圏の三大都市圏に おいて、鉄道・バス等の大量公共交通機関の利用実態を調査 • 最新データは令和3年度に実施 • 調査方法 – 第12回まで：駅において鉄道利用者に紙の調査票を配布し、郵送等にて回収した うえで拡大する手法にて調査を実施(サンプル調査(32万件) – 第13回： 鉄道ICカードの利用実績をもとに集計する手法(非接触かつ全数調査 (1915万件)等に変更 • 令和5年3月公開 https://www.mlit.go.jp/sogoseisaku/transport/sosei_transport_tk_000007.html

• https://www.mlit.go.jp/sogoseisaku/transport/sosei_transport_tk_000007.html

調査データ • 3大都市圏で実施 – 首都圏、中京圏、近畿圏 • 一件明細調査 – 交通系ICカードから取得 – 2021年12月の平日の2日（日付は非公開） – 定期券の利用は含まない • 定期券調査 – 2021年12月に有効な定期券発売枚数 – 事業者によって回収率が異なる • JR東日本は32.5%

一件明細調査のデータ種類 • 0次OD – 複数のICカード媒体毎の1日の改札機通過データを元に、個人が駅を出場した後に駅に入場するとい う、出場/入場の組み合わせを一組にしたトリップデータ • 1次OD – 複数のICカード媒体毎の1日の改札機通過データを元に、入場/出場を一組にしたトリップデータ • 2次OD – 複数のICカード媒体毎の1日の改札機通過データを元に、入場/出場を一組にしたトリップデータ。ただ し、次のトリップまでの時間がある閾値時間以内の場合は同一トリップとみなす – 閾値は15分、30分、60分の3種類 • 3次OD – 個人の一日の動きを1レコードとして出力したデータ。複数のICカード媒体毎の1日の改札機通過データ から作成した、2次ODデータ(入場/出場を一組にしたトリップデータであり、次のトリップまでの時間 がある閾値時間以内の場合は同一トリップとみなしている)を元に、各IDの入場/出場の組を一日分並べ たトリップデータを作成 – 閾値は15分、30分、60分の3種類

定期券データ • 回収率が事業者によって異なる – JR東日本が約1/3 であり、集計の際は注意が必要 • 区間を定めない全線定期券なども存在する

データ準備

pgAdmin4起動 • PostgreSQLを操作 するためのGUIツー ル – PostgreSQL自体はPC やサーバなどでバック グラウンド動作 • Tools→Query Tool を開く（タブで複 数個開ける）

データベース作成・PostGIS設定 • ServerまたはDatabase上で右クリック Create →Database

PostGIS設定 • 作成したデータベース上で右クリック、 Create →Extension • postgis を選択、Save

簡易な方法： バックアップファイルからDBを復元 • https://www.dropbox.com/s/1v3yuy6widnw4ch/census13-andbase.backup?dl=0 • 新規作成したDBを選択して右クリック→Restore – インデックス再構築などのため時間が掛かる – このようなエラーが出ても問題はない pg_restore: error: could not execute query: ERROR: role "postgres" does not exist –

• https://www.dropbox.com/s/1v3yuy6widnw4ch/census13-and-base.backup?dl=0

Windowsの場合: PATH設定 • C:¥Program Files¥PostgreSQL¥15¥bin • デフォルトにチェックを付ける

本格派： CSVファイルをPostgreSQLに読み込み • データ読み込みは初回に1度だけ実施。ディスクに書き込まれ る • データをPostgreSQLに読み込む方法は、データ種類などによ り複数ある データベー ス作成 PostGIS設 定 テーブル定 義 データ読み 込み実施 後処理 インデック ス作成

データのダウンロード • e-StatからCSV形式でダウン ロード可能 • 一件明細調査は200-500MB程 度のファイルに分割されてお り、統合が必要 • 容量（非圧縮textファイル） – – – – – 0次OD： 6.6GB 1次OD： 2.1GB 2次KD： 8.7GB 3次OD： ? 定期券： 140MB https://www.e-stat.go.jp/statsearch/files?page=1&layout=datalist&toukei=00600020&tstat=000001103355&cycle=0 &tclass1=000001203341&tclass2=000001203350&tclass3=000001203820&tclass4val=0

• https://www.e-stat.go.jp/stat-search/files?page=1&layout=datalist&toukei=00600020&tstat=000001103355&cycle=0&tclass1=000001203341&tclass2=000001203350&tclass3=000001203820&tclass4val=0

スキーマ作成 • スキーマ： データベースに登録されたテーブルを分類するた めのフォルダのようなもの – デフォルトで public が存在 – 大都市交通センサスをまとめる census13 というスキーマを作成

1次ODデータ • x

テーブル定義： CSVファイルの項目名と型を参照 • • • • • • • • • • • • • • • • • 圏域 カード種別 【入場】圏域 【入場】事業者名 【入場】路線名 【入場】駅名 【入場】都道府県 【入場】市町村区 【入場】時間帯 【出場】圏域 【出場】事業者名 【出場】路線名 【出場】駅名 【出場】都道府県 【出場】市町村区 所要時間（５分単位） 人数 CREATE TABLE census13.od_level1 ( zone VARCHAR(20), card_type VARCHAR(20), origin_zone VARCHAR(20), origin_operator VARCHAR(50), origin_line VARCHAR(80), origin_station VARCHAR(80), origin_prefecture VARCHAR(20), origin_city VARCHAR(50), origin_hour INTEGER, destination_zone VARCHAR(20), destination_operator VARCHAR(50), destination_line VARCHAR(80), destination_station VARCHAR(80), destination_prefecture VARCHAR(20), destination_city VARCHAR(50), travel_time INTEGER, passenger_count INTEGER );

クエリーツールからテーブル作成 • テーブル定義が書 けたら実行（▶ボ タンを一度押す）

テーブル作成はGUIでも可能だが勧めない • データがうまく読み込まれない場合に文字数を変えるなど、日調 整が必要になる • その度に入力し直すのは効率が悪い

CSVファイル読み込み • COPY テーブル名 FROM ’ファイル名（絶対パス）’ with (オプ ション) – この例では1日目のデータのみを読み込み COPY census13.od_level1 FROM 'C:¥census-data¥1ji¥1ji_1_1.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level1 FROM 'C:¥census-data¥1ji¥1ji_1_2.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level1 FROM 'C:¥census-data¥1ji¥1ji_1_3.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level1 FROM 'C:¥census-data¥1ji¥1ji_1_4.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level1 FROM 'C:¥census-data¥1ji¥1ji_1_5.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level1 FROM 'C:¥census-data¥1ji¥1ji_1_6.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');

インデックス作成 • やらなくても動作するが検索が遅くなる（大きな違いが出るこ とも） CREATE INDEX od_level1_origin_operator_idx ON census13.od_level1 (origin_operator); CREATE INDEX od_level1_origin_line_idx ON census13.od_level1 (origin_line); CREATE INDEX od_level1_origin_station_idx ON census13.od_level1 (origin_station); CREATE INDEX od_level1_destination_operator_idx ON census13.od_level1 (destination_operator); CREATE INDEX od_level1_destination_line_idx ON census13.od_level1 (destination_line); CREATE INDEX od_level1_destination_station_idx ON census13.od_level1 (destination_station); CREATE INDEX od_level1_od_station_idx ON census13.od_level1 (origin_station, destination_station);

2次ODデータ • 1次ODデータに加えて「経由 情報」 (transfer_station)が 追加 CREATE TABLE census13.od_level2 ( zone VARCHAR(20), card_type VARCHAR(20), origin_zone VARCHAR(20), origin_operator VARCHAR(50), origin_line VARCHAR(80), origin_station VARCHAR(80), origin_prefecture VARCHAR(20), origin_city VARCHAR(50), origin_hour INTEGER, destination_zone VARCHAR(20), destination_operator VARCHAR(50), destination_line VARCHAR(80), destination_station VARCHAR(80), destination_prefecture VARCHAR(20), destination_city VARCHAR(50), travel_time INTEGER, transfer_station VARCHAR(200), passenger_count INTEGER );

2次ODデータ: CSVファイル読み込み・ インデックス作成 • 1日目 30分閾値のデータを読み込み COPY census13.od_level2 FROM 'C:¥census-data¥2ji¥2ji_30_1_1.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level2 FROM 'C:¥census-data¥2ji¥2ji_30_1_2.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level2 FROM 'C:¥census-data¥2ji¥2ji_30_1_3.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level2 FROM 'C:¥census-data¥2ji¥2ji_30_1_4.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level2 FROM 'C:¥census-data¥2ji¥2ji_30_1_5.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level2 FROM 'C:¥census-data¥2ji¥2ji_30_1_6.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); COPY census13.od_level2 FROM 'C:¥census-data¥2ji¥2ji_30_1_7.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8'); • インデックス作成 CREATE INDEX od_level2_origin_operator_idx ON census13.od_level2 (origin_operator); CREATE INDEX od_level2_origin_line_idx ON census13.od_level2 (origin_line); CREATE INDEX od_level2_origin_station_idx ON census13.od_level2 (origin_station); CREATE INDEX od_level2_destination_operator_idx ON census13.od_level2 (destination_operator); CREATE INDEX od_level2_destination_line_idx ON census13.od_level2 (destination_line); CREATE INDEX od_level2_destination_station_idx ON census13.od_level2 (destination_station); CREATE INDEX od_level2_od_station_idx ON census13.od_level2 (origin_station, destination_station);

定期券データ： • 3桁毎カンマ区切りの数字があり、このままでは整数として読 み込みが出来ない • いったん文字列として一時テーブルに読み込んだ後、カンマを 削除し整数に変換して正式なテーブルに読み込む

定期券： テーブル定義 CREATE TABLE census13.transit_pass_tmp ( zone VARCHAR(10), origin_prefecture VARCHAR(10), origin_city VARCHAR(30), origin_operator VARCHAR(30), origin_station VARCHAR(30), transfer_station VARCHAR(200), destination_prefecture VARCHAR(10), destination_city VARCHAR(30), destination_operator VARCHAR(30), destination_station VARCHAR(30), origin_prefecture2 VARCHAR(10), origin_city2 VARCHAR(30), origin_operator2 VARCHAR(30), origin_station2 VARCHAR(30), transfer_station2 VARCHAR(200), destination_prefecture2 VARCHAR(10), destination_city2 VARCHAR(30), destination_operator2 VARCHAR(30), destination_station2 VARCHAR(30), commuting_tickets VARCHAR(20), school_tickets VARCHAR(20), total_tickets VARCHAR(20), data_type CHAR(4) );

定期券： CSVファイル読み込み • Windowsでは日本語ファイル名が正常に動作しなかったため ファイル名をアルファベットに変更 COPY census13.transit_pass_tmp FROM 'C:¥census-data¥teiki¥census13-teiki.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',', ENCODING 'UTF8');

文字列を数値に変換 • カンマ付きの文字列と して読み込んだ整数を、 カンマを削除し整数に 変換した新しいテーブ ルを作成 • 元のテーブルを削除 CREATE TABLE census13.transit_pass as select zone, origin_prefecture, origin_city, origin_operator, origin_station, transfer_station, destination_prefecture, destination_city, destination_operator, destination_station, origin_prefecture2, origin_city2, origin_operator2, origin_station2, transfer_station2, destination_prefecture2, destination_city2, destination_operator2, destination_station2, REPLACE(commuting_tickets, ',', '')::integer as commuting_tickets, REPLACE(school_tickets, ',', '')::integer as school_tickets, REPLACE(total_tickets, ',', '')::integer as total_tickets, data_type from census13.transit_pass_tmp; DROP TABLE IF EXISTS census13.transit_pass_tmp;

定期券： インデックス作成 CREATE INDEX transit_pass_zone_idx ON census13.transit_pass (zone); CREATE INDEX transit_pass_origin_operator_idx ON census13.transit_pass (origin_operator); CREATE INDEX transit_pass_origin_station_idx ON census13.transit_pass (origin_station); CREATE INDEX transit_pass_destination_operator_idx ON census13.transit_pass (destination_operator); CREATE INDEX transit_pass_destination_station_idx ON census13.transit_pass (destination_station); CREATE INDEX transit_pass_data_type_idx ON census13.transit_pass (data_type); CREATE INDEX transit_pass_od_station_idx ON census13.transit_pass (origin_station, destination_station); • ここまでで本格的な読み込みが完了

SQLを試す

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

駅間で検索（三鷹→根津） • 何時台に何人いる？ • 東大前駅、本郷三丁目前駅利用者とどれが多い？ • 今日の自分と同じ経路・時間帯の利用者は何人いた？ select * from census13.od_level2 where origin_station ='三鷹' and destination_station = '本郷三丁目'

見やすくなるようにコラムを絞る • 注目したいコラムだけ select節に明記 select card_type, origin_line, origin_station, destination_line, destination_station, origin_hour, travel_time, transfer_station, passenger_count – 順番も設定可能 • order by で出発時刻順に ソート from census13.od_level2 where origin_station ='三鷹' and destination_station = '本郷三丁目' order by origin_hour

order by: 出力結果の並び替え • 複数の列名を列挙すれば、有線順位のある並び替えが可能 • desc を付けることで逆順に

応用： 同じ区間の定期券の枚数を確認しよう

AND, OR, NOT • 目的地が本郷三丁目、根津、東大前のどれか destination_station in ('本郷三丁目', '根津', '東大前') • destination_line が大江戸線ではない not destination_line = '大江戸線’ destination_line not like '大江戸線' • origin_hourが9時台ではない not origin_hour = 9 • origin_hour が7,8,9時台ではない not origin_hour in (7, 8, 9)

like 文字列を柔軟に検索 • % は何にでも当てはまる • distinct select を実行した結果の重複行をまとめる select select distinct origin_station from distinct origin_station from census13.od_level1 where census13.od_level1 where origin_station like'三鷹%' origin_station like'%浦和%'

応用: 渋谷駅で乗り換える定期券を検索しよう

到着時刻の推定 • データには出発時間帯、移動時間（5分単位）が入力されてい るが、到着時刻が分からず不便 • 到着時刻を簡易に計算してみよう。例えば – 出発時間帯+30分+移動時間 – 出発時間帯+乱数（0-60分）＋移動時間 • → 新しいコラムが追加されたテーブルを出力

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

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

三鷹→本郷三丁目の乗車人数 • 集約関数 sum を使って合計人数を算出

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

応用： • 時間帯ごとに本郷三丁目駅から出発する人数 • 時間帯ごとに本郷三丁目駅に到着する人数

OD表を作ろう • 東京モノレールのOD表、ゆりかもめのOD表 • SQLとExcelの合わせ技で作成 • 同じことは山手線で可能？ • 同じことは小田急線で可能？

公式の乗降人数と比較してみよう • 例：小田急電鉄 • 乗車人数＋降車人数では揃わ ないはず – 定期券を含んでいないため https://www.odakyu.jp/company/railroad/users/

• https://www.odakyu.jp/company/railroad/users/

乗降人員 • 実際の人数とは異なる（かなり上 振れする可能性） – 定期券利用者が毎日1往復する想定 – 直通運転の乗客を計上 • 定期 – 月割りにした枚数×６０人（１カ月＝３０日 ＝を毎日1往復）として算出 • 定期外 – 各駅で発売された普通乗車券、回数券、 一日乗車券、団体券などの発売実績によ る。

データサイエンス100本ノック 構造化データ加工編 • SQL、Python、Rで データ加工を行う演習 • 手元PCでJupyter Lab 上で動作 • ガイドブックもあり https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess

• https://github.com/The-Japan-DataScientist-Society/100knocks-preprocess

ChatGPTとSQLとの相性！ • x

講義予定 （全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月3日（水） 23:59まで