gtfs_tables.sqlite

drop table if exists gtfs_agency;
drop table if exists gtfs_stops;
drop table if exists gtfs_routes;
drop table if exists gtfs_calendar;
drop table if exists gtfs_calendar_dates;
drop table if exists gtfs_fare_attributes;
drop table if exists gtfs_fare_rules;
drop table if exists gtfs_shapes;
drop table if exists gtfs_trips;
drop table if exists gtfs_stop_times;
drop table if exists gtfs_frequencies;

drop table if exists gtfs_transfers;
drop table if exists gtfs_feed_info;

drop table if exists gtfs_route_types;
drop table if exists gtfs_directions;
drop table if exists gtfs_pickup_dropoff_types;
drop table if exists gtfs_payment_methods;

drop table if exists gtfs_location_types;
drop table if exists gtfs_transfer_types;

drop table if exists service_combo_ids;
drop table if exists service_combinations;

PRAGMA foreign_keys = OFF;

begin;

create table gtfs_agency (
  agency_id    text PRIMARY KEY,--PRIMARY KEY,
  agency_name  text NOT NULL,--NOT NULL,
  agency_url   text NOT NULL,--NOT NULL,
  agency_timezone    text NOT NULL,--NOT NULL,
  agency_lang  text
  -- unofficial features
  ,
  agency_phone text,
  fare_url text
);

--unoffical table, related to gtfs_stops(location_type)
create table gtfs_location_types (
  location_type int PRIMARY KEY,
  description text
);

insert into gtfs_location_types(location_type, description) 
       values (0,'stop');
insert into gtfs_location_types(location_type, description) 
       values (1,'station');
insert into gtfs_location_types(location_type, description) 
       values (2,'station entrance');


create table gtfs_stops (
  stop_id    text PRIMARY KEY,--PRIMARY KEY,
  stop_name  text NOT NULL, --NOT NULL,
  stop_desc  text,
  stop_lat   double precision,
  stop_lon   double precision,
  zone_id    int,
  stop_url   text,
  stop_code  text,

  -- new
  stop_street text,
  stop_city   text,
  stop_region text,
  stop_postcode text,
  stop_country text,

  -- unofficial features

  location_type int, --FOREIGN KEY REFERENCES gtfs_location_types(location_type)
  parent_station text, --FOREIGN KEY REFERENCES gtfs_stops(stop_id)
  stop_timezone text,
  FOREIGN KEY (location_type) REFERENCES gtfs_location_types(location_type),
  FOREIGN KEY (parent_station) REFERENCES gtfs_stops(stop_id)
);

-- select AddGeometryColumn( 'gtfs_stops', 'location', #{WGS84_LATLONG_EPSG}, 'POINT', 2 );
-- CREATE INDEX gtfs_stops_location_ix ON gtfs_stops USING GIST ( location GIST_GEOMETRY_OPS );

create table gtfs_route_types (
  route_type int PRIMARY KEY,
  description text
);

insert into gtfs_route_types (route_type, description) values (0, 'Street Level Rail');
insert into gtfs_route_types (route_type, description) values (1, 'Underground Rail');
insert into gtfs_route_types (route_type, description) values (2, 'Intercity Rail');
insert into gtfs_route_types (route_type, description) values (3, 'Bus');
insert into gtfs_route_types (route_type, description) values (4, 'Ferry');
insert into gtfs_route_types (route_type, description) values (5, 'Cable Car');
insert into gtfs_route_types (route_type, description) values (6, 'Suspended Car');
insert into gtfs_route_types (route_type, description) values (7, 'Steep Incline Mode');


create table gtfs_routes (
  route_id    text PRIMARY KEY,--PRIMARY KEY,
  agency_id   text , --REFERENCES gtfs_agency(agency_id),
  route_short_name  text DEFAULT '',
  route_long_name   text DEFAULT '',
  route_desc  text,
  route_type  int , --REFERENCES gtfs_route_types(route_type),
  route_url   text,
  route_color text,
  route_text_color  text,
  FOREIGN KEY (agency_id) REFERENCES gtfs_agency(agency_id),
  FOREIGN KEY (route_type) REFERENCES gtfs_route_types(route_type)
);

create table gtfs_directions (
  direction_id int PRIMARY KEY,
  description text
);

insert into gtfs_directions (direction_id, description) values (0,'This way');
insert into gtfs_directions (direction_id, description) values (1,'That way');


create table gtfs_pickup_dropoff_types (
  type_id int PRIMARY KEY,
  description text
);

insert into gtfs_pickup_dropoff_types (type_id, description) values (0,'Regularly Scheduled');
insert into gtfs_pickup_dropoff_types (type_id, description) values (1,'Not available');
insert into gtfs_pickup_dropoff_types (type_id, description) values (2,'Phone arrangement only');
insert into gtfs_pickup_dropoff_types (type_id, description) values (3,'Driver arrangement only');



-- CREATE INDEX gst_trip_id_stop_sequence ON gtfs_stop_times (trip_id, stop_sequence);

create table gtfs_calendar (
  service_id   text PRIMARY KEY,--PRIMARY KEY,
  monday int NOT NULL, --NOT NULL,
  tuesday int NOT NULL, --NOT NULL,
  wednesday    int NOT NULL, --NOT NULL,
  thursday     int NOT NULL, --NOT NULL,
  friday int NOT NULL, --NOT NULL,
  saturday     int NOT NULL, --NOT NULL,
  sunday int NOT NULL, --NOT NULL,
  start_date   date NOT NULL, --NOT NULL,
  end_date     date NOT NULL --NOT NULL
);

create table gtfs_calendar_dates (
  service_id     text , --REFERENCES gtfs_calendar(service_id),
  date     date NOT NULL, --NOT NULL,
  exception_type int  NOT NULL--NOT NULL
  -- above reference not in makeindices.sql
);

-- The following two tables are not in the spec, but they make dealing with dates and services easier
create table service_combo_ids
(
combination_id serial --primary key
);
create table service_combinations
(
combination_id int , --references service_combo_ids(combination_id),
service_id text --references gtfs_calendar(service_id)
);


create table gtfs_payment_methods (
  payment_method int PRIMARY KEY,
  description text
);

insert into gtfs_payment_methods (payment_method, description) values (0,'On Board');
insert into gtfs_payment_methods (payment_method, description) values (1,'Prepay');


create table gtfs_fare_attributes (
  fare_id     text PRIMARY KEY,--PRIMARY KEY,
  price double precision NOT NULL, --NOT NULL,
  currency_type     text NOT NULL, --NOT NULL,
  payment_method    int , --REFERENCES gtfs_payment_methods,
  transfers   int,
  transfer_duration int,
  agency_id text,  --REFERENCES gtfs_agency(agency_id),
  FOREIGN KEY (payment_method) REFERENCES gtfs_payment_methods(payment_method),
  FOREIGN KEY (agency_id) REFERENCES gtfs_agency(agency_id)
);

create table gtfs_fare_rules (
  fare_id     text , --REFERENCES gtfs_fare_attributes(fare_id),
  route_id    text , --REFERENCES gtfs_routes(route_id),
  origin_id   int ,
  destination_id int ,
  contains_id int 
  -- unofficial features
  ,
  service_id text, -- REFERENCES gtfs_calendar(service_id) ?
  FOREIGN KEY (fare_id) REFERENCES gtfs_fare_attributes(fare_id),
  FOREIGN KEY (route_id) REFERENCES gtfs_routes(route_id)

);

create table gtfs_shapes (
  shape_id    text NOT NULL, --NOT NULL,
  shape_pt_lat double precision NOT NULL, --NOT NULL,
  shape_pt_lon double precision NOT NULL, --NOT NULL,
  shape_pt_sequence int NOT NULL, --NOT NULL,
  shape_dist_traveled double precision
);

create table gtfs_trips (
  route_id text , --REFERENCES gtfs_routes(route_id),
  service_id    text , --REFERENCES gtfs_calendar(service_id),
  trip_id text PRIMARY KEY,--PRIMARY KEY,
  trip_headsign text,
  direction_id  int NOT NULL, --NOT NULL --REFERENCES gtfs_directions(direction_id),
  block_id text,
  shape_id text,
  -- unofficial features  
  trip_short_name text,
  FOREIGN KEY (route_id) REFERENCES gtfs_routes(route_id),
  FOREIGN KEY (direction_id) REFERENCES gtfs_directions(direction_id)
);

create table gtfs_stop_times (
  trip_id text , --REFERENCES gtfs_trips(trip_id),
  arrival_time text, -- CHECK (arrival_time LIKE '__:__:__'),
  departure_time text, -- CHECK (departure_time LIKE '__:__:__'),
  stop_id text , --REFERENCES gtfs_stops(stop_id),
  stop_sequence int NOT NULL, --NOT NULL,
  stop_headsign text,
  pickup_type   int , --REFERENCES gtfs_pickup_dropoff_types(type_id),
  drop_off_type int , --REFERENCES gtfs_pickup_dropoff_types(type_id),
  shape_dist_traveled double precision

  -- unofficial features
  ,
  timepoint int

  -- the following are not in the spec
  ,
  arrival_time_seconds int, 
  departure_time_seconds int,

  FOREIGN KEY (trip_id) REFERENCES gtfs_trips(trip_id),
  FOREIGN KEY (stop_id) REFERENCES gtfs_stops(stop_id),
  FOREIGN KEY (pickup_type) REFERENCES gtfs_pickup_dropoff_types(type_id),
  FOREIGN KEY (drop_off_type) REFERENCES gtfs_pickup_dropoff_types(type_id),
  CHECK (arrival_time LIKE '__:__:__'),
  CHECK (departure_time LIKE '__:__:__')
);

create index arr_time_index on gtfs_stop_times(arrival_time_seconds);
create index dep_time_index on gtfs_stop_times(departure_time_seconds);
create index stop_seq_index on gtfs_stop_times(trip_id,stop_sequence);
create index shape_seq_index on gtfs_shapes(shape_id,shape_pt_sequence);

-- select AddGeometryColumn( 'gtfs_shapes', 'shape', #{WGS84_LATLONG_EPSG}, 'LINESTRING', 2 );

create table gtfs_frequencies (
  trip_id     text , --REFERENCES gtfs_trips(trip_id),
  start_time  text NOT NULL, --NOT NULL,
  end_time    text NOT NULL, --NOT NULL,
  headway_secs int NOT NULL, --NOT NULL
  start_time_seconds int,
  end_time_seconds int,
  FOREIGN KEY (trip_id) REFERENCES gtfs_trips(trip_id)
);





-- unofficial tables


create table gtfs_transfer_types (
  transfer_type int PRIMARY KEY,
  description text
);

insert into gtfs_transfer_types (transfer_type, description) 
       values (0,'Preferred transfer point');
insert into gtfs_transfer_types (transfer_type, description) 
       values (1,'Designated transfer point');
insert into gtfs_transfer_types (transfer_type, description) 
       values (2,'Transfer possible with min_transfer_time window');
insert into gtfs_transfer_types (transfer_type, description) 
       values (3,'Transfers forbidden');


create table gtfs_transfers (
  from_stop_id text, --REFERENCES gtfs_stops(stop_id)
  to_stop_id text, --REFERENCES gtfs_stops(stop_id)
  transfer_type int, --REFERENCES gtfs_transfer_types(transfer_type)
  min_transfer_time int,
  from_route_id text, --REFERENCES gtfs_routes(route_id)
  to_route_id text, --REFERENCES gtfs_routes(route_id)
  service_id text, --REFERENCES gtfs_calendar(service_id) ?
  FOREIGN KEY (from_stop_id) REFERENCES gtfs_stops(stop_id),

  FOREIGN KEY (to_stop_id) REFERENCES gtfs_stops(stop_id),
  FOREIGN KEY (transfer_type) REFERENCES gtfs_transfer_types(transfer_type),
  FOREIGN KEY (from_route_id) REFERENCES gtfs_routes(route_id),
  FOREIGN KEY (to_route_id) REFERENCES gtfs_routes(route_id)
);


create table gtfs_feed_info (
  feed_publisher_name text,
  feed_publisher_url text,
  feed_timezone text,
  feed_lang text,
  feed_version text
);



commit;