Last active
December 10, 2015 21:59
GTFS MySQL db creation script. This script only contains a subset of the GTFS tables that I needed, but you can probably figure out the rest. The indices, particularly the ones on the stop_times table, are important - queries will run really slow without them.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Database: `ocdata` | |
-- | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `agency` | |
-- | |
CREATE TABLE `agency` ( | |
`agency_name` varchar(10) DEFAULT NULL, | |
`agency_url` varchar(24) DEFAULT NULL, | |
`agency_timezone` varchar(16) DEFAULT NULL, | |
`agency_lang` varchar(2) DEFAULT NULL | |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `calendar` | |
-- | |
CREATE TABLE `calendar` ( | |
`service_id` varchar(26) DEFAULT NULL, | |
`monday` int(1) DEFAULT NULL, | |
`tuesday` int(1) DEFAULT NULL, | |
`wednesday` int(1) DEFAULT NULL, | |
`thursday` int(1) DEFAULT NULL, | |
`friday` int(1) DEFAULT NULL, | |
`saturday` int(1) DEFAULT NULL, | |
`sunday` int(1) DEFAULT NULL, | |
`start_date` int(8) DEFAULT NULL, | |
`end_date` int(8) DEFAULT NULL, | |
KEY `service_id` (`service_id`) | |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `calendar_dates` | |
-- | |
CREATE TABLE `calendar_dates` ( | |
`service_id` varchar(25) DEFAULT NULL, | |
`date` int(8) DEFAULT NULL, | |
`exception_type` int(1) DEFAULT NULL, | |
KEY `service_id` (`service_id`) | |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `routes` | |
-- | |
CREATE TABLE `routes` ( | |
`route_id` varchar(7) DEFAULT NULL, | |
`route_short_name` varchar(4) DEFAULT NULL, | |
`route_long_name` varchar(10) DEFAULT NULL, | |
`route_desc` varchar(10) DEFAULT NULL, | |
`route_type` int(1) DEFAULT NULL, | |
KEY `route_id` (`route_id`) | |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `stops` | |
-- | |
CREATE TABLE `stops` ( | |
`stop_id` varchar(5) DEFAULT NULL, | |
`stop_code` varchar(4) DEFAULT NULL, | |
`stop_name` varchar(49) DEFAULT NULL, | |
`stop_desc` varchar(10) DEFAULT NULL, | |
`stop_lat` decimal(10,6) DEFAULT NULL, | |
`stop_lon` decimal(10,6) DEFAULT NULL, | |
`stop_street` varchar(10) DEFAULT NULL, | |
`stop_city` varchar(10) DEFAULT NULL, | |
`stop_region` varchar(10) DEFAULT NULL, | |
`stop_postcode` varchar(10) DEFAULT NULL, | |
`stop_country` varchar(10) DEFAULT NULL, | |
`zone_id` varchar(10) DEFAULT NULL, | |
KEY `stop_id` (`stop_id`) | |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `stop_times` | |
-- | |
CREATE TABLE `stop_times` ( | |
`trip_id` varchar(50) NOT NULL, | |
`arrival_time` time NOT NULL, | |
`departure_time` time NOT NULL, | |
`stop_id` varchar(7) NOT NULL, | |
`stop_sequence` int(4) NOT NULL, | |
`pickup_type` int(2) NOT NULL, | |
`dropoff_type` int(2) NOT NULL, | |
KEY `stop_times_index` (`stop_id`,`trip_id`,`arrival_time`) | |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; | |
-- -------------------------------------------------------- | |
-- | |
-- Table structure for table `trips` | |
-- | |
CREATE TABLE `trips` ( | |
`route_id` varchar(7) DEFAULT NULL, | |
`service_id` varchar(26) DEFAULT NULL, | |
`trip_id` varchar(35) DEFAULT NULL, | |
`trip_headsign` varchar(22) DEFAULT NULL, | |
`block_id` varchar(7) DEFAULT NULL, | |
KEY `route_id` (`route_id`), | |
KEY `service_id` (`service_id`), | |
KEY `trip_id` (`trip_id`) | |
) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment