A city service manager wants to compare where their one-week sewer-request cohort generated a larger review queue. Raw counts mostly repeat where more people report. A percentage is more comparable, but a neighborhood with a single report can show 100% by chance. Produce a map for areas with enough reports to support a first-pass conversation.
The tables in the PostGIS sandbox are nyc_sewer_requests and nyc_ntas, both SRID 4326; their columns are below. Use the supplied simplified 2020 Neighborhood Tabulation Area polygons; ntatype='0' denotes an ordinary neighborhood area.
Spatially assign each geocoded request to the polygon that covers it, including a point exactly on its boundary. Calculate elapsed closure days from closed_at - created_at, and count a review when elapsed time is strictly greater than one day. Return one polygon row per ntatype='0' NTA with at least three assigned requests. Required columns: nta2020, ntaname, integer request_count, integer review_count, review_pct = round(100 * review_count / request_count, 1), and NTA polygon geom. Order by review_pct descending and nta2020 for ties.
This is an exploratory share within a single closed, geocoded intake cohort, not a population rate or a formal service-standard violation. Reporters and complaint categories are unevenly distributed; the three-request cutoff is a small-denominator safeguard, not evidence of statistical significance. The NTA polygons were simplified for screening and should not be used for legal or block-level attribution. Run shows the SQL output and graduated map without grading; Submit grades it.
nyc_ntas — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
nta2020 | text | 2020 NTA identifier |
ntaname | text | NTA name |
boroname | text | Borough name |
ntatype | text | 0 for ordinary neighborhood area; other codes for nonresidential areas |
geom | geometry(Geometry, 4326) | MultiPolygon |
nyc_sewer_requests — SRID 4326, GiST index on geom.
| column | type | meaning |
|---|---|---|
id | integer | row number the loader adds as the primary key; the file carries no id of its own |
request_id | text | 311 service request identifier |
created_at | text | Local NYC civil time when the request was created |
closed_at | text | Local NYC civil time when the agency closed the request |
descriptor | text | Complaint subtype |
borough | text | Reported borough |
incident_zip | text | Reported postal code |
geom | geometry(Geometry, 4326) | Point |
These are the input columns your query can read. The Data tab also shows field meanings and sample records.
nyc_ntasCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| nta2020 | text |
| ntaname | text |
| boroname | text |
| ntatype | text |
| geom | geometry(Geometry,4326) |
nyc_sewer_requestsCurrent sandbox schema| Column | Type |
|---|---|
| id | integer |
| request_id | text |
| created_at | text |
| closed_at | text |
| descriptor | text |
| borough | text |
| incident_zip | text |
| geom | geometry(Geometry,4326) |
These are where the teaching is. Read them twice.
The NYC Department of City Planning publishes 2020 Neighborhood Tabulation Areas (https://data.cityofnewyork.us/City-Government/2020-Neighborhood-Tabulation-Areas-NTAs-/9nt8-h7nd). Spatial join and aggregation follow the PostGIS workshop's practical exercises (https://postgis.net/workshops/postgis-intro/joins_exercises.html).
8 scored, 0 informational
Neighborhood review-share result fields
13%Result is neighborhood polygons
7%Assigns requests by location
7%Neighborhoods meeting the sample rule
20%Correct neighborhood set
20%Requests represented on the map
13%Review requests represented on the map
13%Spatial lookup uses a geometry index
7%