Live Transit API/Architecture

Enterprise Database
This zone contains the SQLServer database to which the OrbCAD positional data is replicated on a continual basis.

DMZ
The County Transit API Server node will be located in the County DMZ. This server extracts/transforms/loads data and replicates to a server instance in the Amazon EC2 cloud

EC2 Cloud
One or more server instance nodes in the Amazon Elastic Compute Cloud (EC2) support Transit Center signs and other applications. These instances may be scaled up or down based on resource demand loads.

Interface with County Enterprise SQL Server Database
In the case of push via triggers (recommended approach):
 * The triggers and any user-defined procedures will need to be installed on the County Enterprise SQL Server database by an authority with sufficient credentials
 * The DMZ-based County Transit API server will need a connection to the Enterprise SQL Server instance

In the case of poll via application:
 * The DMZ-based County Transit API server will need the ability to connect the County Enterprise SQL Server database, including database connection credentials

Under either scenario:
 * The County will stand up a Linux system that hosts the DMZ-based County Transit API server.
 * The DMZ-based County Transit API server will need to be accessible internally by Transit staff in order to upload the GTFS formatted static data and post messages
 * As the DMZ-based County Transit API server will replicate in an outbound only mode, no incoming ports need to be opened. However, the DMZ-based County Transit API server will need the ability to make outgoing connections

SQL Server Extract Module
A combination of a new SQL Server view and small footprint CLR Stored Procedure will publish Transit real time information from the County SQL Server Enterprise database. A .NET 3.5 assembly (dll).


 * 1) An update trigger tied to the the DOT database VEHICLE table sends a Vehicle_id to the CLR stored procedure upon update to any vehicle record.
 * 2) The CLR Stored Procedure then:
 * 3) Queries the Vehiicle_Status view with "Vehicle_Id” to get the real time data, including vehicle position and arrival times
 * 4) Performs HTTP Post to County Live Transit API server in the DMZ with updated vehicle status

A User Defined Function (UDF) will support configuration to specify URL of destination server.

The implementation strategy is expected to have minimal impact on the existing system. It includes:


 * One View
 * One table update trigger
 * One CLR-based stored procedure
 * One UDF

It's expected this solution will work on SQL Server 2005 and later.

Minimal Latency Requirements
The most important component for minimal latency will be the bus position data. Given the frequency of the updates on vehicle position, it is crucial to ensure that this data is quickly available in the reporting application.

Data Storage Implications
While the schedule data will be accepted in GTFS format, it will be de-normalized for storage to increase the performance of the reporting application.