Equitable Bike Access Using EtherData H3 Census

-- Equitable bike access
WITH
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 AS (
 SELECT h3
 FROM manhattan m,
 UNNEST(bqcarto.h3.ST_ASH3_POLYFILL(m.geom, 8)) AS h3
),


-- Trips aggregated to H3 R8 (origin = start station)
trips_by_h3 AS (
 SELECT
   bqcarto.h3.LONGLAT_ASH3(t.start_station_longitude, t.start_station_latitude, 8) AS h3,
   COUNT(1) AS trip_count
 FROM `bigquery-public-data.new_york_citibike.citibike_trips` t
 WHERE DATE(t.starttime) BETWEEN DATE '2018-04-10' AND DATE '2018-09-10'
   AND t.start_station_latitude  BETWEEN 40.68 AND 40.88
   AND t.start_station_longitude BETWEEN -74.05 AND -73.90
   AND t.start_station_latitude  IS NOT NULL
   AND t.start_station_longitude IS NOT NULL
 GROUP BY 1
),
trips_manhattan AS (
 SELECT t.*
 FROM trips_by_h3 t
 JOIN manhattan_h3 mh USING (h3)
),


-- Stations + docks aggregated to H3 R8 (supply proxy)
stations_by_h3 AS (
 SELECT
   bqcarto.h3.LONGLAT_ASH3(s.longitude, s.latitude, 8) AS h3,
   COUNT(*) AS station_count,
   SUM(COALESCE(s.capacity, 0)) AS total_docks
 FROM `bigquery-public-data.new_york_citibike.citibike_stations` s
 WHERE s.latitude  BETWEEN 40.68 AND 40.88
   AND s.longitude BETWEEN -74.05 AND -73.90
   AND s.latitude IS NOT NULL
   AND s.longitude IS NOT NULL
   AND COALESCE(s.is_installed, TRUE) = TRUE
 GROUP BY 1
),
stations_manhattan AS (
 SELECT sb.*
 FROM stations_by_h3 sb
 JOIN manhattan_h3 mh USING (h3)
),


-- Etherdata canonical census (H3 R8) —
census_manhattan AS (
 SELECT
   c.h3,
   c.total_pop,
   c.households,
   c.median_income
 FROM `v3layer-pro.census_tract_h3r8.nydemo` c
 JOIN manhattan_h3 mh USING (h3)
 WHERE c.state_fips = '36'
   AND c.h3 IS NOT NULL
),


-- Roll up to one row per H3 cell (weighted income)
census_rollup AS (
 SELECT
   h3,
   SUM(total_pop) AS total_pop,
   SUM(households) AS households,
   SAFE_DIVIDE(SUM(median_income * total_pop), NULLIF(SUM(total_pop), 0)) AS median_income
 FROM census_manhattan
 GROUP BY 1
),


base AS (
 SELECT
   c.h3,
   bqcarto.h3.ST_BOUNDARY(c.h3) AS geom,
   ST_Y(ST_CENTROID(bqcarto.h3.ST_BOUNDARY(c.h3))) AS lat,
   ST_X(ST_CENTROID(bqcarto.h3.ST_BOUNDARY(c.h3))) AS lon,


   -- Census
   c.total_pop,
   c.households,
   c.median_income,


   -- Demand + supply
   COALESCE(t.trip_count, 0) AS trip_count,
   COALESCE(s.station_count, 0) AS station_count,
   COALESCE(s.total_docks, 0) AS total_docks,


   -- Normalized operational rates
   SAFE_DIVIDE(COALESCE(t.trip_count, 0), NULLIF(c.households, 0)) AS trips_per_household,
   SAFE_DIVIDE(COALESCE(t.trip_count, 0), NULLIF(COALESCE(s.total_docks, 0), 0)) AS trips_per_dock,


   SAFE_DIVIDE(COALESCE(s.total_docks, 0), NULLIF(c.households, 0)) * 1000 AS docks_per_1k_households
 FROM census_rollup c
 LEFT JOIN trips_manhattan t USING (h3)
 LEFT JOIN stations_manhattan s USING (h3)
 WHERE c.total_pop > 0
),


scored AS (
 SELECT
   *,


   -- Relative ranks within the Manhattan H3 domain
   PERCENT_RANK() OVER (ORDER BY median_income) AS pct_income,
   PERCENT_RANK() OVER (ORDER BY docks_per_1k_households) AS pct_access,
   PERCENT_RANK() OVER (ORDER BY trips_per_household) AS pct_demand,


   -- Low-income exposure: higher = lower income (relative)
   (1 - PERCENT_RANK() OVER (ORDER BY median_income)) AS low_income_exposure_score,


   -- Equity gap: higher = more low-income exposure with relatively lower access
   ((1 - PERCENT_RANK() OVER (ORDER BY median_income)) - PERCENT_RANK() OVER (ORDER BY docks_per_1k_households)) AS equity_gap_access,


   -- Optional: low-income exposure vs realized demand (usage)
   ((1 - PERCENT_RANK() OVER (ORDER BY median_income)) - PERCENT_RANK() OVER (ORDER BY trips_per_household)) AS equity_gap_demand,


   CASE
     WHEN households < 50 THEN 'LOW_EXPOSURE_HH' -- guardrail: very low residential exposure
     WHEN (1 - PERCENT_RANK() OVER (ORDER BY median_income)) >= 0.70
          AND PERCENT_RANK() OVER (ORDER BY docks_per_1k_households) <= 0.30 THEN 'LOW_INCOME_LOW_ACCESS'
     WHEN (1 - PERCENT_RANK() OVER (ORDER BY median_income)) >= 0.70
          AND PERCENT_RANK() OVER (ORDER BY docks_per_1k_households) >= 0.70 THEN 'LOW_INCOME_HIGH_ACCESS'
     WHEN (1 - PERCENT_RANK() OVER (ORDER BY median_income)) <= 0.30
          AND PERCENT_RANK() OVER (ORDER BY docks_per_1k_households) <= 0.30 THEN 'HIGH_INCOME_LOW_ACCESS'
     ELSE 'MIXED'
   END AS equity_segment
 FROM base
)


SELECT
 *
FROM scored
WHERE h3 not in ("882a107295fffff","882a100f21fffff")
ORDER BY equity_gap_access DESC;
                    

Results & Interpretation (What the charts show)

Key takeaways (grounded in the charts)

• Exposure is structured and stable: the low-income exposure map provides a usable equity baseline.
• Access is uneven and not clearly aligned to exposure: scatter indicates mixed alignment rather than a clean “more exposure → more access” pattern.
• Demand is also weakly aligned: usage does not systematically rise with exposure; constraints and destination effects likely dominate.
• Outliers exist and must be validated: extreme docks-per-1K and trips-per-household values are often denominator-sensitive; use the table to confirm.
• Actionability comes from the gap + table: rank high-exposure, low-access cells with sufficient households to prioritize planning follow-up.

What we can conclude (and what we cannot)