311 Health & Sanitation Intelligence (Manhattan Demo) - Powered by Etherdata's Canonical US Census at H3 Resolution 8

Conclusion

This Manhattan demo is intentionally simple - and that is the point. The lift is not making a map. The lift is establishing a canonical backbone that makes operational data comparable, explainable, and fair.

With Etherdata's canonical census table, NYC 311 becomes a reusable health and sanitation intelligence layer: it supports hotspot detection, operational triage, and equity-aware prioritization in a single framework, and it generalizes immediately to any other dataset that can be indexed to H3.

-- NYC 311 Health & Sanitation monitoring at H3 R8, filtered to Manhattan,
-- joined to Etherdata canonical census, and enriched with geometry helpers for plotting.


WITH
-- Manhattan boundary (New York County, FIPS 061 within NY state 36)
manhattan AS (
 SELECT county_geom AS geom
 FROM `bigquery-public-data.geo_us_boundaries.counties`
 WHERE state_fips_code = '36'
   AND RIGHT(county_fips_code, 3) = '061'
 LIMIT 1
),


--  Manhattan H3 set at R8 via polyfill
manhattan_h3 AS (
 SELECT h3
 FROM manhattan m,
 UNNEST(bqcarto.h3.ST_ASH3_POLYFILL(m.geom, 8)) AS h3
),


-- Filter and spatialize 311 requests into H3 R8
sr AS (
 SELECT
   unique_key,
   created_date,
   closed_date,
   agency,
   agency_name,
   complaint_type,
   descriptor,
   location_type,
   address_type,
   city,
   borough,
   incident_zip,
   latitude,
   longitude,


   -- H3 at resolution 8 (CARTO toolbox)
   bqcarto.h3.LONGLAT_ASH3(longitude, latitude, 8) AS h3,


   -- Operational metric: time to close (hours) when closed_date is present
   SAFE_DIVIDE(TIMESTAMP_DIFF(closed_date, created_date, SECOND), 3600.0) AS hours_to_close
 FROM `bigquery-public-data.new_york_311.311_service_requests`
 WHERE created_date >= TIMESTAMP('2021-01-01')
   AND created_date <  TIMESTAMP('2022-01-01')
   AND latitude  IS NOT NULL
   AND longitude IS NOT NULL
),


--  Keep only records whose H3 falls in Manhattan polyfill
sr_manhattan AS (
 SELECT s.*
 FROM sr s
 INNER JOIN manhattan_h3 mh
   ON s.h3 = mh.h3
),


--  Define a transparent "health & sanitation" subset
sr_labeled AS (
 SELECT
   *,
   CASE
     WHEN LOWER(complaint_type) IN (
       'rodent',
       'food poisoning',
       'food establishment',
       'unsanitary condition',
       'dirty conditions',
       'standing water',
       'water system',
       'sewer',
       'air quality',
       'hazardous materials',
       'mold'
     )
     THEN 1 ELSE 0
   END AS is_health_sanitation
 FROM sr_manhattan
),


--  Aggregate 311 metrics per H3 cell
sr_agg AS (
 SELECT
   h3,


   -- Volumes
   COUNT(1) AS requests_total,
   SUM(is_health_sanitation) AS requests_health_sanitation,
   SAFE_DIVIDE(SUM(is_health_sanitation), COUNT(1)) AS health_sanitation_share,


   -- Service performance (avg time-to-close in hours)
   AVG(hours_to_close) AS avg_hours_to_close_all,
   AVG(IF(is_health_sanitation = 1, hours_to_close, NULL)) AS avg_hours_to_close_health,


   -- Closure counts (helps filter unstable averages)
   COUNTIF(closed_date IS NOT NULL) AS requests_closed_total,
   COUNTIF(is_health_sanitation = 1 AND closed_date IS NOT NULL) AS requests_closed_health
 FROM sr_labeled
 WHERE h3 IS NOT NULL
 GROUP BY h3
),


-- 4) Canonical census denominators & context (already H3-keyed)
census AS (
 SELECT
   h3,
   total_pop,
   households,
   housing_units,
   median_income,
   median_rent,
   percent_income_spent_on_rent,
   housing_built_1939_or_earlier,
   no_car
 FROM `v3layer-pro.census_tract_h3r8.nydemo`
),


-- 5) Final layer: rates + composite index + geometry helpers
final AS (
 SELECT
   a.h3,


   -- Geometry helpers for plotting
   bqcarto.h3.ST_BOUNDARY(a.h3) AS geom,
   CONCAT(
     ST_Y(ST_CENTROID(bqcarto.h3.ST_BOUNDARY(a.h3))),
     ",",
     ST_X(ST_CENTROID(bqcarto.h3.ST_BOUNDARY(a.h3)))
   ) AS latlon,


   -- 311 volumes
   a.requests_total,
   a.requests_health_sanitation,
   a.health_sanitation_share,


   -- Service performance
   a.avg_hours_to_close_all,
   a.avg_hours_to_close_health,
   a.requests_closed_total,
   a.requests_closed_health,


   -- Census context
   c.total_pop,
   c.households,
   c.housing_units,
   c.median_income,
   c.median_rent,
   c.percent_income_spent_on_rent,
   c.housing_built_1939_or_earlier,
   c.no_car,


   -- Exposure-normalized rates (per 1,000)
   SAFE_MULTIPLY(1000.0, SAFE_DIVIDE(a.requests_health_sanitation, NULLIF(c.total_pop, 0))) AS health_requests_per_1k_pop,
   SAFE_MULTIPLY(1000.0, SAFE_DIVIDE(a.requests_health_sanitation, NULLIF(c.households, 0))) AS health_requests_per_1k_hh,


   -- Composite prioritization index (ranking/monitoring; not causal)
   (
     SAFE_DIVIDE(
       (
         SAFE_MULTIPLY(1000.0, SAFE_DIVIDE(a.requests_health_sanitation, NULLIF(c.total_pop, 0)))
         - AVG(SAFE_MULTIPLY(1000.0, SAFE_DIVIDE(a.requests_health_sanitation, NULLIF(c.total_pop, 0)))) OVER()
       ),
       NULLIF(
         STDDEV_POP(SAFE_MULTIPLY(1000.0, SAFE_DIVIDE(a.requests_health_sanitation, NULLIF(c.total_pop, 0)))) OVER(),
         0
       )
     )
     +
     SAFE_DIVIDE(
       (c.percent_income_spent_on_rent - AVG(c.percent_income_spent_on_rent) OVER()),
       NULLIF(STDDEV_POP(c.percent_income_spent_on_rent) OVER(), 0)
     )
     +
     SAFE_DIVIDE(
       (a.avg_hours_to_close_health - AVG(a.avg_hours_to_close_health) OVER()),
       NULLIF(STDDEV_POP(a.avg_hours_to_close_health) OVER(), 0)
     )
   ) / 3.0 AS health_sanitation_risk_index

 FROM sr_agg a
 LEFT JOIN census c
   ON a.h3 = c.h3
)

SELECT * FROM final;