# Boston Crime Incident Reports, 2012 to present

## Facts

- Provider: City of Boston (Boston Police Department)
- License: Open Data Commons Public Domain Dedication and License (PDDL) 1.0
- Size: 13 files
- Records: 1,214,381 rows
- Stored at: data.boston.gov
- Access: public, readable without an account
- Category: Urban & Civic
- Source: https://data.boston.gov/dataset/crime-incident-reports-august-2015-to-date-source-new-system
- Page: https://scigantic.com/archive/523eb363-ad9a-5a33-b034-c79f2989932e
- Keywords: Urban & Civic

## About

Incident reports filed by the Boston Police Department, published as two separate CKAN datasets that together cover July 2012 to the present. The current series (August 2015 onward, sourced from the Mark43 records management system that went live in September 2019) holds 946,325 rows across nine yearly DataStore resources, with 2023 to present consolidated into one 289,421-row resource. The legacy series covers 8 July 2012 to 10 August 2015 with 268,056 rows from the Intergraph system. One row is one reported incident. The current schema carries an incident number, a four-digit offense code with its description and the FBI UCR part (One, Two, Three), the BPD district and reporting area, a shooting flag, the occurrence timestamp broken out into year, month, day of week and hour, the street name, and a latitude and longitude. The legacy schema is different in almost every column name and adds fields the new system dropped: nature code, weapon type, a domestic-incident flag and the shift. Records excluded under Massachusetts General Laws chapter 41 section 98F are withheld, and in the current 2023-to-present resource the OFFENSE_CODE_GROUP and UCR_PART columns are null in all 289,421 rows even though the columns still exist, so any breakdown by crime category has to key off OFFENSE_CODE or OFFENSE_DESCRIPTION rather than the grouping columns. Coordinates are present but 13,208 of those 289,421 rows carry a null or zero latitude, which silently drags any mapped centroid toward the Gulf of Guinea if not filtered. The legacy file stores its timestamp as MM/DD/YYYY HH:MI:SS AM text, so a lexical sort or a plain min/max returns a meaningless answer without a to_timestamp cast. The schema card documents the column mapping between the two systems, the null patterns, and the coordinate filter. Published by the Boston Police Department under PDDL 1.0.

## Load the data

```python
import io, json, urllib.error, urllib.parse, urllib.request
import pandas as pd

CKAN = "https://data.boston.gov/api/3/action"

def ckan(action, **params):
    """Any CKAN action endpoint. Plain urllib works; no auth, no headers."""
    url = f"{CKAN}/{action}?" + urllib.parse.urlencode(params)
    with urllib.request.urlopen(url, timeout=300) as r:
        return json.load(r)["result"]

def sql(query):
    """Server-side SQL over the DataStore. Warns when the 32,000-row cap clips."""
    res = ckan("datastore_search_sql", sql=query)
    if res.get("records_truncated"):
        print("WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET")
    return pd.DataFrame(res["records"])

R_CURRENT = "b973d8cb-eeb2-4e7e-99da-c92938efc9c0"   # 2023 to present, 289,421 rows

# Hour-of-day profile by district, geocoded rows only. Aggregated server-side.
profile = sql(f'''
    SELECT "DISTRICT", "HOUR"::int AS hour, count(*) AS incidents
    FROM "{R_CURRENT}"
    WHERE "DISTRICT" IS NOT NULL
      AND "Lat" IS NOT NULL AND "Lat"::float <> 0
    GROUP BY 1, 2
''')
pivot = profile.pivot(index="hour", columns="DISTRICT", values="incidents").fillna(0)
print(pivot.head(24))
print("\ntotal geocoded incidents:", int(profile["incidents"].sum()))
```

## Data structure

| Field | Type |
| --- | --- |
| `_id` | int |
| `INCIDENT_NUMBER` | text |
| `OFFENSE_CODE` | text |
| `OFFENSE_CODE_GROUP` | text |
| `OFFENSE_DESCRIPTION` | text |
| `DISTRICT` | text |
| `REPORTING_AREA` | text |
| `SHOOTING` | text |
| `OCCURRED_ON_DATE` | text |
| `YEAR` | text |
| `MONTH` | text |
| `DAY_OF_WEEK` | text |
| `HOUR` | text |
| `UCR_PART` | text |
| `STREET` | text |
| `Lat` | text |
| `Long` | text |
| `Location` | text |

Files:

```text
One row is one reported incident. Two CKAN packages, two record management systems, 1,214,381 rows total.
CURRENT SERIES (package crime-incident-reports-august-2015-to-date-source-new-system), 946,325 rows in 9 yearly resources plus two empty XLSX stubs. 2023 to present is consolidated into one 289,421-row resource; 2015 to 2022 are one resource per year. Sourced from the Mark43 RMS that went live September 2019 and backfilled to June 2015. 18 columns.
LEGACY SERIES (package crime-incident-reports-july-2012-august-2015-source-legacy-system), 268,056 rows in one file, verified range 2012-07-08 to 2015-08-10, from the Intergraph RMS. 21 columns, almost none of which share a name with the current schema.
COLUMN MAPPING between the two:
  current                   legacy
  INCIDENT_NUMBER           COMPNOS
  OFFENSE_DESCRIPTION       INCIDENT_TYPE_DESCRIPTION
  OFFENSE_CODE              MAIN_CRIMECODE (different code system, not comparable)
  DISTRICT                  REPTDISTRICT
  REPORTING_AREA            REPORTINGAREA
  OCCURRED_ON_DATE          FROMDATE
  SHOOTING                  Shooting ("0"/"1" vs "Yes"/"No")
  UCR_PART                  UCRPART ("Part One" in legacy; all-null in current)
  STREET / Lat / Long       STREETNAME / Y / X (legacy X,Y are State Plane, not lat/lon)
  (none)                    NatureCode, WEAPONTYPE, DOMESTIC, SHIFT, XSTREETNAME
The legacy-only columns are the interesting loss: weapon type, the domestic-incident flag and the shift were never carried into Mark43, so any analysis using them ends in August 2015.
PER-YEAR ROW COUNTS (current): 2015 53,597 | 2016 99,430 | 2017 101,338 | 2018 98,888 | 2019 87,184 | 2020 70,894 | 2021 71,721 | 2022 73,852 | 2023-present 289,421.
RESOURCES:
  Crime Incident Reports (August 2015 - To Date) (Source: New System) (data.boston.gov/dataset/crime-incident-reports-august-2015-to-date-source-new-system): 11 DataStore resources of 11 total
    b973d8cb-eeb2-4e7e-99da-c92938efc9c0     289421 rows  Crime Incident Reports - 2023 to Present
    313e56df-6d77-49d2-9c49-ee411f10cf58      73852 rows  Crime Incident Reports - 2022
    f4495ee9-c42c-4019-82c1-d067f07e45d2      71721 rows  Crime Incident Reports - 2021
    be047094-85fe-4104-a480-4fa3d03f9623      70894 rows  Crime Incident Reports - 2020
    34e0ae6b-8c94-4998-ae9e-1b51551fe9ba      87184 rows  Crime Incident Reports - 2019
    e86f8e38-a23c-4c1a-8455-c8f94210a8f1      98888 rows  Crime Incident Reports - 2018
    64ad0053-842c-459b-9833-ff53d568f2e3     101338 rows  Crime Incident Reports - 2017
    b6c4e2c3-7b1e-4f4a-b019-bef8c6a0e882      99430 rows  Crime Incident Reports - 2016
    792031bf-b9bb-467c-b118-fe795befdf00      53597 rows  Crime Incident Reports - 2015
    9c30453a-fefa-4fe0-b51a-5fc09b0f4655          0 rows  rmscrimeincidentfieldexplanation.xlsx
    3aeccf51-a231-4555-ba21-74572b4c33d6          0 rows  rmsoffensecodes.xlsx
...
```

Example file: `Top offense descriptions in the current file, with their share of geocoded rows:
  SELECT "OFFENSE_DESCRIPTION", count(*) n,
         sum(CASE WHEN "Lat"::float <> 0 THEN 1 ELSE 0 END) AS geocoded
  FROM "b973d8cb-eeb2-4e7e-99da-c92938efc9c0"
  GROUP BY 1 ORDER BY n DESC LIMIT 20`

## Open it

- In a hosted notebook with the data mounted: https://scigantic.com/archive/523eb363-ad9a-5a33-b034-c79f2989932e
- Full record as JSON (schema card included): https://api.scigantic.com/api/archives/523eb363-ad9a-5a33-b034-c79f2989932e
- Over MCP: `get_schema_card` with id `523eb363-ad9a-5a33-b034-c79f2989932e` at https://api.scigantic.com/mcp

## Related topics

- [Urban & Civic datasets](https://scigantic.com/topics/urban-and-civic)
