Transit App Trip Patterns

A metropolitan transit agency needed rider movement intelligence from a mobile trip-planning app: each record is a leg (origin, destination, route, mode, timestamps). The volume ruled out mapping every raw point in a public dashboard. I built the path from extract to Experience: load into SQL Server, clean and separate bad data, attach census block-group geography and demographics, aggregate to rolling 7-day OD flows and 31-day transfer hotspots (with a short data lag), then automate ArcGIS layer builds and portal overwrite so nobody re-publishes by hand. The portfolio demo below is a Leaflet stand-in with the same four pages — about, OD, transfers, adoption — on synthetic zones. Agency name withheld.

Problem

Trip-planning app data arrived as a huge raw feed; planners could not see where riders started, ended, or transferred without drowning in point noise.

Who

Service planning and operations.

Solution

Python loads into SQL, cleans and isolates bad legs, joins census block groups, aggregates to 7- and 31-day rollups, then an ArcGIS Pro publisher rebuilds hosted layers and overwrites ArcGIS Online on a Windows Task Scheduler job.

Outcome

Planners and operations could see origin–destination and transfer patterns across the network in an Experience Builder dashboard — without a dedicated enterprise GIS server.

Background

The trip-planning app produced trip-level legs at a scale that could not sit raw in a hosted feature layer for non-enterprise users. Planning still asked “where do riders go, and where do they transfer?” with one-off pulls. Census geography and demographic context lived in separate workflows.

Challenges

• Getting a massive raw extract into the reporting database reliably
• Cleaning duplicates, incomplete trips, and unrealistic legs
• Collapsing false same-route “transfers” and applying a 3-day lag
• Prepping block-group OD and transfer aggregates that a browser map could draw
• Publishing without a dedicated ArcGIS Enterprise GIS server
• Automating refresh so layers did not go stale between analyst runs

Stakeholders

• Service planning
• Operations

Success criteria

• 7-day OD and 31-day transfer products that refresh on a schedule
• Hosted AGOL layers overwritten safely (refuse empty overwrites)
• An Experience dashboard planners can open without GIS desktop
• Clear filters for transfer types (bus↔bus, bus↔on-demand)

Data sources

• Mobile trip-planning app leg extracts
• SQL Server reporting database
• Census block-group boundaries (and vintage crosswalks)
• Jobs / population layers for context
• APC monthly route ridership (adoption share table)

Stack

Design decisions

Why aggregate to 7 and 31 days? Full raw geometry was too large for hosted layers without enterprise GIS. Rolling windows keep the map readable and the overwrite job predictable.

Why publish from ArcGIS Pro instead of editing AGOL row-by-row? Feature-service overwrite from a built FGDB is faster and safer at this volume than thousands of REST edits. The publisher refuses empty overwrites so a bad run cannot wipe the portal.

Why Task Scheduler? The same machine that runs ArcGIS Pro can rebuild and push layers overnight. Planners always open yesterday’s window, not last month’s one-off publish.

Business metrics

OD trip volume

Count of cleaned legs between origin and destination block groups in the rolling 7-day window.

Transfer count

Journeys sharing a transfer stop and route pattern in the rolling 31-day window.

Wait / travel time

Average gap (wait) and in-vehicle time/distance along transfer chains.

App adoption share

Transit-app boardings as a share of APC monthly route ridership.

Governance

• Deduplicate inserts; filter incomplete and unrealistic legs
• Collapse false same-route transfers under a short gap threshold
• 3-day portal lag so late files do not redraw mid-window
• Refuse AGOL overwrite when the local feature class has zero rows
• This site uses synthetic zones — not production block groups or live counts

What the model was telling us

Plain-language readout for analysts. Snippets below are the shape of the fit, not the agency notebook.

Aggregation is the product. The insight was never “draw every leg.” It was OD pairs and transfer hotspots at a grain a browser map and a planner can use.

Clean before you map. Duplicates, broken timestamps, and fake same-route transfers will invent corridors that do not exist. QA is spatial accuracy.

Publish is part of the pipeline. If the last step is a person clicking Share in Pro, the dashboard goes stale. Task Scheduler + overwrite made the Experience a living system.

Ingestion

• Python extract of trip-planning app legs into SQL Server
• Incremental / rolling windows with overlap days to catch late arrivals
• Census block-group boundaries and demographic joins

Transformations

• Clean and isolate bad data; standardize route and stop fields
• Build origin–destination pairs at block-group level (7-day)
• Build transfer chains and hotspot points (31-day)
• Materialize ArcGIS-ready tables for flows, transfers, walk egress

Automation

• Windows Task Scheduler runs the job on a fixed cadence
• ArcGISPro.py builds FGDB feature classes and overwrites AGOL hosted layers
• Experience Builder reads the hosted services (no manual layer rebuild)

legs = read_sql("dbo.TransitApp_Leg_Trips", window)
od = aggregate_od(legs, days=7, lag_days=3)
transfers = aggregate_transfers(legs, days=31, lag_days=3)
if count_rows(od_fc) == 0:
    raise RuntimeError("Refuse AGOL overwrite: empty OD layer")
overwrite_agol(od_fc, "TransitApp7Day")

select trip_date, origin_bg, dest_bg, route_no,
       count(*) as trips,
       avg(travel_min) as avg_min
from transitapp.LegTrips_Clean
where trip_date >= dateadd(day, -10, cast(getdate() as date))
group by trip_date, origin_bg, dest_bg, route_no;

• ArcGIS Experience: About, 7-day OD, 31-day transfers, adoption
• Hosted OD line / polygon layers and transfer hotspot points
• Monthly route app-share table vs APC
• Leaflet demo below (synthetic network, same page structure)

Production is ArcGIS Online + Experience Builder. This page cannot host the live org layers, so the interactive below uses Leaflet, twelve synthetic zones, and scaled counts. Filters mimic the Experience: origin zone on OD, transfer type on transfers.

Operational outcomes

• Planners could see where riders were going to and from within the network
• Operations could spot transfer hubs and bus↔on-demand handoffs
• No dedicated enterprise GIS server required for the end-user experience

Adoption

• Service planning
• Operations

Lessons learned

• Raw trip firehoses do not belong in a dashboard — rollups do.
• Automated publish (and empty-overwrite guards) matter as much as the SQL.
• A short data lag buys trust: better a stable window than a twitchy map.