NHTSA 交通事故データ

BigQuery を使用して一般公開データセットをクエリする方法

BigQuery は、フルマネージドのデータ ウェアハウスおよび分析プラットフォームです。SQL クエリを使用して分析できる一般公開データセットが用意されています。BigQuery の一般公開データセットにアクセスするには、ウェブ UIコマンドライン ツールを使用します。また、クライアント ライブラリJava.NETPython など)を使用して BigQuery REST API を呼び出してアクセスすることもできます。

現在、BigQuery の一般公開データセットは US マルチリージョン ロケーションに格納されています。一般公開データセットをクエリする場合は、コマンドラインで --location=US フラグを指定するか、BigQuery ウェブ UI で処理ロケーションとして US を選択するか、API でジョブリソースjobReference セクションの location プロパティを指定します。一般公開データセットは米国内に格納されているため、一般公開データのクエリ結果を別のリージョンにあるテーブルに書き込むことはできず、一般公開データセットのテーブルを別のリージョンにあるテーブルと結合することもできません。

BigQuery の一般公開データセットの使用を開始するには、プロジェクトを作成または選択します。毎月、最初の 1 テラバイトのデータの処理については課金されないため、課金を有効にせずに一般公開データセットのクエリを開始できます。無料枠を超える可能性がある場合は、課金を有効にする必要があります。

  1. Google アカウントにログインします。

    Google アカウントをまだお持ちでない場合は、新しいアカウントを登録します。

  2. GCP プロジェクトを選択または作成します。

    [リソースの管理] ページに移動

  3. プロジェクトに対して課金が有効になっていることを確認します。

    課金を有効にする方法について

  4. 新しいプロジェクトでは、BigQuery が自動的に有効になります。既存のプロジェクトで BigQuery を有効にする手順は以下のとおりです。 BigQuery API を有効にする。

    Enable the API

データセットの概要

この一般公開データセットは、米国交通局の国道交通安全管理局(NHTSA)によって作成され、死亡者が発生した交通事故のさまざまな側面を記述する 20 のテーブルが含まれています。交通事故の側面には、たとえば、車両と道路の種類、事故前の運転状況、歩行者と自転車の関与などがあります。

BigQuery コンソールで、このデータを探索してみましょう。

NHTSA 交通事故データに移動

サンプルクエリ

BigQuery を使用してこのデータで実行できる SQL クエリの例をいくつか示します。

これらのサンプルでは BigQuery の標準 SQL サポートを使用します。#standardSQL タグを使用すると、標準 SQL を使用することが BigQuery で認識されます。#standardSQL 接頭辞の詳細については、クエリ接頭辞の設定をご覧ください。

2015 年に死亡事故が最も多かった州は?

NHTSA は、各事故に一意のインデックス番号を割り当てます。このデータセットのほとんどのテーブルで、consecutive_number 列にこの番号が含まれています。この列は、一意の事故をカウントしたり、複数のテーブルを結合したりするために使用できます。次のクエリでは、州ごとの事故数をカウントし、死亡者数を集計して、事故当たりの致死率を計算します。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  state_name,
  COUNT(consecutive_number) AS accidents,
  SUM(number_of_fatalities) AS fatalities,
  SUM(number_of_fatalities)/COUNT(consecutive_number) AS fatalities_per_accident
FROM
  `bigquery-public-data.nhtsa_traffic_fatalities.accident_2015`
GROUP BY
  state_name
ORDER BY
  fatalities_per_accident DESC

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  state_name,
  COUNT(consecutive_number) AS accidents,
  SUM(number_of_fatalities) AS fatalities,
  SUM(number_of_fatalities)/COUNT(consecutive_number) AS fatalities_per_accident
FROM
  `bigquery-public-data.nhtsa_traffic_fatalities.accident_2015`
GROUP BY
  state_name
ORDER BY
  fatalities_per_accident DESC'

サンプルの結果を以下に示します。

+----------------+----------------+-------------+--------------------------+
| state_name     | accidents      | fatalities  | fatalities_per_accident  |
+----------------+----------------+-------------+--------------------------+
| North Dakota   | 111            | 131         | 1.18018018               |
| South Dakota   | 115            | 133         | 1.156521739              |
| Vermont        | 50             | 57          | 1.14                     |
| Iowa           | 282            | 320         | 1.134751773              |
| Nebraska       | 218            | 246         | 1.128440367              |
| Texas          | 3124           | 3516        | 1.125480154              |
| Arkansas       | 472            | 531         | 1.125                    |
| Wyoming        | 129            | 145         | 1.124031008              |
| Mississippi    | 604            | 677         | 1.120860927              |
| New Mexico     | 269            | 298         | 1.107806691              |
+----------------+----------------+-------------+--------------------------+

次のリンクをクリックすると、Google データスタジオでこのデータの可視化サンプルが表示されます。

乗員以外の最も危険な行動は?

このデータセットには、車両の乗員以外の事故の死傷者に関する情報が含まれています。また、事故直前の乗員以外の行動を示すテーブルも含まれています。これらのテーブルを組み合わせることで、事故前のどの行動が最も死傷につながりやすいかを判別できます。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  a.injury_severity_name AS severity,
  b.non_motorist_action_circumstances_name AS prior_action,
  COUNT(a.person_number) AS total
FROM
  `bigquery-public-data.nhtsa_traffic_fatalities.person_2015` a
JOIN
  `bigquery-public-data.nhtsa_traffic_fatalities.nmprior_2015` b
ON
  a.person_number=b.person_number
  AND a.consecutive_number=b.consecutive_number
WHERE
  a.injury_severity = 3
  OR a.injury_severity = 4
GROUP BY
  severity,
  prior_action
ORDER BY
  total DESC

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  a.injury_severity_name AS severity,
  b.non_motorist_action_circumstances_name AS prior_action,
  COUNT(a.person_number) AS total
FROM
  `bigquery-public-data.nhtsa_traffic_fatalities.person_2015` a
JOIN
  `bigquery-public-data.nhtsa_traffic_fatalities.nmprior_2015` b
ON
  a.person_number=b.person_number
  AND a.consecutive_number=b.consecutive_number
WHERE
  a.injury_severity = 3
  OR a.injury_severity = 4
GROUP BY
  severity,
  prior_action
ORDER BY
  total DESC'

サンプルの結果を以下に示します。

+-------------------+-------------------------------------------------------------------------+----------------+
| severity          | prior_action                                                            | total          |
+-------------------+-------------------------------------------------------------------------+----------------+
| Fatal Injury (K)  | Crossing Roadway                                                        | 3429           |
| Fatal Injury (K)  | Movement Along Roadway with Traffic (In or Adjacent to Travel Lane)     | 889            |
| Fatal Injury (K)  | In Roadway-Other (Working, Playing, etc.)                               | 739            |
| Fatal Injury (K)  | Disabled Vehicle Related (Working on, Pushing, Leaving/Approaching)     | 299            |
| Fatal Injury (K)  | Adjacent to Roadway (e.g., Shoulder, Median)                            | 249            |
| Fatal Injury (K)  | Other                                                                   | 248            |
| Fatal Injury (K)  | Unknown                                                                 | 234            |
| Fatal Injury (K)  | Movement Along Roadway Against Traffic (In or Adjacent to Travel Lane)  | 230            |
| Fatal Injury (K)  | Movement Along Roadway – Direction Unknown (Since 2012)                 | 119            |
+-------------------+-------------------------------------------------------------------------+----------------+

このクエリの結果では、「Crossing Roadway(道路を横断中)」が、死亡につながる行動第 1 位であり、それより下位の原因をいくつか組み合わせた場合より件数が多くなっています。また、車両の進行方向に歩く歩行者の致死率が、進行方向の逆向きに歩く歩行者の致死率のほぼ 4 倍となっていることにも注目してください。このようなさまざまな行動を人々がどの程度の頻度で行うかについての数値は得られないため、これらの結果を使用してリスク評価を割り当てることはできません。

不注意運転による死亡事故の州別発生率は?

運転者の不注意による死亡事故の州別発生率はどのようになっているでしょうか。このクエリでは、他の 2 つの一般公開データセット(米国国勢調査局の郵便番号別人口情報と郵便番号を州名にマップする別のテーブル)のテーブルを組み合わせます。これにより、不注意運転による死亡事故の 1 人当たりの発生率を算出することができます。これは上記のクエリよりも複雑ですが、1 つのデータセットだけでは得られない可能性のある分析情報を、さまざまな一般公開データセットをリンクすることで入手できるということを示しています。

ウェブ UI

ウェブ UI で次のクエリを開きます。

#standardSQL
SELECT
  COUNT(a.consecutive_number) AS total,
  b.driver_distracted_by_name AS distraction,
  a.state_name AS state,
  c.state_pop AS population,
  ROUND((COUNT(a.consecutive_number) / c.state_pop) * 100000, 3) AS rate_per_100000
FROM
  `bigquery-public-data.nhtsa_traffic_fatalities.accident_2015` a
JOIN
  `bigquery-public-data.nhtsa_traffic_fatalities.distract_2015` b
ON
  a.consecutive_number = b.consecutive_number
JOIN (
  SELECT
    SUM(d.population) AS state_pop,
    e.state_name AS state
  FROM
    `bigquery-public-data.census_bureau_usa.population_by_zip_2010` d
  JOIN
    `bigquery-public-data.utility_us.zipcode_area` e
  ON
    d.zipcode=e.zipcode
  GROUP BY
    state ) c
ON
  c.state=a.state_name
WHERE
  b.driver_distracted_by_name != 'Not Distracted'
  AND b.driver_distracted_by_name != 'Unknown if Distracted'
  AND b.driver_distracted_by_name != 'Not Reported'
GROUP BY
  distraction,
  state,
  population,
  c.state_pop
ORDER BY
  rate_per_100000 DESC

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  COUNT(a.consecutive_number) AS total,
  b.driver_distracted_by_name AS distraction,
  a.state_name AS state,
  c.state_pop AS population,
  ROUND((COUNT(a.consecutive_number) / c.state_pop) * 100000, 3) AS rate_per_100000
FROM
  `bigquery-public-data.nhtsa_traffic_fatalities.accident_2015` a
JOIN
  `bigquery-public-data.nhtsa_traffic_fatalities.distract_2015` b
ON
  a.consecutive_number = b.consecutive_number
JOIN (
  SELECT
    SUM(d.population) AS state_pop,
    e.state_name AS state
  FROM
    `bigquery-public-data.census_bureau_usa.population_by_zip_2010` d
  JOIN
    `bigquery-public-data.utility_us.zipcode_area` e
  ON
    d.zipcode=e.zipcode
  GROUP BY
    state ) c
ON
  c.state=a.state_name
WHERE
  b.driver_distracted_by_name != 'Not Distracted'
  AND b.driver_distracted_by_name != 'Unknown if Distracted'
  AND b.driver_distracted_by_name != 'Not Reported'
GROUP BY
  distraction,
  state,
  population,
  c.state_pop
ORDER BY
  rate_per_100000 DESC'

サンプルの結果を以下に示します。

+-----------+-----------------------------------------------------+----------------+-------------+---------------------+
| total     | distraction                                         | state          | population  | rate_per_100000     |
+-----------+-----------------------------------------------------+----------------+-------------+---------------------+
| 117       | Inattention (Inattentive), Details Unknown          | New Mexico     | 6161043     | 1.8990291091946607  |
| 149       | Inattention (Inattentive), Details Unknown          | Kentucky       | 12977133    | 1.1481734833109902  |
| 50        | Inattention (Inattentive), Details Unknown          | Kansas         | 8551884     | 0.5846664898635202  |
| 131       | Inattention (Inattentive), Details Unknown          | New Jersey     | 26375682    | 0.496669621661347   |
| 23        | Inattention (Inattentive), Details Unknown          | Idaho          | 4702578     | 0.4890934291786335  |
| 14        | Looked But Did Not See                              | Montana        | 2965035     | 0.4721698057527146  |
| 106       | Other Distraction                                   | Virginia       | 24003717    | 0.44159827413396013 |
| ...                                                                                                                  |
+-----------+-----------------------------------------------------+----------------+-------------+---------------------+

このクエリ結果は興味深い情報を提供していますが、SELECT ステートメントと GROUP BY ステートメントで不注意の種類を限定しないクエリの方が有用な情報を得られる可能性があります。不注意の種類を限定しなかった場合は、不注意運転による死亡事故の 1 人当たりの発生率が最も高い州が導き出されます。変更されたクエリのサンプル結果は次のとおりです。

+-----------+----------------+-------------+---------------------+
| total     | state          | population  | rate_per_100000     |
+-----------+----------------+-------------+---------------------+
| 128       | New Mexico     | 6161043     | 2.077570307         |
| 207       | Kentucky       | 12977133    | 1.595113497         |
| 43        | Montana        | 2965035     | 1.450235832         |
| 152       | Louisiana      | 13598490    | 1.117771164         |
| 16        | Wyoming        | 1683354     | 0.9504833802        |
| 14        | Kansas         | 8551884     | 0.8769997348        |
| 75        | Washington     | 19240062    | 0.8264006634        |
| ...                                                            |
+-----------+----------------+-------------+---------------------+

データについて

データセット ソース: NHTSA

カテゴリ: 科学

使用: このデータセットは、データセット ソースによって規定されている条件(http://www.data.gov/privacy-policy#data_policy)の下ですべてのユーザーに公開されています。Google はこのデータセットを「現状のまま」提供し、明示、黙示を問わず、いかなる保証も行いません。Google は、このデータセットの使用で、直接または間接の損害が発生したとしても一切の責任を負いません。

更新頻度: 毎年

BigQuery で表示: NHTSA 交通事故データに移動

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

ご不明な点がありましたら、Google のサポートページをご覧ください。