Transit Line Data Modeling and Query Design: A Case Study of a Single Bus Route

1. Scenario and Problem Definition: A Bus Route Is a Set of Structured Data

Consider a typical suburban bus route in a city: it connects a suburban passenger terminal with a rail transit interchange station, spans approximately 16.9 km, has 29 stops in the outbound direction and 28 in the inbound direction, operates from 06:00 to 20:00 in both directions with a headway of about 20 minutes, charges a flat fare of 2 CNY, and one terminal offers transfers to the rail network. After several years of operation, the route was renamed with a new number and taken over by a different operator.

For passengers, this is ordinary travel information. But for a development team responsible for transit network data services, this description contains nearly every typical problem in public transit data modeling:

• How should line attributes (length, fare structure, operating hours) be stored structurally?
• What does the asymmetric stop count between directions (29 vs. 28) mean, and how should it be represented in data?
• Should “transferable to rail transit” be attached to the line or to the station?
• How can lifecycle events such as renaming and operator changes be handled without overwriting and losing historical data?

This article addresses the general solutions to these problems, presenting a complete approach to domain modeling, storage design, and core queries. All examples are illustrative structures and do not depend on any specific implementation.

2. Domain Modeling: A Line Is Not a Single List — It Is a Set of Stop Sequences in Two Directions

2.1 Core Entity Decomposition

The most easily underestimated point is that a bus “line” in data is not a simple list of stops, but a collection of multiple “directions.” The following entities should be separated:

• Line: Carries relatively stable attributes such as number, name, type, fare structure, and opening date.
• Direction: A line typically contains two directions, each with its own stop sequence, terminals, and first/last bus times. For passenger-facing display, naming a direction as “from Station A to Station B” is less confusing than abstract “outbound/inbound” labels.
• Station: A physical stop with a unique identifier and coordinates. It can be shared by multiple lines and is the smallest reusable unit.
• StopPattern: The association between a direction and its stations, recording the order of stops within a direction.
• TransferLink: A station-level association. In the example, “the terminal offers rail transit transfer” should be modeled as “this bus station has a transfer connection to a rail station,” not as a line attribute.
• OperationInfo: First/last bus times, headway, etc. These may differ by direction and should be attached at the direction level.

2.2 Bidirectional Asymmetry: Behind 29 and 28

Having 29 stops outbound and 28 inbound is a very common phenomenon in transit network data. Typical causes include one-way detours, one-directional stops, and stops served in only one direction. More notably, in real networks, the two directions may not even mirror each other: the outbound terminal is a rail interchange station, while the inbound origin is an adjacent hub station. This yields two modeling rules:

• Each direction must independently store its own terminals and complete stop sequence. Never derive the inbound direction by simply reversing outbound data.
• When data validation detects asymmetric stop sets between directions, the system should prompt the operator to confirm the reason and keep a record, rather than rejecting the data outright. For cases where a vehicle “passes but does not stop,” use an explicit field to flag this so passenger-facing displays are not misleading.

2.3 Illustrative Data Structure

{
  "line_id": "L-0001",
  "name": "XX Line",
  "type": "regular bus",
  "fare": { "currency": "CNY", "amount": 2.0 },
  "directions": [
    {
      "direction_id": "L-0001-D1",
      "label": "Passenger Terminal → Rail Interchange Station",
      "first_bus": "06:00",
      "last_bus": "20:00",
      "headway_min": 20,
      "stops": [
        { "seq": 1, "station_id": "S001", "name": "Passenger Terminal" },
        { "seq": 2, "station_id": "S002", "name": "Old Street Corner" },
        { "seq": 3, "station_id": "S003", "name": "Forest Park" }
      ]
    },
    {
      "direction_id": "L-0001-D2",
      "label": "Hub Station → Passenger Terminal",
      "stops": [ "...independently maintained inbound stop sequence..." ]
    }
  ],
  "transfers": [
    { "station_id": "S029", "connect_to": "RAIL-01", "type": "rail" }
  ]
}

(Station names are fictional and serve only as structural illustrations.)

3. Storage Design: Ordering, History, and Consistency

3.1 Basic Table Structure

CREATE TABLE station (
  station_id  BIGINT PRIMARY KEY,
  name        VARCHAR(64) NOT NULL,
  lng         DECIMAL(10,6),
  lat         DECIMAL(10,6)
);
CREATE TABLE line (
  line_id     BIGINT PRIMARY KEY,
  name        VARCHAR(64) NOT NULL,
  type        VARCHAR(16),
  status      VARCHAR(16) NOT NULL,   -- active / renamed / suspended
  opened_on   DATE
);
CREATE TABLE line_direction (
  direction_id BIGINT PRIMARY KEY,
  line_id      BIGINT NOT NULL,
  label        VARCHAR(128),
  first_bus    TIME,
  last_bus     TIME,
  headway_min  INT
);
CREATE TABLE line_stop (
  direction_id BIGINT NOT NULL,
  seq          INT NOT NULL,
  station_id   BIGINT NOT NULL,
  skip_flag    SMALLINT DEFAULT 0,
  PRIMARY KEY (direction_id, seq)
);

3.2 Techniques for Maintaining the Sequence Field

Stop order is typically represented by an integer seq. If stations are inserted mid-route, incrementing by 1 would require renumbering the entire chain. Two common approaches exist:

• Gapped numbering: Increment seq by 100 (100, 200, 300, …), and insert new stops using the midpoint between neighbors.
• Fractional indexing: Use high-precision decimals or string-based fractional indices. Insertion cost is constant, but readability suffers.

Bus routes typically have stops in the tens, so gapped numbering is simple, intuitive, and usually sufficient. When gaps are exhausted, a full renumbering pass can be performed.

3.3 Lifecycle and Historical Versions

The example route was renamed and changed operators after several years of operation. Such events remind us that transit network data must have built-in history rather than overwriting in place. Recommendations:

• Add status (active / renamed / suspended / discontinued) and effective-period fields to the line table.
• On renaming, create a new line entity with a supersedes_line_id pointing to the old entity. The old entity retains its original data with status set to “renamed,” ensuring historical links and data reconciliation remain accessible.
• Record operator changes in an event table (line_operation_event) including the operator and effective date.
• Distinguish between “current view” and “historical view” on the query side: map display and passenger queries use the current view, while data reconciliation and complaint tracing use the historical view.

4. Core Query Capabilities and Interface Design

4.1 Line Detail Query

The most basic and most frequent interface: input a line identifier and return metadata plus stop sequences for both directions. Key points:

• Return complete stop sequences for both directions in a single response to avoid secondary requests from the frontend.
• Station output includes sequence number, name, and transfer annotations (e.g., “transfer to rail transit”).
• Line static data is read-heavy and write-light, making it suitable for aggressive caching. On operational changes, bump the version number to batch-invalidate.

4.2 Station-to-Line Reverse Lookup

“What lines serve this station” is the second most common query after line details. It is essentially a reverse index:

SELECT DISTINCT l.line_id, l.name
FROM line_stop ls
JOIN line_direction d ON d.direction_id = ls.direction_id
JOIN line l ON l.line_id = d.line_id
WHERE ls.station_id = :sid AND l.status = 'active';

When data volume is modest, a direct database query with an index suffices. If spatial filtering such as “find lines near this location” is added, a spatial index can be introduced, or the station-line relationship can be synced to a general-purpose search engine component.

4.3 Transfer Information Query

The key to transfer queries is “station-level association.” Once transfer relationships are stored in a station_transfer table, “what can I transfer to at this station” becomes a simple join query. If in-station versus out-of-station transfers need to be distinguished later, or walking distances need to be estimated, only the attributes of the transfer table need to be extended without affecting the overall model.

4.4 Representing First/Last Bus Times and Headway

• Store times using the TIME type (06:00, 20:00) rather than free-form strings.
• “Headway of about 20 minutes” is a planned approximation. Store headway_min=20 with an “is exact” flag.
• When “what time is the next bus” is needed, approximate it based on first bus time plus headway. For precise departure times, real-time location and dispatch data streams are required — this is a separate system from static network data. The boundary must be clearly drawn so the static service is not forced to take on real-time responsibilities.

5. Engineering Practice Checklist

• Validate data upfront: On import, validate terminal consistency, seq continuity, time window validity (first bus not later than last bus), and annotate reasons for bidirectional stop differences.
• Unify direction naming: Always use “toward Station X” for passenger-facing displays, and map to direction codes internally.
• Idempotent imports and auditing: Operational data often comes from multiple sources such as spreadsheets, APIs, and manual entry. Use “line + direction + effective date” as the idempotency key on the write side, and keep an audit log of all changes.
• Caching and consistency: Static data can be cached aggressively, but renaming and stop adjustments are “low-frequency, high-risk” changes. After such changes, proactively invalidate related caches and re-verify key pages.
• Decouple capabilities: For map display and location search, use general-purpose map and location service categories to decouple the business model from specific implementation details and reduce future migration costs.

6. Summary

A seemingly ordinary bus route — 16.9 km, 29 stops outbound and 28 inbound, 20-minute headway, 2 CNY flat fare, rail connection at one terminal, renamed with a new operator after several years — covers nearly every typical problem in transit network data modeling: directional stop sequences, asymmetric stop sets, transfer relationship attachment, historical version management, and the boundary between static schedules and real-time dispatch. When these foundational modeling practices are solid, upper-layer capabilities such as route planning, arrival prediction, and network analysis have a reliable foundation to build on.