サンフランシスコ 311 サービス リクエスト データ

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

データセットの概要

このデータには、2008 年 7 月から現在までの 311 サービスへのリクエストがすべて含まれています。このデータは毎日更新されています。311 サービスは、非緊急時の番号であり、急を要しない公共サービスへのアクセスを提供します。

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

SF 311 サービス リクエスト データに移動

サンプルクエリ

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

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

不適切な落書きの発生率が最も高い地区は?

このクエリは、不適切な落書きとそれ以外の落書きを区別する complaint_type 列を使用して、落書きの苦情数合計に対する割合を計算します。

ウェブ UI

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

#standardSQL
SELECT
  neighborhood,
  ROUND(100*COUNTIF(STRPOS(descriptor,
        "- Not_Offensive") > 0) / COUNT(*), 2) AS not_offensive_pct,
  ROUND(100*COUNTIF(STRPOS(descriptor,
        "- Offensive") > 0) / COUNT(*), 2) AS offensive_pct,
  COUNT(*) AS total_count
FROM
  `bigquery-public-data.san_francisco.311_service_requests`
WHERE
  STRPOS(category,
    "Graffiti") > 0
GROUP BY
  neighborhood
ORDER BY
  offensive_pct DESC
LIMIT
  10

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  neighborhood,
  ROUND(100*COUNTIF(STRPOS(descriptor,
        "- Not_Offensive") > 0) / COUNT(*), 2) AS not_offensive_pct,
  ROUND(100*COUNTIF(STRPOS(descriptor,
        "- Offensive") > 0) / COUNT(*), 2) AS offensive_pct,
  COUNT(*) AS total_count
FROM
  `bigquery-public-data.san_francisco.311_service_requests`
WHERE
  STRPOS(category,
    "Graffiti") > 0
GROUP BY
  neighborhood
ORDER BY
  offensive_pct DESC
LIMIT
  10'

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

+------------------------+-------------------+---------------+-------------+
| neighborhood           | not_offensive_pct | offensive_pct | total_count |
+------------------------+-------------------+---------------+-------------+
| Presidio Terrace       | 16.49             | 83.51         | 467         |
| Presidio Heights       | 16.44             | 82.5          | 1794        |
| Presidio National Park | 18.31             | 81.69         | 71          |
| Golden Gate Park       | 18.71             | 81.21         | 3544        |
| Cole Valley            | 21.22             | 78.67         | 1772        |
| Ashbury Heights        | 20.79             | 78.17         | 481         |
| Lake Street            | 22.94             | 77.06         | 109         |
| Parnassus Heights      | 22.75             | 76.43         | 857         |
| Corona Heights         | 23.43             | 75.79         | 508         |
| Lone Mountain          | 26.62             | 73.15         | 3471        |
+------------------------+-------------------+---------------+-------------+

Twitter で最も多く寄せられている苦情とその地区は?

このクエリは、ソース列を使用して、Twitter によるリクエストをフィルタ処理し、neighborhoodcomplaint_type を基準にグループ分けします。

ウェブ UI

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

#standardSQL
SELECT
  neighborhood,
  complaint_type,
  COUNT(*) AS total_count
FROM
  `bigquery-public-data.san_francisco.311_service_requests`
WHERE
  Source="Twitter"
GROUP BY
  Neighborhood,
  complaint_type
ORDER BY
  total_count DESC
LIMIT
  5

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  neighborhood,
  complaint_type,
  COUNT(*) AS total_count
FROM
  `bigquery-public-data.san_francisco.311_service_requests`
WHERE
  Source="Twitter"
GROUP BY
  Neighborhood,
  complaint_type
ORDER BY
  total_count DESC
LIMIT
  5'

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

+------------------------+-------------------------------------------+-------------+
| neighborhood           | complaint_type                            | total_count |
+------------------------+-------------------------------------------+-------------+
| Mission                | Not_Offensive Graffiti on Public Property | 3117        |
| Mission                | Not_Offensive Graffiti on Public Property | 1428        |
| Inner Richmond         | Sidewalk_Defect                           | 1011        |
| Bayview                | Sidewalk_Cleaning                         | 989         |
| Mission                | Graffiti                                  | 574         |
+------------------------+-------------------------------------------+-------------+

サンフランシスコの Muni の停留所について最も多く寄せられる苦情は?

このクエリは、MUNI Feedbackcategory 列の値を使用して、サービス リクエストが最も多いバス停留所を検出します。

ウェブ UI

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

#standardSQL
SELECT
  descriptor,
  incident_address,
  COUNT(*) AS total_count
FROM
  `bigquery-public-data.san_francisco.311_service_requests`
WHERE
  category = "MUNI Feedback" AND incident_address != "Not associated with a specific address"
GROUP BY
  incident_address,
  descriptor
ORDER BY
  total_count DESC
LIMIT 10

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  descriptor,
  incident_address,
  COUNT(*) AS total_count
FROM
  `bigquery-public-data.san_francisco.311_service_requests`
WHERE
  category = "MUNI Feedback" AND incident_address != "Not associated with a specific address"
GROUP BY
  incident_address,
  descriptor
ORDER BY
  total_count DESC
LIMIT 10'

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

+-----------------------------------------+-------------------------------------------+-------------+
| descriptor                              | Incident_address                          | total_count |
+-----------------------------------------+-------------------------------------------+-------------+
| 201_Pass_Up_Did_Not_Wait_for_Transferee | Intersection of 24TH ST and MISSION ST    | 65          |
| 601_Delay_No_Show                       | Intersection of PINE ST and DAVIS ST      | 60          |
| 201_Pass_Up_Did_Not_Wait_for_Transferee | Intersection of 16TH ST and MISSION ST    | 59          |
| 601_Delay_No_Show                       | Intersection of DAVIS ST and PINE ST      | 56          |
| 601_Delay_No_Show                       | Intersection of BEALE ST and FOLSOM ST    | 55          |
| 201_Pass_Up_Did_Not_Wait_for_Transferee | Intersection of MARKET ST and 5TH ST      | 47          |
| 201_Pass_Up_Did_Not_Wait_for_Transferee | Intersection of 5TH ST and MARKET ST      | 45          |
| 301_Discourtesy_to_Customer             | Intersection of POWELL ST and MARKET ST   | 40          |
| 201_Pass_Up_Did_Not_Wait_for_Transferee | Intersection of 4TH ST and KING ST        | 37          |
| 201_Pass_Up_Did_Not_Wait_for_Transferee | Intersection of 4TH ST and TOWNSEND ST    | 37          |
+-----------------------------------------+-------------------------------------------+-------------+

データについて

データセット ソース: SF OpenData

カテゴリ: サンフランシスコ

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

BigQuery で表示: サンフランシスコ 311 サービス リクエスト データに移動

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

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

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