San Francisco Street Trees Data

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

データセットの概要

このデータには、サンフランシスコ公共事業局が整備した街路樹の情報(植栽日、種類、場所の情報)が含まれています。データには、1955 年から現在までのデータが含まれます。

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

SF Street Trees Data に移動

サンプルクエリ

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

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

樹木の平均直径は?

このクエリでは、dbh 列を使用します。DBH は胸の高さでの直径を表します。つまり、地上から 4.5 フィートの高さでの樹木の直径(インチ)です。

ウェブ UI

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

#standardSQL
SELECT
  ROUND(AVG(CAST(dbh as FLOAT64)), 2) as avg_width
FROM
  `bigquery-public-data.san_francisco.street_trees`
WHERE dbh != ""

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  ROUND(AVG(CAST(dbh as FLOAT64)), 2) as avg_width
FROM
  `bigquery-public-data.san_francisco.street_trees`
WHERE dbh != ""'

結果を以下に示します。

+---------------+
| avg_width     |
+---------------+
| 13.31         |
+---------------+

1 年間に最も多く植えられた樹木の種類と本数は?

このクエリは、1955 年以降、1 年間に最も植樹回数が多い樹木の種類を見つけます。

ウェブ UI

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

#standardSQL
SELECT
  EXTRACT(YEAR from plant_date) as plantdate,
  species,
  COUNT(*) as count
FROM
  `bigquery-public-data.san_francisco.street_trees`
WHERE
  plant_date IS NOT null AND
  species != "Tree(s) ::"
GROUP BY
  plantdate, species
ORDER BY
  count desc
LIMIT 1

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  EXTRACT(YEAR from plant_date) as plantdate,
  species,
  COUNT(*) as count
FROM
  `bigquery-public-data.san_francisco.street_trees`
WHERE
  plant_date IS NOT null AND
  species != "Tree(s) ::"
GROUP BY
  plantdate, species
ORDER BY
  count desc
LIMIT 1'

結果を以下に示します。

+----------------+--------------------------------------------+-------+
| plantdate      | species                                    | count |
+----------------+--------------------------------------------+-------+
| 2008           | Arbutus 'Marina' :: Hybrid Strawberry Tree | 489   |
+----------------+--------------------------------------------+-------+

サンフランシスコで最も樹木の数が多い場所は?

このクエリは、樹木の数が最も多い場所の経緯度を返します。

ウェブ UI

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

#standardSQL
SELECT
  latitude,
  longitude,
  COUNT(*) AS count
FROM
  `bigquery-public-data.san_francisco.street_trees`
WHERE latitude IS NOT null AND longitude IS NOT null
GROUP BY
  latitude, longitude
ORDER BY
  count DESC
LIMIT
  10

コマンドライン

bq query --use_legacy_sql=false '
SELECT
  latitude,
  longitude,
  COUNT(*) AS count
FROM
  `bigquery-public-data.san_francisco.street_trees`
WHERE latitude IS NOT null AND longitude IS NOT null
GROUP BY
  latitude, longitude
ORDER BY
  count DESC
LIMIT
  10'

結果を以下に示します。

+--------------------+----------------------+-------------+
| latitude           | longitude            | count       |
+--------------------+----------------------+-------------+
| 37.7170922039834   | -122.472661630304    | 59          |
| 37.7933347653256   | -122.394213120763    | 50          |
| 37.7806072039402   | -122.403939371598    | 48          |
| 37.7478662384021   | -122.46234443277     | 47          |
| 47.2699873738681   | -138.2836696503      | 45          |
| 37.774806598562    | -122.424087136382    | 43          |
| 37.7293262181041   | -122.49338600271     | 43          |
| 37.7976528582722   | -122.395985750709    | 40          |
| 37.7703595033467   | -122.409820383511    | 40          |
| 37.7325250651963   | -122.380424102302    | 39          |
+--------------------+----------------------+-------------+

データについて

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

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

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

更新頻度: 四半期ごと

BigQuery で表示: SF Street Trees Data に移動

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

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

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