San Francisco Street Trees Data

This data includes a list of San Francisco Department of Public Works maintained street trees including: planting date, species, and location. Data includes 1955 to present.

You can start exploring this data in the BigQuery console:

Go to SF Street Trees Data

Sample queries

Here are some examples of SQL queries you can run on this data in BigQuery.

These samples use BigQuery’s support for standard SQL. Use the #standardSQL tag to let BigQuery know you want to use standard SQL. For more information about the #standardSQL prefix, see Setting a query prefix.

What is the average tree diameter?

This query uses the dbh column. DBH stands for diameter at breast height, which is the diameter (in inches) of a tree at 4.5 feet above the ground.

Web UI

Open the following query in the Web UI

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

Command-line

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 != ""'

The results are shown here:

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

What is the highest number of a particular species of tree planted in a single year?

This query finds the tree species with the highest count for any single year since 1955.

Web UI

Open the following query in the Web 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

Command-line

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'

The results are shown here:

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

Which San Francisco locations feature the largest number of trees?

This query returns the geo coordinates for the largest number of trees.

Web UI

Open the following query in the Web 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

Command-line

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'

The results are shown here:

+--------------------+----------------------+-------------+
| 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          |
+--------------------+----------------------+-------------+

About the data

Dataset Source: SF OpenData

Category: San Francisco

Use: This dataset is publicly available for anyone to use under the following terms provided by the Dataset Source - http://sfgov.org/ - and is provided "AS IS" without any warranty, express or implied, from Google. Google disclaims all liability for any damages, direct or indirect, resulting from the use of the dataset.

Update Frequency: Quarterly

View in BigQuery: Go to SF Street Trees data

Send feedback about...