Cal-ITP GTFS-Ingest Pipeline Dataset

CREATE TABLE agency (
  "agency_id" VARCHAR,
  "agency_name" VARCHAR,
  "agency_url" VARCHAR,
  "agency_timezone" VARCHAR,
  "agency_lang" VARCHAR,
  "agency_phone" VARCHAR,
  "agency_fare_url" VARCHAR,
  "agency_email" VARCHAR,
  "base64_url" VARCHAR
);

CREATE TABLE areas (
  "area_id" VARCHAR,
  "area_name" VARCHAR,
  "base64_url" VARCHAR
);

CREATE TABLE attributions (
  "organization_name" VARCHAR,
  "attribution_id" VARCHAR,
  "agency_id" VARCHAR,
  "route_id" VARCHAR,
  "trip_id" VARCHAR,
  "is_producer" BIGINT,
  "is_operator" VARCHAR,
  "is_authority" VARCHAR,
  "attribution_url" VARCHAR,
  "attribution_email" VARCHAR,
  "attribution_phone" VARCHAR,
  "base64_url" VARCHAR
);

CREATE TABLE calendar (
  "service_id" VARCHAR,
  "monday" BIGINT,
  "tuesday" BIGINT,
  "wednesday" BIGINT,
  "thursday" BIGINT,
  "friday" BIGINT,
  "saturday" BIGINT,
  "sunday" BIGINT,
  "start_date" TIMESTAMP,
  "end_date" TIMESTAMP,
  "base64_url" VARCHAR
);

CREATE TABLE calendar_dates (
  "service_id" VARCHAR,
  "date" TIMESTAMP,
  "exception_type" BIGINT,
  "base64_url" VARCHAR
);

CREATE TABLE cal_itp_gtfs_schedule_data_dictionary (
  "system_name" VARCHAR,
  "table_name" VARCHAR,
  "field_name" VARCHAR,
  "field_alias" VARCHAR,
  "field_description" VARCHAR,
  "field_description_authority" VARCHAR,
  "confidential" VARCHAR,
  "sensitive" VARCHAR,
  "pii" VARCHAR,
  "pci" VARCHAR,
  "field_type" VARCHAR,
  "field_length" BIGINT,
  "field_precision" DOUBLE,
  "units" VARCHAR,
  "domain_type" VARCHAR,
  "allowable_min_value" VARCHAR,
  "allowable_max_value" VARCHAR,
  "usage_notes" VARCHAR
);

CREATE TABLE cal_itp_gtfs_schedule_metadata (
  "dataset_name" VARCHAR,
  "tags" VARCHAR,
  "description" VARCHAR,
  "methodology" VARCHAR,
  "topic" VARCHAR,
  "publisher_organization" VARCHAR,
  "place" VARCHAR,
  "frequency" VARCHAR,
  "next_update" TIMESTAMP,
  "creation_date" TIMESTAMP,
  "last_update" VARCHAR,
  "status" VARCHAR,
  "temporal_coverage_begin" VARCHAR,
  "temporal_coverage_end" VARCHAR,
  "data_dictionary" VARCHAR,
  "data_dictionary_type" VARCHAR,
  "contact_organization" VARCHAR,
  "contact_position" VARCHAR,
  "contact_name" VARCHAR,
  "contact_email" VARCHAR,
  "public_access_level" VARCHAR,
  "access_constraints" VARCHAR,
  "use_constraints" VARCHAR,
  "data_life_span" VARCHAR,
  "caltrans_link" VARCHAR,
  "data_standard" VARCHAR,
  "notes" VARCHAR,
  "gis_theme" VARCHAR,
  "gis_horiz_accuracy" VARCHAR,
  "gis_vert_accuracy" VARCHAR,
  "gis_coordinate_system_epsg" BIGINT,
  "gis_vert_datum_epsg" VARCHAR
);

CREATE TABLE fare_attributes (
  "fare_id" VARCHAR,
  "price" DOUBLE,
  "currency_type" VARCHAR,
  "payment_method" BIGINT,
  "transfers" DOUBLE,
  "agency_id" VARCHAR,
  "transfer_duration" DOUBLE,
  "base64_url" VARCHAR
);

CREATE TABLE fare_leg_rules (
  "base64_url" VARCHAR,
  "leg_group_id" VARCHAR,
  "network_id" VARCHAR,
  "from_area_id" VARCHAR,
  "to_area_id" VARCHAR,
  "fare_product_id" VARCHAR
);

CREATE TABLE fare_media (
  "base64_url" VARCHAR,
  "fare_media_id" VARCHAR,
  "fare_media_name" VARCHAR,
  "fare_media_type" BIGINT
);

CREATE TABLE fare_products (
  "base64_url" VARCHAR,
  "fare_product_id" VARCHAR,
  "fare_product_name" VARCHAR,
  "fare_media_id" VARCHAR,
  "amount" DOUBLE,
  "currency" VARCHAR
);

CREATE TABLE fare_rules (
  "fare_id" VARCHAR,
  "route_id" VARCHAR,
  "origin_id" VARCHAR,
  "destination_id" VARCHAR,
  "contains_id" VARCHAR,
  "base64_url" VARCHAR
);

CREATE TABLE fare_transfer_rules (
  "base64_url" VARCHAR,
  "from_leg_group_id" VARCHAR,
  "to_leg_group_id" VARCHAR,
  "transfer_count" DOUBLE,
  "duration_limit" DOUBLE,
  "duration_limit_type" DOUBLE,
  "fare_transfer_type" BIGINT,
  "fare_product_id" VARCHAR
);

CREATE TABLE feed_info (
  "feed_publisher_name" VARCHAR,
  "feed_publisher_url" VARCHAR,
  "feed_lang" VARCHAR,
  "default_lang" VARCHAR,
  "feed_version" VARCHAR,
  "feed_contact_email" VARCHAR,
  "feed_contact_url" VARCHAR,
  "feed_start_date" TIMESTAMP,
  "feed_end_date" TIMESTAMP,
  "base64_url" VARCHAR
);

CREATE TABLE frequencies (
  "trip_id" VARCHAR,
  "start_time" VARCHAR,
  "end_time" VARCHAR,
  "headway_secs" BIGINT,
  "exact_times" DOUBLE,
  "base64_url" VARCHAR
);

CREATE TABLE gtfs_datasets (
  "name" VARCHAR,
  "type" VARCHAR,
  "regional_feed_type" VARCHAR,
  "base64_url" VARCHAR,
  "url" VARCHAR,
  "schedule_to_use_for_rt_validation_base64_url" VARCHAR,
  "schedule_to_use_for_rt_validation_url" VARCHAR
);

CREATE TABLE levels (
  "level_id" VARCHAR,
  "level_index" DOUBLE,
  "level_name" VARCHAR,
  "base64_url" VARCHAR
);

CREATE TABLE pathways (
  "pathway_id" VARCHAR,
  "from_stop_id" VARCHAR,
  "to_stop_id" VARCHAR,
  "pathway_mode" BIGINT,
  "is_bidirectional" BIGINT,
  "length" DOUBLE,
  "traversal_time" VARCHAR,
  "stair_count" DOUBLE,
  "max_slope" VARCHAR,
  "min_width" DOUBLE,
  "signposted_as" VARCHAR,
  "reversed_signposted_as" VARCHAR,
  "base64_url" VARCHAR
);

CREATE TABLE routes (
  "route_id" VARCHAR,
  "route_type" BIGINT,
  "agency_id" VARCHAR,
  "route_short_name" VARCHAR,
  "route_long_name" VARCHAR,
  "route_desc" VARCHAR,
  "route_url" VARCHAR,
  "route_color" VARCHAR,
  "route_text_color" VARCHAR,
  "route_sort_order" DOUBLE,
  "continuous_pickup" DOUBLE,
  "continuous_drop_off" DOUBLE,
  "network_id" VARCHAR,
  "base64_url" VARCHAR
);

CREATE TABLE shapes (
  "shape_id" VARCHAR,
  "shape_pt_lat" DOUBLE,
  "shape_pt_lon" DOUBLE,
  "shape_pt_sequence" BIGINT,
  "shape_dist_traveled" DOUBLE,
  "base64_url" VARCHAR
);

CREATE TABLE stop_areas (
  "area_id" VARCHAR,
  "stop_id" VARCHAR,
  "base64_url" VARCHAR
);

CREATE TABLE stops (
  "base64_url" VARCHAR,
  "stop_id" VARCHAR,
  "tts_stop_name" VARCHAR,
  "stop_lat" DOUBLE,
  "stop_lon" DOUBLE,
  "zone_id" VARCHAR,
  "parent_station" VARCHAR,
  "stop_code" VARCHAR,
  "stop_name" VARCHAR,
  "stop_desc" VARCHAR,
  "stop_url" VARCHAR,
  "location_type" DOUBLE,
  "stop_timezone" VARCHAR,
  "wheelchair_boarding" DOUBLE,
  "level_id" VARCHAR,
  "platform_code" VARCHAR
);

CREATE TABLE stop_times (
  "base64_url" VARCHAR,
  "trip_id" VARCHAR,
  "stop_id" VARCHAR,
  "stop_sequence" BIGINT,
  "arrival_time" VARCHAR,
  "departure_time" VARCHAR,
  "stop_headsign" VARCHAR,
  "pickup_type" DOUBLE,
  "drop_off_type" DOUBLE,
  "continuous_pickup" DOUBLE,
  "continuous_drop_off" DOUBLE,
  "shape_dist_traveled" DOUBLE,
  "timepoint" DOUBLE,
  "arrival_sec" DOUBLE,
  "departure_sec" DOUBLE,
  "start_pickup_drop_off_window" VARCHAR,
  "end_pickup_drop_off_window" VARCHAR,
  "mean_duration_factor" DOUBLE,
  "mean_duration_offset" DOUBLE,
  "safe_duration_factor" DOUBLE,
  "safe_duration_offset" DOUBLE,
  "pickup_booking_rule_id" VARCHAR,
  "drop_off_booking_rule_id" VARCHAR
);

CREATE TABLE transfers (
  "from_stop_id" VARCHAR,
  "to_stop_id" VARCHAR,
  "transfer_type" DOUBLE,
  "from_route_id" VARCHAR,
  "to_route_id" VARCHAR,
  "from_trip_id" VARCHAR,
  "to_trip_id" VARCHAR,
  "min_transfer_time" DOUBLE,
  "base64_url" VARCHAR
);

CREATE TABLE trips (
  "base64_url" VARCHAR,
  "route_id" VARCHAR,
  "service_id" VARCHAR,
  "trip_id" VARCHAR,
  "shape_id" VARCHAR,
  "trip_headsign" VARCHAR,
  "trip_short_name" VARCHAR,
  "direction_id" DOUBLE,
  "block_id" VARCHAR,
  "wheelchair_accessible" DOUBLE,
  "bikes_allowed" DOUBLE
);