NYC Street Tree Census Data

The NYC street tree data includes data from the 1995, 2005 and 2015 Street Tree Censuses, which are conducted by volunteers organized by the NYC Department of Parks and Recreation. Trees were inventoried by address, and identified by tree species, diameter, and condition.

You can start exploring this data in the BigQuery console:

Go to NYC Tree Census Dataset

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 are the most common tree species in New York City?

This query looks at the most common species of trees alive on the sidewalks of New York City in 2015, and the percentage of healthy trees of each species.

Web UI

Open the following query in the Web UI

#standardsql
SELECT
  spc_latin,
  spc_common,
  COUNT(*) AS count,
  ROUND(COUNTIF(health="Good")/COUNT(*)*100) AS healthy_pct
FROM
  `bigquery-public-data.new_york.tree_census_2015`
WHERE
  status="Alive"
GROUP BY
  spc_latin,
  spc_common
ORDER BY
  count DESC

Command-line

bq query '
#standardsql
SELECT
  spc_latin,
  spc_common,
  COUNT(*) AS count,
  ROUND(COUNTIF(health="Good")/COUNT(*)*100) AS healthy_pct
FROM
  `bigquery-public-data.new_york.tree_census_2015`
WHERE
  status="Alive"
GROUP BY
  spc_latin,
  spc_common
ORDER BY
  count DESC
'

The results are shown here:

+------------------------------------+------------------------+-------+-------------+
|             spc_latin              |       spc_common       | count | healthy_pct |
+------------------------------------+------------------------+-------+-------------+
| Platanus x acerifolia              | London planetree       | 57415 |        85.0 |
| Gleditsia triacanthos var. inermis | honeylocust            | 42337 |        85.0 |
| Pyrus calleryana                   | Callery pear           | 37301 |        80.0 |
| Quercus palustris                  | pin oak                | 36404 |        86.0 |
| Acer platanoides                   | Norway maple           | 20900 |        65.0 |
| Tilia cordata                      | littleleaf linden      | 19800 |        82.0 |
| Prunus                             | cherry                 | 19783 |        85.0 |
| Zelkova serrata                    | Japanese zelkova       | 19364 |        86.0 |
| Ginkgo biloba                      | ginkgo                 | 13381 |        84.0 |
| Styphnolobium japonicum            | Sophora                | 12314 |        82.0 |
                                        ...
+------------------------------------+------------------------+-------+-------------+

How have tree species changed since 1995 in New York City?

This query examines the growth or decline in the number of each tree species from 1995 to 2015, as well as the number of new dead trees for each species.

Web UI

Open the following query in the Web UI

#standardsql
SELECT
  IFNULL(a.upper_latin, b.upper_latin) as upper_latin,
  IFNULL(count_2015, 0) as count_2015,
  IFNULL(count_1995, 0) as count_1995,
  (IFNULL(count_2015, 0)-IFNULL(count_1995, 0)) AS count_growth,
  (IFNULL(alive_2015, 0)-IFNULL(alive_1995, 0)) as alive_growth,
  (IFNULL(dead_2015, 0)-IFNULL(dead_1995, 0)) as dead_growth
FROM (
  SELECT
    UPPER(spc_latin) AS upper_latin,
    spc_common,
    COUNT(*) AS count_2015,
    COUNTIF(status="Alive") AS alive_2015,
    COUNTIF(status="Dead") AS dead_2015
  FROM
    `bigquery-public-data.new_york.tree_census_2015`
  WHERE spc_latin != ""
  GROUP BY
    spc_latin,
    spc_common)a
FULL OUTER JOIN (
  SELECT
    UPPER(spc_latin) AS upper_latin,
    COUNT(*) AS count_1995,
    COUNTIF(status!="Dead") AS alive_1995,
    COUNTIF(status="Dead") AS dead_1995
  FROM
    `bigquery-public-data.new_york.tree_census_1995`
  GROUP BY
    spc_latin)b
ON
  a.upper_latin=b.upper_latin
ORDER BY
  count_growth DESC

Command-line

bq query '
#standardsql
SELECT
  IFNULL(a.upper_latin, b.upper_latin) as upper_latin,
  IFNULL(count_2015, 0) as count_2015,
  IFNULL(count_1995, 0) as count_1995,
  (IFNULL(count_2015, 0)-IFNULL(count_1995, 0)) AS count_growth,
  (IFNULL(alive_2015, 0)-IFNULL(alive_1995, 0)) as alive_growth,
  (IFNULL(dead_2015, 0)-IFNULL(dead_1995, 0)) as dead_growth
FROM (
  SELECT
    UPPER(spc_latin) AS upper_latin,
    spc_common,
    COUNT(*) AS count_2015,
    COUNTIF(status="Alive") AS alive_2015,
    COUNTIF(status="Dead") AS dead_2015
  FROM
    `bigquery-public-data.new_york.tree_census_2015`
  WHERE spc_latin != ""
  GROUP BY
    spc_latin,
    spc_common)a
FULL OUTER JOIN (
  SELECT
    UPPER(spc_latin) AS upper_latin,
    COUNT(*) AS count_1995,
    COUNTIF(status!="Dead") AS alive_1995,
    COUNTIF(status="Dead") AS dead_1995
  FROM
    `bigquery-public-data.new_york.tree_census_1995`
  GROUP BY
    spc_latin)b
ON
  a.upper_latin=b.upper_latin
ORDER BY
  count_growth DESC
'

The results are shown here:

+------------------------------------+------------+------------+--------------+--------------+-------------+
|            upper_latin             | count_2015 | count_1995 | count_growth | alive_growth | dead_growth |
+------------------------------------+------------+------------+--------------+--------------+-------------+
| PLATANUS X ACERIFOLIA              |      58570 |          0 |        58570 |        57415 |         497 |
| GLEDITSIA TRIACANTHOS VAR. INERMIS |      43255 |          0 |        43255 |        42337 |         337 |
| PRUNUS                             |      20219 |          0 |        20219 |        19783 |         182 |
| ZELKOVA SERRATA                    |      19679 |       5740 |        13939 |        13718 |          33 |
| STYPHNOLOBIUM JAPONICUM            |      12612 |          0 |        12612 |        12314 |         118 |
| TILIA AMERICANA                    |       8841 |         95 |         8746 |         8534 |          87 |
| PYRUS CALLERYANA                   |      38306 |      31295 |         7011 |         6365 |          46 |
| TILIA TOMENTOSA                    |       5958 |         57 |         5901 |         5789 |          43 |
| ACER                               |       5729 |          0 |         5729 |         5658 |          32 |
| QUERCUS BICOLOR                    |       4631 |          2 |         4629 |         4535 |          40 |
                                                    ...
+------------------------------------+------------+------------+--------------+--------------+-------------+

How has the change in tree species distribution since 1995 affected the color of New York’s Fall foliage?

This query examines the effect of the change in the number of each tree species on the color of fall foliage by joining census data with tree species metadata.

Web UI

Open the following query in the Web UI

#standardsql
SELECT
  c.fall_color,
  SUM(d.count_growth) AS change
FROM (
  SELECT
    fall_color,
    UPPER(species_scientific_name) AS latin
  FROM
    `bigquery-public-data.new_york.tree_species`)c
JOIN (
  SELECT
    IFNULL(a.upper_latin,
      b.upper_latin) AS latin,
    (IFNULL(count_2015,
        0)-IFNULL(count_1995,
        0)) AS count_growth
  FROM (
    SELECT
      UPPER(spc_latin) AS upper_latin,
      spc_common,
      COUNT(*) AS count_2015
    FROM
      `bigquery-public-data.new_york.tree_census_2015`
    WHERE
      status="Alive"
    GROUP BY
      spc_latin,
      spc_common)a
  FULL OUTER JOIN (
    SELECT
      UPPER(spc_latin) AS upper_latin,
      COUNT(*) AS count_1995
    FROM
      `bigquery-public-data.new_york.tree_census_1995`
    WHERE
      status !="Dead"
    GROUP BY
      spc_latin)b
  ON
    a.upper_latin=b.upper_latin
  ORDER BY
    count_growth DESC)d
ON
  d.latin=c.latin
GROUP BY
  fall_color
ORDER BY
  change DESC

Command-line

bq query '
#standardsql
SELECT
  c.fall_color,
  SUM(d.count_growth) AS change
FROM (
  SELECT
    fall_color,
    UPPER(species_scientific_name) AS latin
  FROM
    `bigquery-public-data.new_york.tree_species`)c
JOIN (
  SELECT
    IFNULL(a.upper_latin,
      b.upper_latin) AS latin,
    (IFNULL(count_2015,
        0)-IFNULL(count_1995,
        0)) AS count_growth
  FROM (
    SELECT
      UPPER(spc_latin) AS upper_latin,
      spc_common,
      COUNT(*) AS count_2015
    FROM
      `bigquery-public-data.new_york.tree_census_2015`
    WHERE
      status="Alive"
    GROUP BY
      spc_latin,
      spc_common)a
  FULL OUTER JOIN (
    SELECT
      UPPER(spc_latin) AS upper_latin,
      COUNT(*) AS count_1995
    FROM
      `bigquery-public-data.new_york.tree_census_1995`
    WHERE
      status !="Dead"
    GROUP BY
      spc_latin)b
  ON
    a.upper_latin=b.upper_latin
  ORDER BY
    count_growth DESC)d
ON
  d.latin=c.latin
GROUP BY
  fall_color
ORDER BY
  change DESC
'

The results are shown here:

+---------------+--------+
|  fall_color   | change |
+---------------+--------+
| Yellow        | 124770 |
| Red/Bronze    |  13702 |
| Cream         |  12314 |
| Maroon        |   9796 |
| Orange/Brown  |   2694 |
| Yellow/Orange |   1565 |
| Purple/Maroon |    663 |
| Red           |  -6402 |
+---------------+--------+

About the data

Dataset Source: NYC Open Data

Category: New York City

Use: This dataset is publicly available for anyone to use under the following terms provided by the Dataset Source - https://data.cityofnewyork.us/ - 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: Historical

View in BigQuery: Go to NYC Tree Census Dataset

Send feedback about...