Make your static bus timetables sing and move along with Arcade - Part 1

GeeFernando · ‎02-28-2020

Static bus timetables (GTFS) are no match for real time bus information (GTFS-realtime). But that doesn't mean you have to settle for static visualisation techniques.

In this blog I will show how you can be a little creative and make your static timetables sing and move along with Arcade. And you'll be left wanting to sing: the wheels on the bus go round and round

Select a link below to view the web map associated with this GIF.

The GIF and web map show popups of upcoming buses and those just recently missed. This would come in handy for bus commuters to understand which services are available at any given date and time. We’ll learn how to create these popup displays from a GTFS feed. Followed by how to extend this solution to a mobile friendly app, where commuters could access this information on the go

More on GTFS

A GTFS feed is a collection of text files, each telling a story about transit information. For this example we'll use the GTFS feed for public buses in Canberra, Australia.

Click here to download the GTFS feed for Canberra.
Click here to view the GTFS specification for Canberra.

Data prep

Before you get started, click here to download an ArcGIS Pro project package with my model builder configurations, used to create these layers.

First, convert the stops.txt file into a hosted feature layer by using the model builder configuration or GTFS stops to features geoprocessing tool. As the name suggests, this is in relation to the location of bus stops.
Then, run the following model to join stop_times.txt to trips.txt to routes.txt to calendar.txt files, and create a hosted table in ArcGIS Online. This is in relation to bus arrival times, route details, accessibility information and date ranges/weekdays when buses are operational.

Build expressions (hosted table)

You may have noticed from the model used for the hosted table, I created an extra field arrivalSeconds to calculate the arrival_time of buses from hh:mm:ss string type to a number (seconds elapsed since start of day). This was done to accelerate the filter query (which we'll use later) to only return features relevant to the time of day. As you can imagine, it's a lot faster to query numbers compared to converting string types on the fly (especially with large datasets).

Convert arrival_time to number of seconds elapsed since start of day

//separate arrival hours, minutes and seconds into an array
var separateArrival = Split($feature["arrival_time"],":")

//To fix hour figures greater than 23 
var fixedHours = When(separateArrival[0] >= 24, separateArrival[0] - 24, separateArrival[0])

//convert arrival_time from 'String' to 'Date' type
var arrivalTimeAware = Date(Year(Now()), Month(Now()), Day(Now()), fixedHours, separateArrival[1], separateArrival[2])

var startOfDayTime = Date(Year(Now()), Month(Now()), Day(Now()), 0, 0, 0)

//arrival_time to number of seconds elapsed since start of day 
var arrivalSeconds = DateDiff(arrivalTimeAware, startOfDayTime, 'seconds')
return arrivalSeconds

Build expressions (web map)

The following expressions assume that you're already familiar with Arcade FeatureSets, and FeatureSetByPortalItem() released in Arcade 1.8. If you'd like to familiarise yourself with Featuresets refer to this excellent blog written by Paul Barker.

After you've added the bus stops feature layer to a web map, build the following expression to return information relevant to the upcoming buses in the next 60 minutes (3,600 seconds).

Here is a web map already built for your convenience .

Expression for upcoming buses in the next 60 minutes

//query features from hosted table - 'stopsTimesTripsRoutesCalendar' in ArcGIS Online
var portal = Portal("https://www.arcgis.com")
var gtfsTable = FeatureSetByPortalItem(portal,"38ee59b7d5bc4173ab8786bd58ae274a", 0)

var stopId = $feature["stop_id"]

var startOfDayTime = Date(Year(Now()), Month(Now()), Day(Now()), 0, 0, 0)
//seconds elapsed since start of day
var secondsElapsed = DateDiff(Now(), startOfDayTime, 'seconds')

//return curr_date as an integer in the format 'YYYYMMDD' #HowItShouldBe
var curr_date = Number(Text(Now(),'YMMDD'))

//filter statement (SQL) - access variables with @
//if you'd like to change the time interval, change '3600' seconds to something else in statement - 'arrivalSeconds <= (@secondsElapsed + 3600)'
var filterStatement = 'arrivalSeconds >= @secondsElapsed AND arrivalSeconds <= (@secondsElapsed + 3600) AND stop_id = @stopId AND @curr_date >= start_date AND @curr_date <= end_date'

//filter the gtfsTable by the filter statement
//order results by arrivalSeconds in ascending order
var filtered = OrderBy(Filter(gtfsTable, filterStatement),'arrivalSeconds ASC')

var popupResult = ''

var weekDays = ['sunday','monday','tuesday','wednesday','thursday','friday','saturday']
//This variable is used within the for loop to determine whether a bus is operational on a given weekDay (e.g. returns 1 if operational, 0 if not).
var currWeekdayOpr = weekDays[Weekday(Now())]

//Iterate over the filtered results to show desried fields in popup
for (var f in filtered){
    if (f[currWeekdayOpr] == 1){
        popupResult += Left(f.arrival_time, 5) + " - " + f.route_long_name +
        When(f.route_type == 712, Concatenate(" (",f.trip_headsign,")"), Concatenate(" (",f.route_short_name,")")) +
        When(f.wheelchair_accessible == 1 && f.bikes_allowed == 1, " - ",
        f.wheelchair_accessible == 1 && f.bikes_allowed == 0," - ",
        f.wheelchair_accessible == 0 && f.bikes_allowed == 1," - ","") + TextFormatting.NewLine + TextFormatting.NewLine
    } else{
        popupResult += ""
    }
}

IIF(IsEmpty(popupResult), "No upcoming buses in the next hour" + TextFormatting.NewLine + TextFormatting.NewLine, popupResult)

You can also use a similar expression with a slightly modified filterStatement to query buses departed in the last x minutes. The statement below queries buses departed in the last 20 minutes (1,200 seconds).

var filterStatement = 'arrivalSeconds >= (@secondsElapsed - 1200) AND arrivalSeconds < @secondsElapsed AND stop_id = @stopId AND @curr_date >= start_date AND @curr_date <= end_date'

Complete expression for buses departed in the last 20 minutes

//query features from hosted table - 'stopsTimesTripsRoutesCalendar' in ArcGIS Online
var portal = Portal("https://www.arcgis.com")
var gtfsTable = FeatureSetByPortalItem(portal,"38ee59b7d5bc4173ab8786bd58ae274a", 0)

var stopId = $feature["stop_id"]

var startOfDayTime = Date(Year(Now()), Month(Now()), Day(Now()), 0, 0, 0)
//seconds elapsed since start of day
var secondsElapsed = DateDiff(Now(), startOfDayTime, 'seconds')

//return curr_date as an integer in the format 'YYYYMMDD' #HowItShouldBe
var curr_date = Number(Text(Now(),'YMMDD'))

//filter statement (SQL) - access variables with @
//if you'd like to change the time interval, change '1200' seconds to something else in statement - 'arrivalSeconds >= (@secondsElapsed - 1200)'
var filterStatement = 'arrivalSeconds >= (@secondsElapsed - 1200) AND arrivalSeconds < @secondsElapsed AND stop_id = @stopId AND @curr_date >= start_date AND @curr_date <= end_date'

//filter the gtfsTable by the filter statement
//order results by arrivalSeconds in ascending order
var filtered = OrderBy(Filter(gtfsTable, filterStatement),'arrivalSeconds ASC')

var popupResult = ''

var weekDays = ['sunday','monday','tuesday','wednesday','thursday','friday','saturday']
//This variable is used within the for loop to determine whether a bus is operational on a given weekDay (e.g. returns 1 if operational, 0 if not).
var currWeekdayOpr = weekDays[Weekday(Now())]

//Iterate over the filtered results to show desried fields in popup
for (var f in filtered){
    if (f[currWeekdayOpr] == 1){
        popupResult += Left(f.arrival_time, 5) + " - " + f.route_long_name +
        When(f.route_type == 712, Concatenate(" (",f.trip_headsign,")"), Concatenate(" (",f.route_short_name,")")) +
        When(f.wheelchair_accessible == 1 && f.bikes_allowed == 1, " - ",
        f.wheelchair_accessible == 1 && f.bikes_allowed == 0," - ",
        f.wheelchair_accessible == 0 && f.bikes_allowed == 1," - ","") + TextFormatting.NewLine + TextFormatting.NewLine
    } else{
        popupResult += ""
    }
}

return popupResult

Final thoughts

Once you’ve built the pop-ups to your heart's content, you can further highlight them by using configurable app templates. Click here to see how I’ve extended the above web map using the NearBy configurable app where users can search for bus stops by stop numbers, stop names or addresses.

Gotchas - FeatureSetByPortalItem() function is a great way to pull in data from another layer without needing to add that layer to your map. But make sure that the same sharing privileges are shared across the layer in the map and the layer you're pulling data from.

Learn more - if you like what you see, check out these carefully crafted Arcade resources by esri staff members. And if you do happen to be visiting this year's Developer Summit keep an eye out for Arcade tech sessions presented by Lisa Berry and Paul Barker.

Finally, stay tuned to Part 2 - where we'll take this solution "all the way to town"

Cheers! - Gee Fernando