Route Colours - GTFS - SQL

58 views

Skip to first unread message

Cyrus Kafai Wu

unread,

Mar 7, 2016, 1:30:40 PM3/7/16

to Open Data Ottawa

Included are the SQL queries I made for Route Colours:

// UPDATE `routes` SET `route_color` = '#AA519F', `route_text_color` = '#fff' where `route_short_name` IN (92,96,93) and `agency_id` = '1'

// UPDATE `routes` SET `route_color` = '#155E60', `route_text_color` = '#fff' where `route_short_name` IN (91,94,95,104) and `agency_id` = '1'

// UPDATE `routes` SET `route_color` = '#A17F55', `route_text_color` = '#fff' where `route_short_name` IN (97,98,99) and `agency_id` = '1'

// limited

// UPDATE `routes` SET `route_color` = 'grey', `route_text_color` = '#fff' where `route_short_name` IN (120,123,132,137,154,161,162,165,174,175,178,185,196,198) and `agency_id` = '1'

// express

// UPDATE `routes` SET `route_color` = 'green', `route_text_color` = '#fff' where `route_short_name` IN (20,21,22,27,30,31,34,35,37,38,60,61,62,64,65,66,68,69,70,71,72,73,77,221,231,232,261,262,263,283) and `agency_id` = '1'

// peak

// UPDATE `routes` SET `route_color` = 'red', `route_text_color` = '#fff' where `route_short_name` IN (6,24,40,41,43,67,102,105,136,140,155,157,180,181,182,186,188,189,192,193,194,199) and `agency_id` = '1'

// special

// UPDATE `routes` SET `route_color` = 'purple', `route_text_color` = '#fff' where `route_short_name` >= '400' and `route_short_name` <= '499' and `agency_id` = '1'

// ecole

// UPDATE `routes` SET `route_color` = 'orange', `route_text_color` = '#fff' where `route_short_name` >= '600' and `route_short_name` <= '699' and `agency_id` = '1'

// train

// UPDATE `routes` SET `route_color` = '#7AC143', `route_text_color` = '#fff' where `route_short_name` IN (750,107) and `agency_id` = '1'

Thanks

Cyrus

http://ottTransit.ca

Cyrus Kafai Wu

unread,

Mar 23, 2016, 4:39:35 PM3/23/16

to Open Data Ottawa

INSERT INTO `routes` (`id`, `agency_id`, `route_short_name`, `route_long_name`, `route_desc`, `route_id`, `route_type`, `route_color`, `route_text_color`) VALUES

(1, 1, '1', '', '', '1-224', 3, '#231F20', '#FFF'),

(2, 1, '2', '', '', '2-224', 3, '#231F20', '#FFF'),

(3, 1, '4', '', '', '4-224', 3, '#231F20', '#FFF'),

(4, 1, '5', '', '', '5-224', 3, '#636466', '#FFF'),

(5, 1, '6', '', '', '6-224', 3, '#B44D2B', '#FFF'),

(6, 1, '7', '', '', '7-224', 3, '#231F20', '#FFF'),

(7, 1, '8', '', '', '8-224', 3, '#231F20', '#FFF'),

(8, 1, '9', '', '', '9-224', 3, '#636466', '#FFF'),

(9, 1, '12', '', '', '12-224', 3, '#231F20', '#FFF'),

(10, 1, '14', '', '', '14-224', 3, '#636466', '#FFF'),

(11, 1, '16', '', '', '16-224', 3, '#636466', '#FFF'),

(12, 1, '18', '', '', '18-224', 3, '#636466', '#FFF'),

(13, 1, '20', '', '', '20-224', 3, '#75BB9E', '#000'),

(14, 1, '21', '', '', '21-224', 3, '#75BB9E', '#000'),

(15, 1, '22', '', '', '22-224', 3, '#4F8636', '#000'),

(16, 1, '24', '', '', '24-224', 3, '#A64D2B', '#FFF'),

(17, 1, '27', '', '', '27-224', 3, '#687E51', '#000'),

(18, 1, '30', '', '', '30-224', 3, '#4F8636', '#000'),

(19, 1, '31', '', '', '31-224', 3, '#75BB9E', '#000'),

(20, 1, '34', '', '', '34-224', 3, '#75BB9E', '#000'),

(21, 1, '35', '', '', '35-224', 3, '#75BB9E', '#000'),

(22, 1, '37', '', '', '37-224', 3, '#4F8636', '#000'),

(23, 1, '38', '', '', '38-224', 3, '#40632F', '#000'),

(24, 1, '40', '', '', '40-224', 3, '#B32316', '#FFF'),

(25, 1, '41', '', '', '41-224', 3, '#C5593B', '#FFF'),

(26, 1, '43', '', '', '43-224', 3, '#C5593B', '#FFF'),

(27, 1, '60', '', '', '60-224', 3, '#40632F', '#000'),

(28, 1, '61', '', '', '61-224', 3, '#40632F', '#FFF'),

(29, 1, '62', '', '', '62-224', 3, '#4F8636', '#FFF'),

(30, 1, '64', '', '', '64-224', 3, '#4F8636', '#FFF'),

(31, 1, '65', '', '', '65-224', 3, '#4F8636', '#FFF'),

(32, 1, '66', '', '', '66-224', 3, '#4F8636', '#FFF'),

(33, 1, '67', '', '', '67-224', 3, '#B77050', '#FFF'),

(34, 1, '68', '', '', '68-224', 3, '#40632F', '#FFF'),

(35, 1, '69', '', '', '69-224', 3, '#40632F', '#FFF'),

(36, 1, '70', '', '', '70-224', 3, '#40632F', '#000'),

(37, 1, '71', '', '', '71-224', 3, '#6D8636', '#000'),

(38, 1, '72', '', '', '72-224', 3, '#40632F', '#000'),

(39, 1, '73', '', '', '73-224', 3, '#6D8636', '#000'),

(40, 1, '77', '', '', '77-224', 3, '#40632F', '#000'),

(41, 1, '85', '', '', '85-224', 3, '#231F20', '#FFF'),

(42, 1, '86', '', '', '86-224', 3, '#231F20', '#FFF'),

(43, 1, '87', '', '', '87-224', 3, '#231F20', '#FFF'),

(44, 1, '91', '', '', '91-224', 3, '#155E60', '#FFF'),

(45, 1, '92', '', '', '92-224', 3, '#AA519F', '#FFF'),

(46, 1, '93', '', '', '93-224', 3, '#AA519F', '#FFF'),

(47, 1, '94', '', '', '94-224', 3, '#155E60', '#FFF'),

(48, 1, '95', '', '', '95-224', 3, '#155E60', '#FFF'),

(49, 1, '96', '', '', '96-224', 3, '#AA519F', '#FFF'),

(50, 1, '97', '', '', '97-224', 3, '#A17F55', '#000'),

(51, 1, '98', '', '', '98-224', 3, '#A17F55', '#000'),

(52, 1, '99', '', '', '99-224', 3, '#A17F55', '#000'),

(53, 1, '101', '', '', '101-224', 3, '#636466', '#FFF'),

(54, 1, '103', '', '', '103-224', 3, '#A64D2B', '#FFF'),

(55, 1, '104', '', '', '104-224', 3, '#155E60', '#FFF'),

(56, 1, '105', '', '', '105-224', 3, '#C5593B', '#FFF'),

(57, 1, '106', '', '', '106-224', 3, '#231F20', '#FFF'),

(58, 1, '111', '', '', '111-224', 3, '#231F20', '#FFF'),

(59, 1, '112', '', '', '112-224', 3, '#636466', '#FFF'),

(60, 1, '114', '', '', '114-224', 3, '#636466', '#FFF'),

(61, 1, '116', '', '', '116-224', 3, '#636466', '#FFF'),

(62, 1, '118', '', '', '118-224', 3, '#231F20', '#FFF'),

(63, 1, '120', '', '', '120-224', 3, '#58728A', '#FFF'),

(64, 1, '121', '', '', '121-224', 3, '#3A6E8F', '#FFF'),

(65, 1, '122', '', '', '122-224', 3, '#006A92', '#FFF'),

(66, 1, '123', '', '', '123-224', 3, '#265771', '#FFF'),

(67, 1, '124', '', '', '124-224', 3, '#636466', '#FFF'),

(68, 1, '126', '', '', '126-224', 3, '#3A6E8F', '#FFF'),

(69, 1, '128', '', '', '128-224', 3, '#636466', '#FFF'),

(70, 1, '129', '', '', '129-224', 3, '#636466', '#FFF'),

(71, 1, '130', '', '', '130-224', 3, '#265771', '#FFF'),

(72, 1, '131', '', '', '131-224', 3, '#006A92', '#FFF'),

(73, 1, '132', '', '', '132-224', 3, '#0085AB', '#FFF'),

(74, 1, '134', '', '', '134-224', 3, '#265771', '#FFF'),

(75, 1, '135', '', '', '135-224', 3, '#636466', '#FFF'),

(76, 1, '136', '', '', '136-224', 3, '#B77050', '#FFF'),

(77, 1, '137', '', '', '137-224', 3, '#636466', '#FFF'),

(78, 1, '140', '', '', '140-224', 3, '#B32316', '#FFF'),

(79, 1, '143', '', '', '143-224', 3, '#58728A', '#FFF'),

(80, 1, '144', '', '', '144-224', 3, '#3A6E8F', '#FFF'),

(81, 1, '146', '', '', '146-224', 3, '#3A6E8F', '#FFF'),

(82, 1, '147', '', '', '147-224', 3, '#3A6E8F', '#FFF'),

(83, 1, '148', '', '', '148-224', 3, '#3A6E8F', '#FFF'),

(84, 1, '149', '', '', '149-224', 3, '#3A6E8F', '#FFF'),

(85, 1, '150', '', '', '150-224', 3, '#006A92', '#FFF'),

(86, 1, '151', '', '', '151-224', 3, '#006A92', '#FFF'),

(87, 1, '152', '', '', '152-224', 3, '#3A6E8F', '#FFF'),

(88, 1, '153', '', '', '153-224', 3, '#265873', '#FFF'),

(89, 1, '154', '', '', '154-224', 3, '#B5392E', '#FFF'),

(90, 1, '155', '', '', '155-224', 3, '#B5392E', '#FFF'),

(91, 1, '156', '', '', '156-224', 3, '#265973', '#FFF'),

(92, 1, '157', '', '', '157-224', 3, '#B72D1E', '#FFF'),

(93, 1, '159', '', '', '159-224', 3, '#3D7090', '#FFF'),

(94, 1, '161', '', '', '161-224', 3, '#006A92', '#FFF'),

(95, 1, '162', '', '', '162-224', 3, '#5BAAC9', '#FFF'),

(96, 1, '164', '', '', '164-224', 3, '#A64D2B', '#FFF'),

(97, 1, '165', '', '', '165-224', 3, '#636466', '#FFF'),

(98, 1, '168', '', '', '168-224', 3, '#636466', '#FFF'),

(99, 1, '170', '', '', '170-224', 3, '#636467', '#FFF'),

(100, 1, '171', '', '', '171-224', 3, '#70808F', '#FFF'),

(101, 1, '172', '', '', '172-224', 3, '#265771', '#FFF'),

(102, 1, '173', '', '', '173-224', 3, '#557285', '#FFF'),

(103, 1, '174', '', '', '174-224', 3, '#98918F', '#FFF'),

(104, 1, '175', '', '', '175-224', 3, '#6789A1', '#FFF'),

(105, 1, '176', '', '', '176-224', 3, '#808182', '#FFF'),

(106, 1, '177', '', '', '177-224', 3, '#6789A1', '#FFF'),

(107, 1, '178', '', '', '178-224', 3, '#93ADC4', '#FFF'),

(108, 1, '180', '', '', '180-224', 3, '#A64D2B', '#FFF'),

(109, 1, '181', '', '', '181-224', 3, '#A64D2B', '#FFF'),

(110, 1, '182', '', '', '182-224', 3, '#B32316', '#FFF'),

(111, 1, '186', '', '', '186-224', 3, '#B77050', '#FFF'),

(112, 1, '188', '', '', '188-224', 3, '#C5593B', '#FFF'),

(113, 1, '189', '', '', '189-224', 3, '#A64D2B', '#FFF'),

(114, 1, '192', '', '', '192-224', 3, '#A64D2B', '#FFF'),

(115, 1, '193', '', '', '193-224', 3, '#B32316', '#FFF'),

(116, 1, '194', '', '', '194-224', 3, '#B82316', '#FFF'),

(117, 1, '199', '', '', '199-224', 3, '#B97050', '#FFF'),

(118, 1, '221', '', '', '221-224', 3, '#4F8636', '#FFF'),

(119, 1, '231', '', '', '231-224', 3, '#4F8634', '#FFF'),

(120, 1, '232', '', '', '232-224', 3, '#4F8634', '#000'),

(121, 1, '261', '', '', '261-224', 3, '#9DBA8B', '#000'),

(122, 1, '262', '', '', '262-224', 3, '#9DBA8B', '#000'),

(123, 1, '263', '', '', '263-224', 3, '#9DBA8B', '#000')

A new update

Reply all

Reply to author

Forward

0 new messages