{"success":true,"data":{"author":"City of Boston (Boston 311)","bucketAccessType":"public","bucketListable":false,"bucketName":"data.boston.gov","category":"Urban & Civic","cloudProvider":"api","createdAt":"2026-08-15T00:00:00.000Z","description":"Every constituent service request handled by BOS:311, the City of Boston's non-emergency request channel, from 2011 to the present: 3,398,808 rows across 17 DataStore-loaded CSV resources on data.boston.gov. One row is one case. Each case carries the request type and subject, the responsible department and queue, open and close timestamps, an SLA target and an on-time or overdue flag, the closure reason as free text, the reporting channel (phone, the Citizens Connect app, web, Twitter), a street address with latitude and longitude, and a full set of administrative geographies: neighborhood, ward, precinct, city council district, police district, fire district and public works district. Photos submitted by residents and taken at closure are linked as Cloudinary URLs. The volume is remarkably stable at roughly 250,000 to 280,000 cases per year since 2016, which makes the series usable for year-over-year comparison of service delivery, response time by request type and neighborhood, and equity analysis of who gets served how fast. The structural fact that governs every query is a backend migration: starting October 2025, 311 began moving to a new case management system, and cases from the new system land in a separate resource with an entirely different column vocabulary (case_id, open_date, case_topic, service_name, assigned_department) from the legacy one (case_enquiry_id, open_dt, type, reason, subject, queue). The two overlap in time rather than succeeding one another, so the 2025 legacy file and the NEW SYSTEM file both contain August through December 2025 and concatenating them double counts. The city expects the transition to complete in mid-2026. The schema card carries the full column mapping between the two schemas, the per-year row counts, and the coordinate and administrative-code quirks (all columns are text, ward is formatted 'Ward 1' in the legacy schema and '06' in the new one, and geom_4326 is PostGIS WKB hex rather than a coordinate pair). Published by Boston 311 under PDDL 1.0.","displayTitle":"Boston 311 Service Requests, 2011 to present","dockerImageUrl":"531775725781.dkr.ecr.us-east-1.amazonaws.com/scigantic-notebook-minimal:latest","id":"a4eb6ef7-ce3a-515c-9db9-2863a7a439b9","license":"Open Data Commons Public Domain Dedication and License (PDDL) 1.0","referenceUrl":"https://data.boston.gov/dataset/311-service-requests","region":"us-east-1","schemaCard":{"accessHints":[{"snippet":"import io, json, urllib.error, urllib.parse, urllib.request\nimport pandas as pd\n\nCKAN = \"https://data.boston.gov/api/3/action\"\n\ndef ckan(action, **params):\n    \"\"\"Any CKAN action endpoint. Plain urllib works; no auth, no headers.\"\"\"\n    url = f\"{CKAN}/{action}?\" + urllib.parse.urlencode(params)\n    with urllib.request.urlopen(url, timeout=300) as r:\n        return json.load(r)[\"result\"]\n\ndef sql(query):\n    \"\"\"Server-side SQL over the DataStore. Warns when the 32,000-row cap clips.\"\"\"\n    res = ckan(\"datastore_search_sql\", sql=query)\n    if res.get(\"records_truncated\"):\n        print(\"WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET\")\n    return pd.DataFrame(res[\"records\"])\n","language":"python"},{"snippet":"# Bulk CSV download. pd.read_csv(url) on this portal raises 403: the CKAN link\n# 302s to a presigned S3 URL that the redirect fails on. Resolve it by hand and\n# drop the explicit :443, which is the single change that makes it a 200.\nclass _NoRedirect(urllib.request.HTTPRedirectHandler):\n    def redirect_request(self, *a, **k):\n        return None\n\ndef boston_csv(url, **read_csv_kwargs):\n    try:\n        urllib.request.build_opener(_NoRedirect).open(url, timeout=120)\n        signed = url\n    except urllib.error.HTTPError as e:\n        if e.code not in (301, 302, 303, 307, 308):\n            raise\n        signed = e.headers[\"Location\"].replace(\"s3.amazonaws.com:443\", \"s3.amazonaws.com\")\n    with urllib.request.urlopen(signed, timeout=900) as r:\n        return pd.read_csv(io.BytesIO(r.read()), **read_csv_kwargs)\n","language":"python"},{"language":"python","snippet":"# Resolve a resource id by package name rather than hardcoding it, and page\n# past the 32,000-row cap. Verified against a 282,836-row resource.\ndef resources(package_name):\n    pkg = ckan(\"package_show\", id=package_name)\n    return [\n        {\"id\": r[\"id\"], \"name\": r.get(\"name\"), \"file\": r[\"url\"].rsplit(\"/\", 1)[-1],\n         \"format\": r.get(\"format\"), \"live\": bool(r.get(\"datastore_active\"))}\n        for r in pkg[\"resources\"]\n    ]\n\ndef read_all(resource_id, page=32000, **filters):\n    \"\"\"Every row of a DataStore resource, in _id order.\"\"\"\n    out, offset = [], 0\n    while True:\n        res = ckan(\"datastore_search\", resource_id=resource_id,\n                   limit=page, offset=offset, **filters)\n        rows = res[\"records\"]\n        out.extend(rows)\n        if len(rows) < page:\n            return pd.DataFrame(out)\n        offset += page\n"}],"totalObjects":17,"sampleRows":[{"location_zipcode":"02128","_id":1,"closure_reason":"Case Closed. Closed date : Wed May 15 06:58:54 EDT 2024 Noted Ticket was issued by code enforcement officer  ","source":"Citizens Connect App","__resourceId":"dff4d804-5031-443a-8409-8344efd0e5c8","case_status":"Closed","subject":"Public Works Department","closed_photo":"https://spot-boston-res.cloudinary.com/image/upload/v1715770728/boston/production/y9ebhnrb0akmpgsys1fr.jpg#spot=61ed3cb1-768e-4d70-ada2-ad295a04943d","location_street_name":"160-162 Liverpool St","__schema":"legacy (2011 to 2026 yearly files)","location":"160-162 Liverpool St  East Boston  MA  02128","police_district":"A7","type":"Improper Storage of Trash (Barrels)","pwd_district":"09","open_dt":"2024-05-14 13:33:28","case_enquiry_id":"101005463183","latitude":"42.37387949518689","sla_target_dt":"2024-05-17 04:30:00","on_time":"ONTIME","geom_4326":"0101000020E610000067D9DA168CC251C0813C8648DB2F4540","department":"PWDx","submitted_photo":null,"neighborhood":"East Boston","longitude":"-71.03980037090297","city_council_district":"1","neighborhood_services_district":"1","fire_district":"1","closed_dt":"2024-05-15 02:58:54","case_title":"Improper Storage of Trash (Barrels)","ward":"Ward 1","queue":"PWDx_Code Enforcement","reason":"Code Enforcement","precinct":"0104"},{"case_status":"In progress","case_topic":"Pruning Request","open_date":"2026-06-23 10:37:18.432093+00","_id":1,"assigned_department":"Parks & Recreation","longitude":"-71.03407942","full_address":"723 E Second St, Boston, MA 02127","on_time":"OVERDUE","precinct":"06","fire_district":"6","__resourceId":"254adca6-64ab-4c5c-9fc0-a6da622be185","street_number":"723","street_name":"E Second St","closure_comments":null,"ward":"06","police_district":"C6","city_council_district":"2","assigned_team":"Urban Forestry","submitted_photo":null,"public_works_district":"5","latitude":"42.3371968","report_source":"BOS311","closure_reason":null,"close_date":null,"case_id":"BCS-00232230","neighborhood":"South Boston","__schema":"new system (Oct 2025 onward)","zip_code":"02127","closed_photo":null,"target_close_date":"2026-06-26 10:37:18.432093+00","service_name":"Pruning Request"}],"format":"v2-hybrid","sampleObjects":["254adca6-64ab-4c5c-9fc0-a6da622be185  311 Service Requests - NEW SYSTEM","1a0b420d-99f1-4887-9851-990b2a5a6e17  311 Service Requests - 2026","9d7c2214-4709-478a-a2e8-fb2020a5bb94  311 Service Requests - 2025","dff4d804-5031-443a-8409-8344efd0e5c8  311 Service Requests - 2024","e6013a93-1321-4f2a-bf91-8d8a02f1e62f  311 Service Requests - 2023","81a7b022-f8fc-4da5-80e4-b160058ca207  311 SERVICE REQUESTS - 2022","f53ebccd-bc61-49f9-83db-625f209c95f5  311 SERVICE REQUESTS - 2021","6ff6a6fd-3141-4440-a880-6f60a37fe789  311 SERVICE REQUESTS - 2020","ea2e4696-4a2d-429c-9807-d02eb92e0222  311 SERVICE REQUESTS - 2019","2be28d90-3a90-4af1-a3f6-f28c1e25880a  311 SERVICE REQUESTS - 2018","30022137-709d-465e-baae-ca155b51927d  311 SERVICE REQUESTS - 2017","b7ea6b1b-3ca4-4c5b-9713-6dc1db52379a  311 SERVICE REQUESTS - 2016","c9509ab4-6f6d-4b97-979a-0cf2a10c922b  311 SERVICE REQUESTS - 2015","bdae89c8-d4ce-40e9-a6e1-a5203953a2e0  311 SERVICE REQUESTS - 2014","407c5cd0-f764-4a41-adf8-054ff535049e  311 SERVICE REQUESTS - 2013","382e10d9-1864-40ba-bef6-4eea3c75463c  311 SERVICE REQUESTS - 2012","94b499d9-712a-4d2a-b790-7ceec5c9c4b1  311 SERVICE REQUESTS - 2011"],"columns":[{"name":"_id","type":"int"},{"name":"case_enquiry_id","type":"text"},{"name":"open_dt","type":"text"},{"name":"sla_target_dt","type":"text"},{"name":"closed_dt","type":"text"},{"type":"text","name":"on_time"},{"type":"text","name":"case_status"},{"type":"text","name":"closure_reason"},{"name":"case_title","type":"text"},{"type":"text","name":"subject"},{"type":"text","name":"reason"},{"type":"text","name":"type"},{"type":"text","name":"queue"},{"name":"department","type":"text"},{"type":"text","name":"submitted_photo"},{"name":"closed_photo","type":"text"},{"type":"text","name":"location"},{"name":"fire_district","type":"text"},{"type":"text","name":"pwd_district"},{"name":"city_council_district","type":"text"},{"name":"police_district","type":"text"},{"type":"text","name":"neighborhood"},{"name":"neighborhood_services_district","type":"text"},{"type":"text","name":"ward"},{"name":"precinct","type":"text"},{"type":"text","name":"location_street_name"},{"type":"text","name":"location_zipcode"},{"type":"text","name":"latitude"},{"type":"text","name":"longitude"},{"name":"geom_4326","type":"text"},{"name":"source","type":"text"}],"caveats":["DO NOT look for a FUSE mount. This archive has cloudProvider \"api\": nothing is mounted, ~/archive and /mnt/archive do not exist, and there are no files on disk to walk. The generic archive preamble you may have been given says otherwise; it is wrong for this row. All data is read over HTTPS from the CKAN API at https://data.boston.gov/api/3/action. The notebook has outbound internet; no credentials, tokens or headers are required for any endpoint below.","DO NOT trust a result set of exactly 32,000 rows. Both datastore_search and datastore_search_sql cap a single response at 32,000 records no matter what LIMIT you write, so a query over a million-row resource returns a full-looking frame that is silently a fragment. datastore_search_sql sets records_truncated: true in the result when it clips; check it on every call. The fixes: aggregate server-side (GROUP BY in SQL returns few enough rows to fit), or page with ORDER BY _id plus OFFSET, which is verified to work past 280,000.","DO NOT do arithmetic or date comparison on a column without casting it. Almost every DataStore column on this portal is typed text regardless of what it holds, so avg(\"latitude\") returns HTTP 409 Conflict rather than a number, and min/max on a date column sorts lexically and returns a confident wrong answer. Cast explicitly in SQL: \"latitude\"::float, \"open_dt\"::timestamp, date_part('year', \"open_dt\"::timestamp). Where a column IS genuinely typed numeric or timestamp it is marked as such in the columns list below, and those are safe to use directly.","SQL AGGREGATES COME BACK AS STRINGS, so arithmetic on the resulting DataFrame silently fails or, worse, concatenates. PostgreSQL `numeric` results are serialized as JSON strings by the CKAN API (sum(\"OVERTIME\") returns '153220443.25', not 153220443.25) while `int`/`bigint` and `double precision` come back as JSON numbers. So count(*) is an int, avg() and sum() over a numeric column are strings, and dividing one such column by another raises \"unsupported operand type(s) for /: 'str' and 'str'\". Run pd.to_numeric on every aggregate column before doing arithmetic on it. Do NOT blanket-coerce every column: ward \"06\", precinct \"0104\" and ZIP \"02128\" are zero-padded identifiers that must stay strings.","A 403 FROM datastore_search_sql MEANS THE SQL TEXT TRIPPED AN EDGE FILTER, NOT THAT YOUR QUERY IS WRONG. Some perfectly valid PostgreSQL is rejected with HTTP 403 Forbidden before it ever reaches the database, so there is no SQL error message to read and retrying the identical query never helps. Verified blocked, deterministically, 5 of 5 attempts each (2026-08-15): agg(...) FILTER (WHERE ...) · extract(<part> FROM ...) · substring(x FROM a FOR b) · trim(BOTH ... FROM ...) · NULLIF(...) · a literal constant comparison such as 1=2. Verified working substitutes for each: sum(CASE WHEN cond THEN 1 ELSE 0 END) for FILTER · date_part('year', ts) and date_part('epoch', ts_a - ts_b) for extract · left(col, n) for substring · CASE WHEN d = 0 THEN NULL ELSE d END for NULLIF. Subqueries, CTEs, LEFT JOIN, IN (...), HAVING, window functions, percentile_cont WITHIN GROUP, the regex operator ~, and casts are all fine. When a query 403s, rewrite it; do not retry it.","DO NOT call pd.read_csv() on a data.boston.gov download URL. It raises HTTP 403. The CKAN download link 302-redirects to a presigned S3 URL on the bucket og-production-open-data-bostonma-892364687672, and the request fails when it is followed from urllib's redirect path, which is what pandas uses. Verified 2026-08-15: pd.read_csv(url) -> 403, while requests.get(url) on the identical URL -> 200. Two fixes, both verified: read through requests (pd.read_csv(io.BytesIO(requests.get(url).content))), or resolve the redirect yourself and strip the explicit \":443\" from the returned Location before fetching it. The bucket itself is private (anonymous ListObjectsV2 and GetObject both return AccessDenied), so there is no S3 path around this: the presigned link is the only way in. See accessHints for a copy-paste helper. None of this applies to the /api/3/action endpoints, which are plain JSON over HTTPS and work with bare urllib.","Resource ids, not names, are the addressing key, and the names are unreliable: CKAN's resource `name` field is null on many Boston resources, and several packages publish generated filenames (tmp4myyj_u8.csv) that carry no year. The ids listed in this card were captured on 2026-08-15. The package NAME is the stable handle, so resolve ids at runtime with GET /api/3/action/package_show?id=<package-name> and match on the resource `name` or `url` rather than hardcoding an index into the resource array, whose order is not guaranteed.","_id is CKAN's synthetic 1-based row number added by the DataStore loader, not a key from the source data. It is the right column to ORDER BY for stable paging and it is meaningless as a join key or an entity id.","DO NOT concatenate the 2025 resource with the NEW SYSTEM resource. They overlap from 2025-08-01 to 2025-12-31. Verified date ranges: legacy 2025 covers 2025-01-01T00:26:17 to 2025-12-31T22:01:00; NEW SYSTEM covers 2025-08-01T06:35:06 to 2026-08-14T09:11:33. There is no shared key to dedupe on either, because case_enquiry_id and case_id come from different systems and use different formats. For a clean series through 2024, use the legacy yearly files only and state the cutoff.","The ward column is formatted differently in each schema: \"Ward 1\" in the legacy files, \"06\" (zero-padded, no prefix) in the new system. precinct is likewise \"0104\" (ward+precinct concatenated) in legacy and \"06\" (precinct alone) in the new system. Joining either to the ward boundary layer without normalising produces an empty join, not an error.","geom_4326 is PostGIS WKB in hex (\"0101000020E610000067D9...\"), not a coordinate pair and not WKT. Use the separate latitude and longitude columns; they hold the same point in plain text.","closure_reason is operator-typed free text that often embeds a second copy of the close timestamp in a different format (\"Case Closed. Closed date : Wed May 15 06:58:54 EDT 2024 ...\"). It is not a controlled vocabulary and cannot be grouped on directly.","on_time is a string flag (\"ONTIME\" / \"OVERDUE\") computed by the city against sla_target_dt, not a boolean. An open case can be OVERDUE with a null closed_dt, so filtering to closed cases before measuring on-time rate changes the answer; decide which question you are asking.","submitted_photo and closed_photo are Cloudinary URLs on an external host, not files in this archive. They are live third-party links and are not guaranteed to persist."],"layout":{"example":"Median days-to-close by request type in 2024, computed server-side so nothing is truncated. Note date_part('epoch', a - b) rather than extract(epoch FROM a - b): the extract form is rejected with a 403 by this portal.\n  SELECT \"type\", count(*) n,\n         percentile_cont(0.5) WITHIN GROUP (ORDER BY\n           date_part('epoch', \"closed_dt\"::timestamp - \"open_dt\"::timestamp) / 86400) AS median_days\n  FROM \"dff4d804-5031-443a-8409-8344efd0e5c8\"\n  WHERE \"closed_dt\" IS NOT NULL AND \"closed_dt\" <> ''\n  GROUP BY 1 ORDER BY n DESC LIMIT 20","pattern":"One row is one 311 case. 3,398,808 rows across 17 DataStore resources, one per year from 2011 to 2026 plus a separate NEW SYSTEM resource.\nTHE ONE THING THAT MATTERS: there are TWO INCOMPATIBLE SCHEMAS and they OVERLAP IN TIME. Boston began migrating 311 to a new case management backend in October 2025. Cases from the new system land in their own resource with a different column vocabulary. The legacy 2025 resource covers 2025-01-01 to 2025-12-31 (267,187 rows) and the NEW SYSTEM resource covers 2025-08-01 to 2026-08-14 (38,469 rows), so August through December 2025 exists in BOTH. Concatenating every resource in the package double counts that window. The city expects the transition to finish in mid-2026.\nCOLUMN MAPPING between the two, for the fields you actually need:\n  legacy                    new system\n  case_enquiry_id           case_id            (numeric string vs \"BCS-00232230\")\n  open_dt                   open_date\n  closed_dt                 close_date\n  sla_target_dt             target_close_date\n  case_title / type         service_name\n  reason / subject          case_topic\n  department / queue        assigned_department / assigned_team\n  source                    report_source\n  location                  full_address (+ street_number, street_name)\n  location_zipcode          zip_code\n  pwd_district              public_works_district\n  (no equivalent)           closure_comments\n  geom_4326                 (dropped)\n  neighborhood_services_district (dropped)\nShared, and identical in both: on_time, case_status, closure_reason, neighborhood, ward, precinct, city_council_district, police_district, fire_district, latitude, longitude, submitted_photo, closed_photo.\nPER-YEAR ROW COUNTS (legacy): 2011 58,262 | 2012 118,092 | 2013 142,610 | 2014 147,820 | 2015 210,083 | 2016 216,535 | 2017 251,269 | 2018 262,748 | 2019 259,022 | 2020 251,222 | 2021 273,784 | 2022 276,599 | 2023 266,438 | 2024 282,836 | 2025 267,187 | 2026 75,832 (partial). NEW SYSTEM: 38,469.\nRESOURCES:\n  311 Service Requests (data.boston.gov/dataset/311-service-requests): 17 DataStore resources of 21 total\n    254adca6-64ab-4c5c-9fc0-a6da622be185      38469 rows  311 Service Requests - NEW SYSTEM\n    1a0b420d-99f1-4887-9851-990b2a5a6e17      75832 rows  311 Service Requests - 2026\n    9d7c2214-4709-478a-a2e8-fb2020a5bb94     267187 rows  311 Service Requests - 2025\n    dff4d804-5031-443a-8409-8344efd0e5c8     282836 rows  311 Service Requests - 2024\n    e6013a93-1321-4f2a-bf91-8d8a02f1e62f     266438 rows  311 Service Requests - 2023\n    81a7b022-f8fc-4da5-80e4-b160058ca207     276599 rows  311 SERVICE REQUESTS - 2022\n    f53ebccd-bc61-49f9-83db-625f209c95f5     273784 rows  311 SERVICE REQUESTS - 2021\n    6ff6a6fd-3141-4440-a880-6f60a37fe789     251222 rows  311 SERVICE REQUESTS - 2020\n    ea2e4696-4a2d-429c-9807-d02eb92e0222     259022 rows  311 SERVICE REQUESTS - 2019\n    2be28d90-3a90-4af1-a3f6-f28c1e25880a     262748 rows  311 SERVICE REQUESTS - 2018\n    30022137-709d-465e-baae-ca155b51927d     251269 rows  311 SERVICE REQUESTS - 2017\n    b7ea6b1b-3ca4-4c5b-9713-6dc1db52379a     216535 rows  311 SERVICE REQUESTS - 2016\n    c9509ab4-6f6d-4b97-979a-0cf2a10c922b     210083 rows  311 SERVICE REQUESTS - 2015\n    bdae89c8-d4ce-40e9-a6e1-a5203953a2e0     147820 rows  311 SERVICE REQUESTS - 2014\n    407c5cd0-f764-4a41-adf8-054ff535049e     142610 rows  311 SERVICE REQUESTS - 2013\n    382e10d9-1864-40ba-bef6-4eea3c75463c     118092 rows  311 SERVICE REQUESTS - 2012\n    94b499d9-712a-4d2a-b790-7ceec5c9c4b1      58262 rows  311 SERVICE REQUESTS - 2011"},"generatedAt":"2026-08-16T17:39:25.112Z","starterCell":"import io, json, urllib.error, urllib.parse, urllib.request\nimport pandas as pd\n\nCKAN = \"https://data.boston.gov/api/3/action\"\n\ndef ckan(action, **params):\n    \"\"\"Any CKAN action endpoint. Plain urllib works; no auth, no headers.\"\"\"\n    url = f\"{CKAN}/{action}?\" + urllib.parse.urlencode(params)\n    with urllib.request.urlopen(url, timeout=300) as r:\n        return json.load(r)[\"result\"]\n\ndef sql(query):\n    \"\"\"Server-side SQL over the DataStore. Warns when the 32,000-row cap clips.\"\"\"\n    res = ckan(\"datastore_search_sql\", sql=query)\n    if res.get(\"records_truncated\"):\n        print(\"WARNING: clipped at the 32,000-row cap; aggregate or page with OFFSET\")\n    return pd.DataFrame(res[\"records\"])\n\nR_2024 = \"dff4d804-5031-443a-8409-8344efd0e5c8\"   # 311 Service Requests - 2024, 282,836 rows\n\n# GROUP BY server-side: the whole year in one small response, no 32k truncation.\n# Two rewrites this portal forces, both because the natural form returns 403:\n#   extract(epoch FROM a - b)   ->  date_part('epoch', a - b)\n#   avg(x) FILTER (WHERE cond)  ->  avg(CASE WHEN cond THEN x END)\nby_type = sql(f'''\n    SELECT \"reason\", \"type\", count(*) AS cases,\n           avg(CASE WHEN \"on_time\" = \\'ONTIME\\' THEN 1.0 ELSE 0.0 END) AS on_time_rate,\n           avg(CASE WHEN \"closed_dt\" IS NOT NULL AND \"closed_dt\" <> \\'\\'\n                    THEN date_part(\\'epoch\\', \"closed_dt\"::timestamp - \"open_dt\"::timestamp)\n                         / 86400.0 END) AS mean_days_to_close\n    FROM \"{R_2024}\"\n    GROUP BY 1, 2\n    ORDER BY cases DESC\n    LIMIT 25\n''')\nprint(by_type.to_string(index=False))\n","rowCount":3398808},"summary":"Every constituent service request handled by BOS:311, the City of Boston's non-emergency request channel, from 2011 to the present: 3,398,808 rows across 17 DataStore-loaded CSV…","title":"Boston 311 Service Requests, 2011 to present","updatedAt":"2026-08-16T17:40:35.920Z","visibility":"public","websiteUrl":"https://www.boston.gov/departments/bos311","whiteLabel":"boston"}}