Calculating travel time from residential points to stores via public transit using ArcGIS for Desktop?

Question

I don't know how to summarize it in just one title, but here is the problem:

My goal is to calculate the travel time from residential points to stores via public transit. Basically this period of time consists three parts:

1. The walking time from a residential point to the nearest bus stop "t1", which can be calculated by the Nearest Facility tool from Network Analyst. The resulting table is called "Resident_Stop", of which the number of records is m;

2. The bus-riding time "t2" from this bus stop to the nearest bus stop of the target store via bus route, which is a record from Network Analyst's OD Time Matrix table, namely, "Stop_Stop". The origins and destinations are all bus stops on one single bus route so that the number of records is s*s;

3. The walking time from the ending bus stop to the store "t3", which is basically the same with the first step. The table is "Stop_Store", of which the number of records is n.

So the total travel time "t" equals to (t1+t2+t3) and here is where problem occurs: I haven't figured out a way to attach the three tables reasonably to add the times on using a join. Currently I have an OD Time Matrix table of the walking time directly from residential points to stores via road network (no transit involved) and its number of records is m*n. I also separatly joined the table "Resident_Stop" in step 1 and the table "Stop_Store" in step 3 to the m*n-sized table based on residential point IDs and store IDs. So the remaining thing now is to assign the bus-riding time in table 2 based on table 1's stop IDs and table 3's stop IDs to each of the m*n records. Then I can get the total time.

Should you get any ideas, I'm willing to discuss with you. I'm now thinking about using Python to handle this but I'm a beginner on that. If you have any books or tutorials to recommand please tell me.

Answer 1

I'll demonstrate my suggestion using 14 bus stops:

The trick here is to modify Stop_Stop matrix into shorter table:

The store is closest to stop 8, thus above table can be reduced to 14 rows by Create Feature Layer (Model builder) or changing Definition Query (script) to [From_Stop]=8. Similar thing has to be done with stops2store table, so it will contain only 1 row:

Start your journeys by joining house2stop, stop2stop and stop2store tables as shown below:

Add field Total to house2stop, and use field calculator to convert distance fields (last fields in above picture) to travel time. Example below shows distances only:

All this is doable using Model Builder and can be extended to the case with multiple stores, using store ID and 'Total' field name as parameters. It woul de nice though to let some of them just walk:) Note: it was tested on FGDB tables.