I’m lucky enough to live in between two train stations, so I often want to know which station has the quickest train to New York City. However, NJ Transit’s trip planner only lets me pick one starting station and one destination station. So, I set out to build my own trip planner. You can try it out if you’d like!
Getting train data
NJ Transit developer portal
NJ Transit has a developer portal that gives access to train data.
Interestingly, it isn’t a live API, but rather a set of .txt files that you download. When the schedules change, NJ Transit emails all developers to let everyone know a new schedule is available for download:
Hello Developers!
We have just loaded new GTFS data for the NJ TRANSIT rail & bus system. Please download the new GTFS files at https://njtransit.com/developer-tools. As a reminder, you must download the new data within 3 business days to remain in compliance with our license agreement.
Thank you.
These .txt files look like .csv exports of database tables. For example, here are the first few lines of the stops.txt file:
stop_id,stop_code,stop_name,stop_desc,stop_lat,stop_lon,zone_id
1,95001,"30TH ST. PHL.",,39.956565,-75.182327,5961
2,95002,"ABSECON",,39.424333,-74.502094,333
3,95003,"ALLENDALE",,41.030902,-74.130957,2893I’d been wanting to try out sqlite, and this seemed like a great opportunity to set up a little sqlite db. In fact, sqlite makes it really easy to import files as CSV. I wrote this bash script to create a sqlite db file from NJ Transit’s supplied .txt files:
#!/bin/bash
# delete the old db
rm njtransit.db
# load in all the new train schedule files
sqlite3 njtransit.db ".import agency.txt agency" --csv
sqlite3 njtransit.db ".import calendar_dates.txt calendar_dates" --csv
sqlite3 njtransit.db ".import routes.txt routes" --csv
sqlite3 njtransit.db ".import shapes.txt shapes" --csv
sqlite3 njtransit.db ".import stop_times.txt stop_times" --csv
sqlite3 njtransit.db ".import stops.txt stops" --csv
sqlite3 njtransit.db ".import trips.txt trips" --csv
# print all the tables in the db to confirm the import worked
sqlite3 njtransit.db ".tables"I had data, but needed a way to access it from my Astro server code.
Using Prisma to query data
I’ve also been interested in trying out Prisma ORM as an easy way to interact with DBs from JavaScript or TypeScript. I followed their quickstart to see how to set things up.
I installed prisma and ran their init command:
npm install prisma --save-dev
npx prisma init --datasource-provider sqliteThis left me with a schema.prisma file that showed all the tables, but the Prisma CLI warned that it didn’t know which fields represented ids for the tables, so I had to manually mark those in the schema.prisma file. After that, I was able to run prisma generate to create fully typed client libraries.
npx prisma generateA quick test query showed that things were working:
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();
const stops = await prisma.stops.findMany();Now, time to build the app.
Building the app
Selecting stops
Since this is a quick app that’s pretty much just for me, I kept the UI simple. It’s just two select elements with the multiple attribute set to true, wrapped in a form that submits to the scheduling page:
<form action="njtransit/which-train">
<fieldset>
<legend>Starting station(s)</legend>
<select id="departure-station" name="departure-station" multiple>
{stops.map((stop) => (
<option>{stop["stop_name"]}</option>
))}
</select>
</fieldset>
<fieldset>
<legend>Destination station(s)</legend>
<select id="arrival-station" name="arrival-station" multiple>
{stops.map((stop) => (
<option>{stop["stop_name"]}</option>
))}
</select>
</fieldset>
<button>Find trains!</button>
</form>When the “Find trains!” button is clicked, the browser loads up a new page (njtransit/which-train) with route data embedded as query params in the URL. Here’s an example:
Showing the best train to take
The server pulls the station selection out of the url using Astro’s Astro.url utility:
// Tell Astro this page is rendered on-demand on the server
export const prerender = false;
const startingStations = Astro.url.searchParams.getAll("departure-station");
const destinationStations = Astro.url.searchParams.getAll("arrival-station");Then, it performs a series of queries with Prisma ORM to find the right trains. The rough order is:
- Get in-service trips: Query the
calendar_datesandtripstables to find train trips that are in service today. - Get a list of trains leaving the departing station soon (regardless of destination): Query the
stop_timestable for trains that are in service today and departing the departure station(s) the next 60 minutes. - Get a list of trains arriving at our destination: Query the
stop_timestable for trains that arrive in the destination station and have a trip_id that matches trains departing the departure station(s). Filter to make sure the arrival time here is after the departure time at the departure station (to get rid of trains going in the opposite direction). - Get the final list of relevant trains leaving: Filter the list of trains leaving to remove any that don’t have a trip_id matching the trip_id in the destination stops list.
- Sort the list in order of time the train arrives at the destination.
The app displays the list of trains, and highlights the one with the fastest time.
Conclusion
It was fun to try sqlite and Prisma. Sqlite was excellent, just really easy to use for something like this. Since the db is just a file, I didn’t have to worry about spinning up an extra DB server. Prisma felt a little clunkier, since the query syntax leaves you with giant objects to describe a somewhat simple SQL query. However, once I figured it all out it was nice to have auto-generated types in TypeScript for my whole DB.
That’s it! A simple web app that solves a problem I have. Give it a try!