MSD Dashboard Metric: general population public transit gtfs coverage

[evansiroky]

Question

What % of California (and Californians) has (open to the public) transit coverage in GTFS?

Metrics

By area:

• The % of non-water area of Californian that is within 1/4 mi of a bus stop or 1 mi of a ferry/rail stop that has is served by a public-funded, open to the general public transit service with GTFS Schedule data

By Population:

• The % of Californians that are within 1/4 mi of a bus stop or 1 mi of a ferry/rail stop that stop that has is served by a public-funded, open to the general public transit service with GTFS Schedule data

By Employment (optional):

• The % of Jobs that are within 1/4 mi of a bus stop or 1 mi of a ferry/rail stop that stop that has is served by a public-funded, open to the general public transit service with GTFS Schedule data

Data sources

• Census data
• Airtable database listing transit services, eligibilities, and GTFS data
• stops.txt for GTFS datasets and shapes.txt for continuous stops and locations.geojson for flexible services.

(Data Servicess Team to Copy and Fill Out Below)

The QuVR MD template below will be filled out by a member of the data services team. This allows us to describe the request, in a way that is easy to hand-off for analysis. After the research phase, we will sync with the asker to figure out if the metric and dashboard pieces are needed.

Before starting research:

• Question: Question written as a single sentence.
• View: E.g. views.gtfs_schedule_fact_daily_feed_files.
• Research:
  • How should the results be presented?
  • When are they needed by?

After reviewing research with the asker:

• Metric: what specific calculations are needed?
• Dashboard: where should we put the result?

[evansiroky]

@edasmalchi to make a first pass at creating a notebook in the https://github.com/cal-itp/data-analyses/ repo to attempt to answer this question.

[edasmalchi]

May be blocked on these since I either can't find the right Census data at geometries smaller than tract or they do not provide it.

The relevant table seems to be B18105 from the 2019 ACS 5-year, but it doesn’t seem like I can get a geometry smaller than tract from either data.census.gov or using their API…

Rephrasing the metric along the lines of "the % of able-bodied Californians in a census tract containing a bus stop or a ferry/rail stop that has a provider with both static and realtime GTFS data" would work if tract-level data really is the best we can get. Also still pending realtime.

[machow]

AFAICT there are 3 ways to tell if a provider has GTFS realtime data, and wonder which would work here (ordered from least to most effort)?:

1. a provider is listed in the airtable as having GTFS RT
2. We have downloaded their GTFS RT data (e.g. within a certain time period)
3. We have downloaded their GTFS RT data and confirmed it contains data for that stop's trips

I wonder if at the very least (1) would get us off the ground on reporting out this metric (on the RT side of things)..!

[edasmalchi]

AFAICT there are 3 ways to tell if a provider has GTFS realtime data, and wonder which would work here (ordered from least to most effort)?:

(1) Seems like enough for a first pass here. I haven't actually used airtable before -- is there a guide someplace or a way to get access?

[evansiroky]

After taking a closer look at the data schema, I don't think we're blocked on realtime after all. We should just be able to fetch from gtfs_schedule_history.calitp_feeds and check if all 3 of the fields gtfs_rt_vehicle_positions_url, gtfs_rt_service_alerts_url and gtfs_rt_trip_updates_url are not null to determine if a feed has realtime.