GTFS Stops → Self-Join (pre_pass + LOOKUP)

View on GitHub

What

Enrich every NYC-subway platform row with its parent station name — a value that lives on a different row of the same file — using bxp's pre_pass + LOOKUP. A self-join, no second file.

Why interesting

GTFS stops.txt is hierarchical inside one flat CSV: a parent station row (location_type = 1) is followed by directional platform rows whose parent_station column holds the parent's stop_id (101N → 101). Downstream you almost always want the human station name next to each platform, which means resolving that opaque id against another row — the classic "join a table to itself" problem. Most CSV tools can't do it in one pass; bxp scans the file once into a keyed table (pre_pass) and then every row can LOOKUP back into it.

flowchart LR
    F["stops.txt<br/><small>stations + platforms, one flat CSV</small>"]
    F -->|"pre_pass"| T["keyed table<br/><small>stop_id → station_name</small>"]
    F -->|"per row"| P["platform 101N<br/><small>parent_station = 101</small>"]
    T --> L{{"LOOKUP(parent_station)"}}
    P --> L
    L --> O["101N · parent_station_name = Van Cortlandt Park-242 St"]

Edge cases sourced from.

• GTFS reference — stops.txt / parent_station defines the location_type / parent_station hierarchy
• platform rows reference a stop_id that may appear before or after them in the file — a single forward scan (pre_pass) handles either order

Data source. MTA — NYC subway GTFS feed (stops.txt; this slice: 5 real stations and their 10 directional platforms — enough for both sides of the self-join to fit in one screen). Public data.

At full scale

bash fetch-full.sh          # downloads the MTA GTFS zip, extracts ./full/stops.txt
bxp-cli --config full.json  # resolves every platform → parent station name

On the full feed: 1,488 stops → 496 stations + 992 platforms, and all 992 platforms resolve their parent station name via the self-join.

The trick

(see sample.json):

1. pre_pass scans the file first and indexes the parent stations (location_type = 1) by stop_id, storing station_name (+ coords).
2. LOOKUP([parent_station], 'station_name') in input_schema resolves each platform's opaque parent id to the name captured in the pre-pass. Stations have no parent, so an IF([parent_station] = '', …) guard keeps them clean.

Final result

Row 101N arrives with only parent_station = 101 — an id that means nothing on its own. The output row carries parent_station_name = "Van Cortlandt Park-242 St", pulled from row 101 elsewhere in the same file. Without the pre_pass you'd need a second tool, a second file, or a manual join; here it's two config lines.

Sample data

Run it with bxp-cli --config ./sample.json --template gtfs_stops_selfjoin:

sample.json (config)sample.csvsample.csvx (result)

{
  // GTFS stops.txt (NYC subway) — each directional platform row carries a
  // `parent_station` that points at ANOTHER row's `stop_id` in the SAME file.
  // This is a self-join: enrich every platform with its parent station's name,
  // resolved from a different row, using bxp's pre_pass + LOOKUP.
  conversion_templates: {
    gtfs_stops_selfjoin: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",

      // PRE_PASS — first scan the whole file and index the *parent stations*
      // (location_type = 1) by their stop_id, capturing the name/coords other
      // rows will need. This is what lets a later row look back at an earlier
      // (or later) row by key.
      pre_pass: {
        when:   "[location_type] = '1'",
        key:    "[stop_id]",
        values: {
          station_name: "[stop_name]",
          station_lat:  "[stop_lat]",
          station_lon:  "[stop_lon]"
        }
      },

      input_schema: {
        $stop_id:   "[stop_id]",
        $name:      "[stop_name]",
        $kind:      "IF([location_type] = '1', 'station', 'platform')",
        $parent_id: "[parent_station]",

        // SELF-JOIN — turn the opaque parent_station id ("101") into the human
        // station name stored by another row. Stations themselves have no
        // parent, so guard the empty case.
        $parent_name: "IF(ISEMPTY([parent_station]), '', LOOKUP([parent_station], 'station_name'))"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        stop_id:             "$stop_id",
        name:                "$name",
        kind:                "$kind",
        parent_id:           "$parent_id",
        parent_station_name: "$parent_name"
      }
    }
  }
}

stop_id,stop_name,stop_lat,stop_lon,location_type,parent_station
101,Van Cortlandt Park-242 St,40.889248,-73.898583,1,
101N,Van Cortlandt Park-242 St,40.889248,-73.898583,,101
101S,Van Cortlandt Park-242 St,40.889248,-73.898583,,101
103,238 St,40.884667,-73.900870,1,
103N,238 St,40.884667,-73.900870,,103
103S,238 St,40.884667,-73.900870,,103
104,231 St,40.878856,-73.904834,1,
104N,231 St,40.878856,-73.904834,,104
104S,231 St,40.878856,-73.904834,,104
106,Marble Hill-225 St,40.874561,-73.909831,1,
106N,Marble Hill-225 St,40.874561,-73.909831,,106
106S,Marble Hill-225 St,40.874561,-73.909831,,106
107,215 St,40.869444,-73.915279,1,
107N,215 St,40.869444,-73.915279,,107
107S,215 St,40.869444,-73.915279,,107

stop_id,name,kind,parent_id,parent_station_name
101,Van Cortlandt Park-242 St,station,,
101N,Van Cortlandt Park-242 St,platform,101,Van Cortlandt Park-242 St
101S,Van Cortlandt Park-242 St,platform,101,Van Cortlandt Park-242 St
103,238 St,station,,
103N,238 St,platform,103,238 St
103S,238 St,platform,103,238 St
104,231 St,station,,
104N,231 St,platform,104,231 St
104S,231 St,platform,104,231 St
106,Marble Hill-225 St,station,,
106N,Marble Hill-225 St,platform,106,Marble Hill-225 St
106S,Marble Hill-225 St,platform,106,Marble Hill-225 St
107,215 St,station,,
107N,215 St,platform,107,215 St
107S,215 St,platform,107,215 St

Full-scale & binary files (run it on the complete dataset): fetch-full.sh · full.json.