By calculating the combined standard deviation of the latitude and longitude of each type of crime, we can find which crimes are the most heavily concentrated in a particular area — highlighting hotspots for specific crimes across the city.

SELECT

descript,

STDDEV(latitude) as sdLat,

STDDEV(longitude) as sdLong,

count(*) as count

FROM

`bigquery-public-data.san_francisco.sfpd_incidents`

WHERE

(Latitude IS NOT NULL) AND (Longitude IS NOT NULL)

AND (Latitude > 30) AND (LATITUDE < 40)

AND (Longitude > -130) AND (Longitude < -120)

GROUP BY

descript

HAVING

sdlat is not null and sdlong is not null

AND count > 500

ORDER BY

sdlat+sdLong ASC

The results with the lowest standard deviation represent crimes with the highest location-concentration; the top 10 are shown in the table below. Note that drugs and prostitution make up half the top 10.

SFPD criminal incident descriptions with over 500 occurrences, and the lowest location standard deviation.

In total, 75% of the drug-related crimes in the categories from the top 10 table above occurred in this part of San Francisco shown in the map below:

Map showing 75% of SFPD incidents for drug-related crimes in categories within the top 10 most concentrated crime types. The Tenderloin is outlined in red.

Prostitution is also tightly concentrated, but in this case most police incidents occur in two separate, but distinct, areas:

Heat-map of prostitution-related crimes in categories within the top 10 most concentrated crime types.

Crimes with the highest location standard deviation — or greatest spread — are shown in the table below, with burglary, theft, and found property constituting 7 of the top 10.

SFPD criminal incidents with over 500 occurrences and the highest location standard deviation.

These crimes tend to occur all over the city; as you can see in the map below that shows locations with at least one burglary or attempted break-in:

All locations with one or more burglary / attempted break-in across San Francisco.

Looking at the most common burglary-related incident type, residential break-ins, 74% of incidents occurred in a location with only one break-in. The map below shows only locations with 5 or more burglary break-ins:

Locations with five or more burglary break-in across San Francisco.

BigQuery includes many more public datasets for San Francisco, as well as other cities including New York and Chicago. What can you discover about the cities we live in?