San Francisco 311 Service Requests Data

This data includes all San Francisco 311 service requests from July 2008 to the present, and is updated daily. 311 is a non-emergency number that provides access to non-emergency municipal services.

You can start exploring this data in the BigQuery console:

Go to SF 311 Service Requests 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.

Which neighborhoods have the highest proportion of offensive graffiti?

This query uses the complaint_type column which differentiates between offensive and non offensive graffiti, and calculates them as a percentage of total complaints for graffiti.

Web UI

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

Command-line

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'

Sample results are shown here:

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

Which complaint is most likely to be made using Twitter and in which neighborhood?

This query uses the source column to filter out requests made via Twitter, grouped by neighborhood and complaint_type.

Web UI

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

Command-line

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'

Sample results are shown here:

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

What are the most complained about Muni stops in San Francisco?

This query uses the category column value of MUNI Feedback to find the bus stop with the most service requests.

Web UI

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

Command-line

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'

Sample results are shown here:

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

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: Daily

View in BigQuery: Go to SF 311 Service Requests data

Send feedback about...

BigQuery Documentation