structure.sql

CREATE TABLE IF NOT EXISTS "calendar_dates" (
  "service_id" varchar(100),
  "date" decimal(10,2),
  "exception_type" decimal(10,2)
);

CREATE TABLE IF NOT EXISTS "routes" (
  "route_id" int PRIMARY KEY,
  "route_short_name" varchar(15),
  "route_long_name" varchar(15),
  "route_type" int,
  "route_color" varchar(6),
  "route_text_color" varchar(6)
);

CREATE TABLE IF NOT EXISTS "stop_times" (
  "trip_id" varchar(11),
  "arrival_time" varchar(8),
  "departure_time" varchar(8),
  "stop_id" int,
  "stop_sequence" int,
  FOREIGN KEY (trip_id) REFERENCES "trips" (trip_id)
);

CREATE TABLE IF NOT EXISTS "stops" (
  "stop_id" int PRIMARY KEY,
  "stop_name" varchar(100),
  "stop_lat" double,
  "stop_lon" double
);

CREATE TABLE IF NOT EXISTS "trips" (
  "route_id" int,
  "service_id" varchar(15),
  "trip_id" varchar(12) PRIMARY KEY,
  "trip_headsign" varchar(19),
  "trip_short_name" int,
  "direction_id" int,
  "shape_id" int,
  FOREIGN KEY (route_id) REFERENCES "routes" (route_id)
);

CREATE INDEX IF NOT EXISTS trips_route_id_index ON "trips" ("route_id");