Query U.S. city crime data in Snowflake

1. Download the typed sample

Download cities.parquet. Parquet preserves the seven-character geographic IDs as text. The dataset page has CSV, column definitions, checksums and the sample license.

2. Create the table in your database

Open a SQL worksheet in Snowsight, select a database where your role can create objects, and choose a warehouse. Run this once to create the schema and table. If the table already exists, inspect it before loading another copy.

CREATE SCHEMA IF NOT EXISTS CRIMEMETRIC;
CREATE TABLE CRIMEMETRIC.CITY_SUMMARY (
  PLACE_GEOID VARCHAR(7), PLACE_NAME VARCHAR,
  STATE VARCHAR(2), PLACE_TYPE VARCHAR, RESIDENTS NUMBER(12,0),
  MAJOR_CRIME_INDEX FLOAT, PERSONS_CRIME_INDEX FLOAT,
  PROPERTY_CRIME_INDEX FLOAT, DISORDER_INDEX FLOAT,
  CONFIDENCE_HIGH_SHARE FLOAT, CONFIDENCE_MEDIUM_SHARE FLOAT,
  CONFIDENCE_LOW_SHARE FLOAT, MODEL_RELEASE VARCHAR, SOURCE_URL VARCHAR
);

3. Load the Parquet file

In Snowsight, open Ingestion, choose to load a local file, and select cities.parquet. Choose your database and the existing CRIMEMETRIC.CITY_SUMMARY table. Review the column mapping, then load. The initial file has 7,895 rows.

The sample has no provider data fee. Snowflake may charge your account for storage and warehouse usage. Suspend your warehouse when you finish.

4. Check coverage, compare and join

Keep the database selected in your worksheet. These examples use the schema and table above. The first query checks the release; the second compares California places of similar size; the third joins on geographic IDs with leading zeros intact.

-- Choose the database created when you get the listing, then run these examples.
-- 1. Check the release and coverage. Expected initial release: 7,895 places, 51 state abbreviations.
SELECT MODEL_RELEASE, COUNT(*) AS PLACES, COUNT(DISTINCT STATE) AS STATES
FROM CRIMEMETRIC.CITY_SUMMARY GROUP BY MODEL_RELEASE;

-- 2. Compare places of similar population, retaining confidence information.
SELECT PLACE_GEOID, PLACE_NAME, STATE, RESIDENTS, MAJOR_CRIME_INDEX,
       CONFIDENCE_HIGH_SHARE, CONFIDENCE_MEDIUM_SHARE, CONFIDENCE_LOW_SHARE
FROM CRIMEMETRIC.CITY_SUMMARY
WHERE STATE = 'CA' AND RESIDENTS BETWEEN 50000 AND 100000
ORDER BY MAJOR_CRIME_INDEX, PLACE_GEOID;

-- 3. Join by GEOID, not a city-name spelling. Keep leading zeros.
WITH MY_PLACES AS (SELECT COLUMN1::VARCHAR AS PLACE_GEOID
                   FROM VALUES ('0618100'), ('0644000'))
SELECT C.* FROM MY_PLACES P
JOIN CRIMEMETRIC.CITY_SUMMARY C ON C.PLACE_GEOID = P.PLACE_GEOID;

Interpret the result

An index of 100 is the modeled U.S. rate per resident. An index of 150 means 1.5 times that rate; it does not mean 150 incidents or a 50% chance of being a victim. Major crime combines persons and property crimes. Social disorder is a separate index.

Confidence shares describe the residents living in block groups at each confidence level. City averages do not describe every neighborhood, and places with many visitors can have high per-resident indexes. The sample does not include every U.S. place.

For a geographic join, both sides must use the same seven-character Census place GEOID. ZIP codes, county FIPS codes and twelve-character block-group GEOIDs represent different geographies and should not be joined directly to this key.